Re: Optimizer: limit not taken into account

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461