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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
