Thread: Evaluating query performance with caching in PostgreSQL 9.1.6

<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
cachedplan returns runtimes that are much lower than the initial execution, so we don't know for certain the tweaks we
madeimproved the performance of the query, without having to bounce the database?<br /></div><div><br /></div><div>2.)
Iam 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:VACUUMpublic.digi_sas_y2007m07 (to prevent wraparound). Is there a way to have autovacuum skip these static
typepartition 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> 

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