Re: Optimizer: limit not taken into account

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
twoqueries: 
>
>    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