On Fri, May 31, 2013 at 7:32 PM, <fburgess@xxxxxxxxxxxxxxx> 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-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
