Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 67E7364FD02
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Sep 2008 13:08:22 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 01482-05 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  4 Sep 2008 13:08:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205])
	by postgresql.org (Postfix) with ESMTP id 850FB64FCEF
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Sep 2008 13:08:16 -0300 (ADT)
Received: from localhost (mail2.mnc.ch [127.0.0.1])
	by mail.mnc.ch (Postfix) with ESMTP id 6230A23086;
	Thu,  4 Sep 2008 16:08:12 +0000 (GMT)
X-Virus-Scanned: amavisd-new at mnc.ch
Received: from mail.mnc.ch ([127.0.0.1])
	by localhost (mail2.mnc.ch [127.0.0.1]) (amavisd-new, port 10025)
	with ESMTP id WXgDDzJMMm0n; Thu,  4 Sep 2008 16:08:11 +0000 (GMT)
Received: from meuh.localdomain (62-2-77-204.static.cablecom.ch [62.2.77.204])
	by mail.mnc.ch (Postfix) with ESMTP id ED91023085;
	Thu,  4 Sep 2008 16:08:11 +0000 (GMT)
Received: by meuh.localdomain (Postfix, from userid 1000)
	id D159C18352F; Thu,  4 Sep 2008 18:08:11 +0200 (CEST)
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner?
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>
From: Guillaume Cottenceau <gc@mnc.ch>
X-Hashcash: 
 1:20:080904:matthew@flymine.org::non6VdwfV2Hw4ayR:0000000000000000000000000000000000000000002SSC
X-Hashcash: 
 1:20:080904:pgsql-performance@postgresql.org::W5WeDFNRRJNVzMJ1:000000000000000000000000000008Rvg
Date: Thu, 04 Sep 2008 18:08:11 +0200
In-Reply-To: <alpine.DEB.1.10.0809041646340.4454@aragorn.flymine.org> (Matthew
	Wakeling's message of "Thu\, 4 Sep 2008 16\:50\:00 +0100 \(BST\)")
Message-ID: <87wshrrj10.fsf@mnc.ch>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200809/53
X-Sequence-Number: 31366

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