From: Simon Riggs <simon@2ndquadrant.com>
To: David Brown <time@bigpond.net.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Analyzer is clueless
Date: Wed, 17 Nov 2004 22:32:48 +0000
Message-ID: <1100730768.4113.7709.camel@localhost.localdomain> (raw)
In-Reply-To: <20041117160824.B694D3A3ECC@svr1.postgresql.org>
References: <20041117160824.B694D3A3ECC@svr1.postgresql.org>
On Thu, 2004-11-18 at 02:08, David Brown wrote:
> Statistics were run on each table before query execution. The random page cost was lowered to 2, but as you can see, the estimated costs are wild anyway.
>
> As a comparison, MS SQL Server took less than 15 seconds, or 4 times faster.
>
> MySQL (InnoDB) took 2 seconds, which is 30 times faster.
>
> The query looks straightforward to me (it might be clearer with a subselect), so what on earth is wrong?
The query is, as you say, straightforward.
You are clearly working with a query that is on the very edge of the
decision between using an index or not.
The main issue is that PostgreSQL's default histogram statistics setting
is lower than other RDBMS. This means that it is less able to
discriminate between cases such as yours that are close to the edge.
This is a trade-off between run-time of the ANALYZE command and the
benefit it produces. As Joshua suggests, increasing the statistics
target for this table will likely allow the optimizer to correctly
determine the selectivity of the index and take the right path.
If this is a general RDBMS comparison, you may wish to extend the
system's default_statistics_target = 80 or at least > 10.
To improve this query, you may wish to extend the table's statistics
target using:
ALTER TABLE "drinv"
ALTER COLUMN OrderDate SET STATISTICS 100;
which should allow the planner to more accurately estimate statistics
and thereby select an index, if appropriate.
The doco has recently been changed with regard to effective_cache_size;
you don't mention what beta release level you're using. That is the only
planner parameter that takes cache size into account, so any other
changes would certainly have zero effect on this *plan* though might
still benefit execution time.
Please post EXPLAIN ANALYZE output for any further questions.
--
Best Regards, Simon Riggs
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: simon@2ndquadrant.com, time@bigpond.net.au
Subject: Re: Analyzer is clueless
In-Reply-To: <1100730768.4113.7709.camel@localhost.localdomain>
* 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