Re: Query Analyser - Mailing list pgsql-performance

From Michael Fuhr
Subject Re: Query Analyser
Date
Msg-id 20070711140557.GA552@winnie.fuhr.org
Whole thread Raw
In response to Query Analyser  ("Gauri Kanekar" <meetgaurikanekar@gmail.com>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Gregory Stark
Date:
Subject: Re: best use of an EMC SAN
Next
From: Dave Cramer
Date:
Subject: Re: best use of an EMC SAN