Thread: Query Analyser
Hi List, Is there anyway so as to indicate the Query Analyser not to use the plan which it is using regularly, and use a new plan ? From where do the Query Analyser gets the all info to prepare a plan? Is it only from the pg_statistics table or are there anyother tables which have this info. stored? And can we change the statistic?? Thanx in advance -- Regards Gauri
On Tue, Jul 10, 2007 at 08:17:05PM +0530, Gauri Kanekar wrote: > Is there anyway so as to indicate the Query Analyser not to use the > plan which it is using regularly, and use a new plan ? You can't dictate the query plan but you can influence the planner's decisions with various configuration settings. http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html Disabling planner methods (enable_seqscan, etc.) should be a last resort -- before doing so make sure that settings like shared_buffers and effective_cache_size are appropriately sized for your system, that you're gathering enough statistics (see below), and that the statistics are current (run ANALYZE or VACUUM ANALYZE). After all that, if you still think you need to disable a planner method then consider posting the query and the EXPLAIN ANALYZE output to pgsql-performance to see if anybody has other suggestions. > From where do the Query Analyser gets the all info to prepare a plan? > Is it only from the pg_statistics table or are there anyother tables > which have this info. stored? The planner also uses pg_class.{reltuples,relpages}. http://www.postgresql.org/docs/8.2/interactive/planner-stats.html http://www.postgresql.org/docs/8.2/interactive/planner-stats-details.html > And can we change the statistic?? You can increase the amount of statistics gathered for a specific column with ALTER TABLE SET STATISTICS or system-wide by adjusting default_statistics_target. http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER -- Michael Fuhr