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
tookabout 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
differentyears 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
flushthe '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
didso 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
