Date: Wed, 17 May 2006 14:20:34 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "Craig A. James" <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer: limit not taken into account
Message-ID: <20060517192034.GW26212@pervasive.com>
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>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <446B474C.2050803@modgraph-usa.com>
User-Agent: Mutt/1.5.11

On Wed, May 17, 2006 at 08:54:52AM -0700, Craig A. James wrote:
> Here's a "corner case" that might interest someone.  It tripped up one of 
> our programmers.
> 
> We have a table with > 10 million rows.  The ID column is indexed, the 
> table has been vacuum/analyzed.  Compare these two queries:
> 
>   select * from tbl where id >= 10000000 limit 1;
>   select * from tbl where id >= 10000000 order by id limit 1;
> 
> The first takes 4 seconds, and uses a full table scan.  The second takes 32 
> msec and uses the index.  Details are below.
> 
> I understand why the planner makes the choices it does -- the "id > 
> 10000000" isn't very selective and under normal circumstances a full table 
> scan is probably the right choice.  But the "limit 1" apparently doesn't 
> alter the planner's strategy at all.  We were surprised by this.

Is it really not very selective? If there's 10000000 rows in the table,
and id starts at 1 with very few gaps, then >= 10000000 should actually
be very selective...

Also, I hope you understand there's a big difference between a limit
query that does and doesn't have an order by.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461