X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 080253A49E7 for ; Fri, 29 Oct 2004 00:54:39 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 92614-10 for ; Thu, 28 Oct 2004 23:54:27 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with ESMTP id 4F63C3A49F8 for ; Fri, 29 Oct 2004 00:54:30 +0100 (BST) Received: by flake.decibel.org (Postfix, from userid 1001) id BBC521C902; Thu, 28 Oct 2004 23:54:30 +0000 (GMT) Date: Thu, 28 Oct 2004 18:54:30 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: John Meinel , pgsql-performance@postgresql.org Subject: Re: Sequential Scan with LIMIT Message-ID: <20041028235430.GJ55164@decibel.org> References: <417C023F.1080502@johnmeinel.com> <17689.1098648713@sss.pgh.pa.us> <20041028234658.GI55164@decibel.org> <9172.1099007368@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9172.1099007368@sss.pgh.pa.us> X-Operating-System: FreeBSD 4.10-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/467 X-Sequence-Number: 8943 On Thu, Oct 28, 2004 at 07:49:28PM -0400, Tom Lane wrote: > "Jim C. Nasby" writes: > > On Sun, Oct 24, 2004 at 04:11:53PM -0400, Tom Lane wrote: > >> The test case you are showing is probably suffering from nonrandom > >> placement of this particular data value; which is something that the > >> statistics we keep are too crude to detect. > > > Isn't that exactly what pg_stats.correlation is? > > No. A far-from-zero correlation gives you a clue that on average, *all* > the data values are placed nonrandomly ... but it doesn't really tell > you much one way or the other about a single data value. Maybe I'm confused about what the original issue was then... it appeared that you were suggesting PGSQL was doing a seq scan instead of an index scan because it thought it would find it on the first page if the data was randomly distributed. If the correlation is highly non-zero though, shouldn't it 'play it safe' and assume that unless it's picking the min or max value stored in statistics it will be better to do an index scan, since the value it's looking for is probably in the middle of the table somewhere? IE: if the values in the field are between 1 and 5 and the table is clustered on that field then clearly an index scan would be better to find a row with field=3 than a seq scan. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"