From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Optimizer: limit not taken into account |
Date: | 2006-05-17 18:22:09 |
Message-ID: | 1147890129.2646.355.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-05-17 18:30:22 | Re: Optimizer: limit not taken into account |
Previous Message | Chris Mckenzie | 2006-05-17 17:50:22 | Performance/Maintenance test result collection |