Re: Optimizer: limit not taken into account

[Simon Riggs <simon(at)2ndquadrant(dot)com>]

On Wed, 2006-05-17 at 08:54 -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.

The rows are not randomly distributed, so the SeqScan takes longer to
find 1 row than the index scan.

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com