Re: Query Analyser

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