From: Tom Lane <tgl@sss.pgh.pa.us>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner?
Date: Thu, 04 Sep 2008 13:14:32 -0400
Message-ID: <25328.1220548472@sss.pgh.pa.us> (raw)
In-Reply-To: <87wshrrj10.fsf@mnc.ch>
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>
<87wshrrj10.fsf@mnc.ch>
Guillaume Cottenceau <gc@mnc.ch> writes:
> 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?
Right, but if you know the value being searched for, you could then
estimate where it is in the table by consulting the histogram.
Actually, an even easier hack (which would have the nice property of not
needing to know the exact value being searched for), would simply use
the existing cost estimates if the WHERE variables have low correlation
(meaning the random-locations assumption is probably good), but apply
some sort of penalty factor if the correlation is high. This would
amount to assuming that the universe is perverse and high correlation
will always mean that the rows we want are at the wrong end of the table
not the right one. But any DBA will tell you that the universe is
indeed perverse ;-)
OTOH, since indexscans get a cost estimate reduction in the presence of
high correlation, we're already biasing the choice in the direction of
indexscans for high correlation. We may not need to do it twice.
I don't recall whether the OP ever showed us his statistics for the
table in question --- did it even appear to have high correlation?
regards, tom lane
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: tgl@sss.pgh.pa.us, gc@mnc.ch, matthew@flymine.org
Subject: Re: limit clause breaks query planner?
In-Reply-To: <25328.1220548472@sss.pgh.pa.us>
* 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