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