To: Bruno Wolff III <bruno@wolff.to>
cc: "Craig A. James" <cjames@modgraph-usa.com>,
	pgsql-performance@postgresql.org
Subject: Re: Optimizer: limit not taken into account 
In-reply-to: <20060517174426.GA14567@wolff.to> 
References: <44699C6C.80302@tweakers.net> <4469D173.50406@sun.com>
	<4469D986.6090300@tweakers.net> <4469F55A.7040004@sun.com>
	<446A1520.2020601@tweakers.net> <446B0409.7050902@sun.com>
	<446B0ED2.5030909@tweakers.net> <446B474C.2050803@modgraph-usa.com>
	<20060517174426.GA14567@wolff.to>
Comments: In-reply-to Bruno Wolff III <bruno@wolff.to>
	message dated "Wed, 17 May 2006 12:44:26 -0500"
Date: Wed, 17 May 2006 14:30:22 -0400
Message-ID: <24245.1147890622@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bruno Wolff III <bruno@wolff.to> writes:
> I suspect it wasn't intended to be a full table scan. But rather a sequential
> scan until it found a matching row. If the data in the table is ordered by
> by id, this strategy may not work out well. Where as if the data is randomly
> ordered, it would be expected to find a match quickly.

Right.  You can see from the differential in the estimates for the
SeqScan and the Limit nodes that the planner is not expecting the
seqscan to run to completion, but rather to find a matching row quite
quickly.

There is not anything in there that considers whether the table's
physical order is so nonrandom that the search will take much longer
than it would given uniform distribution.  It might be possible to do
something with the correlation statistic in simple cases ...

			regards, tom lane