Evaluating query performance with caching in PostgreSQL 9.1.6
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Two questions Please</div><div><br></div><div>1.) Is there any way to clear the <b>cache</b> so that we can ensure that when we run "<b>explain analyze</b>" on a query and make some minor adjustments to that query and re-execute, the plan is not cached. Since the cached plan returns runtimes that are much lower than the initial execution, so we don't know for certain the tweaks we made improved the performance of the query, without having to bounce the database?<br></div><div><br></div><div>2.) I am noticing that when I look at pg_stat_activities: autovacuum is re-processing some old Partition tables way back in 2007, which are static and are essentially read-only partitions. the line item in pg_stat reads as follows: autovacuum:VACUUM public.digi_sas_y2007m07 (to prevent wraparound). Is there a way to have autovacuum skip these static type partition tables, and only process partitions that have had; Inserts, updates, or deletes attributed to them? </div><div><br></div><div>thanks.<br></div><div> </div></span></body></html>
On Fri, May 31, 2013 at 7:32 PM, <fburgess@radiantblue.com> wrote:
1.) Is there any way to clear the cache so that we can ensure that when we
run "explain analyze" on a query and make some minor adjustments to that
query and re-execute, the plan is not cached.
PostgreSQL doesn't cache query plans if you do a normal "SELECT" or
"EXPLAIN ANALYZE SELECT" query. Plans are cached only if you use
prepared queries:
1. Embedded queries within PL/pgSQL procedures
2. Explicit PREPARE/EXECUTE commands
3. PQprepare in the libpq library (or other client library)
If you don't use these, then you are experiencing something else and
not "plan cache".
Maybe you're referring to disk cache. The only way to clear
PostgreSQL's cache (shared buffers) is to restart it, but there is
another level of caching done by the operating system.
On Linux you can drop the OS cache using:
echo 1 > /proc/sys/vm/drop_caches
2.) I am noticing that when I look at pg_stat_activities: autovacuum is
re-processing some old Partition tables way back in 2007, which are static
and are essentially read-only partitions. the line item in pg_stat reads as
follows: autovacuum:VACUUM public.digi_sas_y2007m07 (to prevent wraparound).
Is there a way to have autovacuum skip these static type partition tables,
No. This is a necessary and critical operation. PostgreSQL stores row
visibility information based on 32-bit transaction IDs (xids). This
value is small enough that it can wrap around, so very old tables need
to be "frozen". Details here:
http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
If this is a problem for you then you may want to schedule manual
VACUUM FREEZE on old tables during low usage periods.
Regards,
Marti
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general