pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer: limit not taken into account
6+ messages / 5 participants
[nested] [flat]
* Optimizer: limit not taken into account
@ 2006-05-17 15:54 Craig A. James <cjames@modgraph-usa.com>
0 siblings, 3 replies; 6+ messages in thread
From: Craig A. James @ 2006-05-17 15:54 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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.
Adding the "order by" was a simple solution.
Craig
pg=> explain analyze select url, url_digest from url_queue where priority >= 10000000 limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------
Limit (cost=0.00..0.65 rows=1 width=108) (actual time=4036.113..4036.117 rows=1 loops=1)
-> Seq Scan on url_queue (cost=0.00..391254.35 rows=606176 width=108) (actual time=4036.101..4036.101 rows=1 loops=1)
Filter: (priority >= 10000000)
Total runtime: 4036.200 ms
(4 rows)
pg=> explain analyze select url, url_digest from url_queue where priority >= 10000000 order by priority limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=0.00..2.38 rows=1 width=112) (actual time=32.445..32.448 rows=1 loops=1)
-> Index Scan using url_queue_priority on url_queue (cost=0.00..1440200.41 rows=606176 width=112) (actual time=32.434..32.434 rows=1 loops=1)
Index Cond: (priority >= 10000000)
Total runtime: 32.566 ms
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer: limit not taken into account
@ 2006-05-17 17:44 Bruno Wolff III <bruno@wolff.to>
parent: Craig A. James <cjames@modgraph-usa.com>
2 siblings, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2006-05-17 17:44 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
Please don't reply to previous messages to start new threads. This makes it
harder to find stuff in the archives and may keep people from noticing your
message.
On Wed, May 17, 2006 at 08:54:52 -0700,
"Craig A. James" <cjames@modgraph-usa.com> 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 suspect it wasn't intended to be a full table scan. But rather a sequential
scan until it found a matching row. If the data in the table is ordered by
by id, this strategy may not work out well. Where as if the data is randomly
ordered, it would be expected to find a match quickly.
Have you analyzed the table recently? If the planner has bad stats on the
table, that is going to make it more likely to choose a bad plan.
> 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.
>
> Adding the "order by" was a simple solution.
>
> Craig
>
>
>
> pg=> explain analyze select url, url_digest from url_queue where priority
> >= 10000000 limit 1;
> QUERY PLAN
> ------------------------------------------------------------------------------------------
> Limit (cost=0.00..0.65 rows=1 width=108) (actual time=4036.113..4036.117
> rows=1 loops=1)
> -> Seq Scan on url_queue (cost=0.00..391254.35 rows=606176 width=108)
> (actual time=4036.101..4036.101 rows=1 loops=1)
> Filter: (priority >= 10000000)
> Total runtime: 4036.200 ms
> (4 rows)
>
> pg=> explain analyze select url, url_digest from url_queue where priority
> >= 10000000 order by priority limit 1;
> QUERY PLAN
> --------------------------------------------------------------------------------------
> Limit (cost=0.00..2.38 rows=1 width=112) (actual time=32.445..32.448
> rows=1 loops=1)
> -> Index Scan using url_queue_priority on url_queue
> (cost=0.00..1440200.41 rows=606176 width=112) (actual time=32.434..32.434
> rows=1 loops=1)
> Index Cond: (priority >= 10000000)
> Total runtime: 32.566 ms
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer: limit not taken into account
@ 2006-05-17 18:22 Simon Riggs <simon@2ndquadrant.com>
parent: Craig A. James <cjames@modgraph-usa.com>
2 siblings, 0 replies; 6+ messages in thread
From: Simon Riggs @ 2006-05-17 18:22 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer: limit not taken into account
@ 2006-05-17 18:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-05-17 18:30 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
Bruno Wolff III <bruno@wolff.to> writes:
> I suspect it wasn't intended to be a full table scan. But rather a sequential
> scan until it found a matching row. If the data in the table is ordered by
> by id, this strategy may not work out well. Where as if the data is randomly
> ordered, it would be expected to find a match quickly.
Right. You can see from the differential in the estimates for the
SeqScan and the Limit nodes that the planner is not expecting the
seqscan to run to completion, but rather to find a matching row quite
quickly.
There is not anything in there that considers whether the table's
physical order is so nonrandom that the search will take much longer
than it would given uniform distribution. It might be possible to do
something with the correlation statistic in simple cases ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer: limit not taken into account
@ 2006-05-17 18:58 Craig A. James <cjames@modgraph-usa.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Craig A. James @ 2006-05-17 18:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruno Wolff III <bruno@wolff.to>; pgsql-performance
Tom Lane wrote:
> There is not anything in there that considers whether the table's
> physical order is so nonrandom that the search will take much longer
> than it would given uniform distribution. It might be possible to do
> something with the correlation statistic in simple cases ...
In this case, the rows are not random at all, in fact they're inserted from a sequence, then rows are deleted as they are processed. If the planner is hoping for random physical distribution, this particular case is exactly wrong.
Craig
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer: limit not taken into account
@ 2006-05-17 19:20 Jim C. Nasby <jnasby@pervasive.com>
parent: Craig A. James <cjames@modgraph-usa.com>
2 siblings, 0 replies; 6+ messages in thread
From: Jim C. Nasby @ 2006-05-17 19:20 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
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 jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-05-17 19:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-17 15:54 Optimizer: limit not taken into account Craig A. James <cjames@modgraph-usa.com>
2006-05-17 17:44 ` Bruno Wolff III <bruno@wolff.to>
2006-05-17 18:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-05-17 18:58 ` Craig A. James <cjames@modgraph-usa.com>
2006-05-17 18:22 ` Simon Riggs <simon@2ndquadrant.com>
2006-05-17 19:20 ` Jim C. Nasby <jnasby@pervasive.com>
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