Thread: PostgreSQL's query caching behaviour

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

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.