From: Simon Riggs <simon@2ndquadrant.com>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer: limit not taken into account
Date: Wed, 17 May 2006 19:22:09 +0100
Message-ID: <1147890129.2646.355.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, cjames@modgraph-usa.com
Subject: Re: Optimizer: limit not taken into account
In-Reply-To: <1147890129.2646.355.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox