From: Michael Fuhr <mike@fuhr.org>
To: Gauri Kanekar <meetgaurikanekar@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Analyser
Date: Wed, 11 Jul 2007 08:05:57 -0600
Message-ID: <20070711140557.GA552@winnie.fuhr.org> (raw)
In-Reply-To: <7e4ba9550707100747r411cc2aex56f0c6d2fa273f73@mail.gmail.com>
References: <7e4ba9550707100747r411cc2aex56f0c6d2fa273f73@mail.gmail.com>
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.htmlhttp://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.htmlhttp://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER
--
Michael Fuhr
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mike@fuhr.org, meetgaurikanekar@gmail.com
Subject: Re: Query Analyser
In-Reply-To: <20070711140557.GA552@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox