X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id BC0ED9FA279 for ; Wed, 17 May 2006 15:22:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 74209-09 for ; Wed, 17 May 2006 15:22:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sand4.gxn.net (sand4.gxn.net [195.147.249.239]) by postgresql.org (Postfix) with ESMTP id E75789F944D for ; Wed, 17 May 2006 15:22:11 -0300 (ADT) Received: from [62.232.55.118] (helo=[192.168.0.2]) by sand4.gxn.net with esmtp (Exim 4.50) id 1FgQdX-0000J4-HB; Wed, 17 May 2006 19:20:43 +0100 Subject: Re: Optimizer: limit not taken into account From: Simon Riggs To: "Craig A. James" Cc: pgsql-performance@postgresql.org In-Reply-To: <446B474C.2050803@modgraph-usa.com> References: <44699C6C.80302@tweakers.net> <4469D173.50406@sun.com> <4469D986.6090300@tweakers.net> <4469F55A.7040004@sun.com> <446A1520.2020601@tweakers.net> <446B0409.7050902@sun.com> <446B0ED2.5030909@tweakers.net> <446B474C.2050803@modgraph-usa.com> Content-Type: text/plain Date: Wed, 17 May 2006 19:22:09 +0100 Message-Id: <1147890129.2646.355.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.6.1 (2.6.1-1.fc5.2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/367 X-Sequence-Number: 19154 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