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
Content-Transfer-Encoding: 8bit

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