pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL's query caching behaviour
2+ messages / 2 participants
[nested] [flat]
* PostgreSQL's query caching behaviour
@ 2009-01-22 12:53 Jan-Peter.Seifert@gmx.de
0 siblings, 1 reply; 2+ messages in thread
From: Jan-Peter.Seifert@gmx.de @ 2009-01-22 12:53 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PostgreSQL's query caching behaviour
@ 2009-01-22 21:52 Chander Ganesan <chander@otg-nc.com>
parent: Jan-Peter.Seifert@gmx.de
0 siblings, 0 replies; 2+ messages in thread
From: Chander Ganesan @ 2009-01-22 21:52 UTC (permalink / raw)
To: Jan-Peter.Seifert@gmx.de; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-01-22 21:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-01-22 12:53 PostgreSQL's query caching behaviour Jan-Peter.Seifert@gmx.de
2009-01-22 21:52 ` Chander Ganesan <chander@otg-nc.com>
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