From: Jan-Peter.Seifert@gmx.de
To: pgsql-admin@postgresql.org
Subject: PostgreSQL's query caching behaviour
Date: Thu, 22 Jan 2009 13:53:28 +0100
Message-ID: <20090122125328.233660@gmx.net> (raw)
Hello,
I've a question regarding caching of results in the PostgreSQL-Server.
As expected a:
SELECT COUNT(*) FROM <table>;
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 <table> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Jan-Peter.Seifert@gmx.de
Subject: Re: PostgreSQL's query caching behaviour
In-Reply-To: <20090122125328.233660@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox