From: Guillaume Cottenceau <gc@mnc.ch>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner?
Date: Thu, 04 Sep 2008 18:08:11 +0200
Message-ID: <87wshrrj10.fsf@mnc.ch> (raw)
In-Reply-To: <alpine.DEB.1.10.0809041646340.4454@aragorn.flymine.org>
References: <48BD33B2.D078.0028.0@rentrak.com>
<48BD33B2.D078.0028.0@rentrak.com>
<5925.1220389248@sss.pgh.pa.us>
<48BF195E.D078.0028.0@rentrak.com>
<23341.1220542340@sss.pgh.pa.us>
<alpine.DEB.1.10.0809041646340.4454@aragorn.flymine.org>
Matthew Wakeling <matthew 'at' flymine.org> writes:
> On Thu, 4 Sep 2008, Tom Lane wrote:
>> Ultimately the only way that we could get the right answer would be if
>> the planner realized that the required rows are concentrated at the end
>> of the table instead of being randomly scattered. This isn't something
>> that is considered at all right now in seqscan cost estimates. I'm not
>> sure offhand whether the existing correlation stats would be of use for
>> it, or whether we'd have to get ANALYZE to gather additional data.
>
> Using the correlation would help, I think, although it may not be the
> best solution possible. At least, if the correlation is zero, you
> could behave as currently, and if the correlation is 1, then you know
> (from the histogram) where in the table the values are.
It seems to me that if the correlation is 0.99[1], and you're
looking for less than 1% of rows, the expected rows may be at the
beginning or at the end of the heap?
Ref:
[1] or even 1, as ANALYZE doesn't sample all the rows?
--
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36
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: gc@mnc.ch, matthew@flymine.org
Subject: Re: limit clause breaks query planner?
In-Reply-To: <87wshrrj10.fsf@mnc.ch>
* 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