From Jan-Peter.Seifert@gmx.de Thu Jan 22 12:53:35 2009 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B93646325C9 for ; Thu, 22 Jan 2009 08:53:35 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 89840-01 for ; Thu, 22 Jan 2009 08:53:32 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by mail.postgresql.org (Postfix) with SMTP id EB2D36325CC for ; Thu, 22 Jan 2009 08:53:31 -0400 (AST) Received: (qmail 19461 invoked by uid 0); 22 Jan 2009 12:53:28 -0000 Received: from 192.124.237.8 by www110.gmx.net with HTTP; Thu, 22 Jan 2009 13:53:28 +0100 (CET) Content-Type: text/plain; charset="iso-8859-1" Date: Thu, 22 Jan 2009 13:53:28 +0100 From: Jan-Peter.Seifert@gmx.de Message-ID: <20090122125328.233660@gmx.net> MIME-Version: 1.0 Subject: PostgreSQL's query caching behaviour To: pgsql-admin@postgresql.org X-Authenticated: #955937 X-Flags: 0001 X-Mailer: WWW-Mail 6100 (Global Message Exchange) X-Priority: 3 X-Provags-ID: V01U2FsdGVkX1/Vz0MPx5bV5+znWXvXm2DQA5bjA+PGRq5xFLE3Lf WudNVsFtoWe/oT86+H3LpjCc7tjz/mLLlRug== Content-Transfer-Encoding: 8bit X-GMX-UID: cFtNfTMcTiE+UlL3aWRw6rJ9ZUVSRNcN X-FuHaFi: 0.5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/115 X-Sequence-Number: 31162 Hello, I've a question regarding caching of results in the PostgreSQL-Server. As expected a: SELECT COUNT(*) FROM ; causes a sequential scan of the table. The table I tested this on has about 345 000 tuples. This and any following run took about 50-60 seconds on a test system (pgAdminIII 1.8.4 + Windows xp sp3 + Pg v8.3.5). "Aggregate (cost=179437.32..179437.33 rows=1 width=0) (actual time=44632.925..44632.927 rows=1 loops=1)" " -> Seq Scan on table (cost=0.00..178576.45 rows=344345 width=0) (actual time=13316.145..43815.699 rows=344345 loops=1)" "Total runtime: 44633.150 ms" On the other hand this just took 3-7 seconds: SELECT COUNT(*) FROM
WHERE year = '2008'; The index used is on year and two other columns. No surprise here as well. However, every subsequent run and with different years does not even take a second: Aggregate (cost=71684.22..71684.23 rows=1 width=0) (actual time=13.071..13.074 rows=1 loops=1) -> Bitmap Heap Scan on table (cost=987.71..71618.62 rows=26239 width=0) (actual time=1.285..7.883 rows=2214 loops=1) Recheck Cond: ((year)::text = '2008'::text) -> Bitmap Index Scan on absidx (cost=0.00..981.15 rows=26239 width=0) (actual time=0.994..0.994 rows=2214 loops=1) Index Cond: ((year)::text = '2008'::text) Total runtime: 13.308 ms The server does remember even after a stop and restart as the query still doesn't need more than a second then. I want to compare the performance of two different implementations of an application on the same database. So I want to flush the 'cache' responsible for this between the runs. This caching seems to come with increasing work_mem (from 1MB to 2MB). A different server not showing this behaviour did so after increasing the work_mem as well (and max_stack_depth). Doing VACUUM ANALYZE did 'help' a bit it seems as the query took substantially longer again, but I want to make sure. My next guess was the Statistics Collector: http://www.postgresql.org/docs/8.3/interactive/monitoring-stats.html Neither pg_stat_clear_snapshot() nor pg_stat_reset() did had any effect though. So how do I reset the server / cache reliably? Thank you very much for any hints in advance, Peter Seifert -- Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger From chander@otg-nc.com Thu Jan 22 21:52:21 2009 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E6E9A6325C7 for ; Thu, 22 Jan 2009 17:52:21 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 79497-10 for ; Thu, 22 Jan 2009 17:52:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp242.iad.emailsrvr.com (smtp242.iad.emailsrvr.com [207.97.245.242]) by mail.postgresql.org (Postfix) with ESMTP id A0091632582 for ; Thu, 22 Jan 2009 17:52:15 -0400 (AST) Received: from relay14.relay.iad.mlsrvr.com (localhost [127.0.0.1]) by relay14.relay.iad.mlsrvr.com (SMTP Server) with ESMTP id 2CF9D236C27; Thu, 22 Jan 2009 16:52:14 -0500 (EST) Received: by relay14.relay.iad.mlsrvr.com (Authenticated sender: chander-AT-otg-nc.com) with ESMTPSA id 05B93236B37; Thu, 22 Jan 2009 16:52:13 -0500 (EST) Message-ID: <4978EA8D.5010609@otg-nc.com> Date: Thu, 22 Jan 2009 16:52:13 -0500 From: Chander Ganesan User-Agent: Thunderbird 2.0.0.19 (Windows/20081209) MIME-Version: 1.0 To: Jan-Peter.Seifert@gmx.de CC: pgsql-admin@postgresql.org Subject: Re: PostgreSQL's query caching behaviour References: <20090122125328.233660@gmx.net> In-Reply-To: <20090122125328.233660@gmx.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200901/130 X-Sequence-Number: 31177 Jan-Peter.Seifert@gmx.de wrote: > Hello, > > I've a question regarding caching of results in the PostgreSQL-Server. > If you restart the server it will clear out the shared buffer pool, and subsequent queries would show the appropriate delay.. You can clear out the OS disk cache (on linux) using the commands: sync echo 1 > /proc/sys/vm/drop_caches That's likely what you are missing. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com Expert PostgreSQL, PostGIS, MapServer, Python, PHP, Django and other Open Source Training.