Re: Optimizer: limit not taken into account - Mailing list pgsql-performance

From Simon Riggs
Subject Re: Optimizer: limit not taken into account
Date
Msg-id 1147890129.2646.355.camel@localhost.localdomain
Whole thread Raw
In response to Optimizer: limit not taken into account  ("Craig A. James" <cjames@modgraph-usa.com>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Chris Mckenzie
Date:
Subject: Performance/Maintenance test result collection
Next
From: Tom Lane
Date:
Subject: Re: Optimizer: limit not taken into account