On Wed, May 17, 2006 at 08:54:52AM -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. > > I understand why the planner makes the choices it does -- the "id > > 10000000" isn't very selective and under normal circumstances a full table > scan is probably the right choice. But the "limit 1" apparently doesn't > alter the planner's strategy at all. We were surprised by this.
Is it really not very selective? If there's 10000000 rows in the table, and id starts at 1 with very few gaps, then >= 10000000 should actually be very selective... Also, I hope you understand there's a big difference between a limit query that does and doesn't have an order by. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
