Thread: Adding "LIMIT 1" kills performance.
[Attn list-queue maintainers: Please drop the earlier version
of this email that I accidentally sent from an unsubscribed address. ]
Hi,
I'm having a strange problem with a slow-running select query. The
query I use in production ends in "LIMIT 1", and it runs very slowly.
But when I remove the "LIMIT 1", the query runs quite quickly. This
behavior has stumped a couple smart DBAs.
The full queries and EXPLAIN ANALYZE plans are included below, but by
way of explanation/observation:
1) The "LIMIT 1" case will sometimes be quicker (but still much slower
than the non-"LIMIT 1" case) for different values of
calendar_group_id.
2) The query below is a slightly simplified version of the one I
actually use. The real one includes more conditions which explain why
each table is joined. For reference, the original query is quoted at
the end [1]. The original query exhibits the same behavior as the
simplified versions w.r.t. the "LIMIT 1" case taking _much_ longer
(even longer than the simplified version) than the non-"LIMIT 1" case,
and uses the same plans.
Can anyone explain why such a slow plan is chosen when the "LIMIT 1"
is present? Is there anything I can do to speed this query up?
Thanks.
-chris
production=> select version();
version
------------------------------------------------------------------------------
PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by GCC x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
(1 row)
production=> analyze calendar_groups;
ANALYZE
production=> analyze calendar_links;
ANALYZE
production=> analyze calendars;
ANALYZE
production=> analyze event_updates;
ANALYZE
production=> EXPLAIN ANALYZE SELECT event_updates.*
FROM event_updates
INNER JOIN calendars ON event_updates.feed_id = calendars.id
INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
WHERE (calendar_links.calendar_group_id = 3640)
ORDER BY event_updates.id DESC
LIMIT 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Limit (cost=16.55..91.73 rows=1 width=2752) (actual time=27810.058..27810.059 rows=1 loops=1)
-> Nested Loop (cost=16.55..695694.18 rows=9254 width=2752) (actual time=27810.054..27810.054 rows=1 loops=1)
Join Filter: (event_updates.feed_id = calendars.id)
-> Index Scan Backward using event_updates_pkey on event_updates (cost=0.00..494429.30 rows=8944370
width=2752)(actual time=0.030..7452.142 rows=5135706 loops=1)
-> Materialize (cost=16.55..16.56 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=5135706)
-> Nested Loop (cost=0.00..16.55 rows=1 width=8) (actual time=0.029..0.034 rows=1 loops=1)
-> Index Scan using index_calendar_links_on_calendar_group_id_and_source_tracker_id on
calendar_links (cost=0.00..8.27 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: (calendar_group_id = 3640)
-> Index Scan using harvest_trackers_pkey on calendars (cost=0.00..8.27 rows=1 width=4) (actual
time=0.012..0.013rows=1 loops=1)
Index Cond: (calendars.id = calendar_links.source_tracker_id)
Total runtime: 27810.161 ms
(11 rows)
production=> EXPLAIN ANALYZE SELECT event_updates.* FROM event_updates
INNER JOIN calendars ON event_updates.feed_id = calendars.id
INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
WHERE (calendar_links.calendar_group_id = 3640)
ORDER BY event_updates.id DESC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Sort (cost=43376.36..43399.50 rows=9256 width=2752) (actual time=10.178..10.205 rows=36 loops=1)
Sort Key: event_updates.id
-> Nested Loop (cost=249.86..31755.56 rows=9256 width=2752) (actual time=9.957..10.098 rows=36 loops=1)
-> Nested Loop (cost=0.00..16.55 rows=1 width=8) (actual time=9.868..9.873 rows=1 loops=1)
-> Index Scan using index_calendar_links_on_calendar_group_id_and_source_tracker_id on calendar_links
(cost=0.00..8.27rows=1 width=4) (actual time=9.824..9.825 rows=1 loops=1)
Index Cond: (calendar_group_id = 3640)
-> Index Scan using harvest_trackers_pkey on calendars (cost=0.00..8.27 rows=1 width=4) (actual
time=0.034..0.036rows=1 loops=1)
Index Cond: (calendars.id = calendar_links.source_tracker_id)
-> Bitmap Heap Scan on event_updates (cost=249.86..31623.01 rows=9280 width=2752) (actual time=0.080..0.138
rows=36loops=1)
Recheck Cond: (event_updates.feed_id = calendars.id)
-> Bitmap Index Scan on index_event_updates_on_feed_id_and_feed_type (cost=0.00..247.54 rows=9280
width=0)(actual time=0.056..0.056 rows=36 loops=1)
Index Cond: (event_updates.feed_id = calendars.id)
Total runtime: 10.337 ms
(13 rows)
---------
[1] The original, unsimplified query:
SELECT event_updates.* FROM event_updates
INNER JOIN calendars ON (event_updates.feed_id = calendars.id AND event_updates.feed_type = E'Calendar')
INNER JOIN calendar_links ON (calendars.id = calendar_links.source_tracker_id AND calendars.type = E'SourceTracker')
WHERE (calendar_links.calendar_group_id = 3640 AND calendars.deactivated_at IS NULL)
ORDER BY event_updates.id DESC
LIMIT 1
Chris Shoemaker wrote: > [Attn list-queue maintainers: Please drop the earlier version > of this email that I accidentally sent from an unsubscribed address. ] > > Hi, > > I'm having a strange problem with a slow-running select query. The > query I use in production ends in "LIMIT 1", and it runs very slowly. > But when I remove the "LIMIT 1", the query runs quite quickly. This > behavior has stumped a couple smart DBAs. > > Can anyone explain why such a slow plan is chosen when the "LIMIT 1" > is present? Is there anything I can do to speed this query up? > Thanks. > From what I know using an ORDER BY and a LIMIT can often prevent *shortening* the query as it still needs to find all rows to perform the order by before it limits. The difference in plans eludes me. > production=> EXPLAIN ANALYZE SELECT event_updates.* > FROM event_updates > INNER JOIN calendars ON event_updates.feed_id = calendars.id > INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id > WHERE (calendar_links.calendar_group_id = 3640) > ORDER BY event_updates.id DESC > LIMIT 1; Does removing the DESC from the order by give the same variation in plans? Or is this only when using ORDER BY ... DESC LIMIT 1? One thing that interests me is try - EXPLAIN ANALYZE SELECT * FROM ( SELECT event_updates.* FROM event_updates INNER JOIN calendars ON event_updates.feed_id = calendars.id INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id WHERE (calendar_links.calendar_group_id = 3640) ORDER BY event_updates.id DESC ) AS foo LIMIT 1; -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
On Fri, May 30, 2008 at 02:23:46AM +0930, Shane Ambler wrote:
> Chris Shoemaker wrote:
>> [Attn list-queue maintainers: Please drop the earlier version
>> of this email that I accidentally sent from an unsubscribed address. ]
>>
>> Hi,
>> I'm having a strange problem with a slow-running select query. The
>> query I use in production ends in "LIMIT 1", and it runs very slowly.
>> But when I remove the "LIMIT 1", the query runs quite quickly. This
>> behavior has stumped a couple smart DBAs.
>>
>
>> Can anyone explain why such a slow plan is chosen when the "LIMIT 1"
>> is present? Is there anything I can do to speed this query up?
>> Thanks.
>>
>
> From what I know using an ORDER BY and a LIMIT can often prevent
> *shortening* the query as it still needs to find all rows to perform the
> order by before it limits.
That makes complete sense, of course.
> The difference in plans eludes me.
>
>> production=> EXPLAIN ANALYZE SELECT event_updates.*
>> FROM event_updates
>> INNER JOIN calendars ON event_updates.feed_id = calendars.id
>> INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
>> WHERE (calendar_links.calendar_group_id = 3640)
>> ORDER BY event_updates.id DESC
>> LIMIT 1;
>
> Does removing the DESC from the order by give the same variation in plans?
> Or is this only when using ORDER BY ... DESC LIMIT 1?
Except for using Index Scan instead of Index Scan Backward, the plan
is the same with ORDER BY ... or ORDER BY ... ASC as with ORDER BY
... DESC. In case you're wondering what would happen without the
ORDER BY at all:
production=> EXPLAIN SELECT event_updates.*
FROM event_updates
INNER JOIN calendars ON event_updates.feed_id = calendars.id
INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
WHERE (calendar_links.calendar_group_id = 3640)
LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.95 rows=1 width=2752)
-> Nested Loop (cost=0.00..36992.38 rows=9362 width=2752)
-> Nested Loop (cost=0.00..16.55 rows=1 width=8)
-> Index Scan using index_calendar_links_on_calendar_group_id_and_source_tracker_id on calendar_links
(cost=0.00..8.27rows=1 width=4)
Index Cond: (calendar_group_id = 3640)
-> Index Scan using harvest_trackers_pkey on calendars (cost=0.00..8.27 rows=1 width=4)
Index Cond: (calendars.id = calendar_links.source_tracker_id)
-> Index Scan using index_event_updates_on_feed_id_and_feed_type on event_updates (cost=0.00..36858.50
rows=9386width=2752)
Index Cond: (event_updates.feed_id = calendars.id)
(9 rows)
>
>
> One thing that interests me is try -
>
> EXPLAIN ANALYZE SELECT * FROM (
>
> SELECT event_updates.*
> FROM event_updates
> INNER JOIN calendars ON event_updates.feed_id = calendars.id
> INNER JOIN calendar_links ON calendars.id = calendar_links.source_tracker_id
> WHERE (calendar_links.calendar_group_id = 3640)
> ORDER BY event_updates.id DESC
> ) AS foo
>
> LIMIT 1;
That's an interesting experiment. Here are the results:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=16.55..91.74 rows=1 width=6027) (actual time=490709.355..490709.357 rows=1 loops=1)
-> Nested Loop (cost=16.55..703794.95 rows=9361 width=2752) (actual time=490709.352..490709.352 rows=1 loops=1)
Join Filter: (event_updates.feed_id = calendars.id)
-> Index Scan Backward using event_updates_pkey on event_updates (cost=0.00..500211.53 rows=9047416
width=2752)(actual time=0.222..469082.071 rows=5251179 loops=1)
-> Materialize (cost=16.55..16.56 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=5251179)
-> Nested Loop (cost=0.00..16.55 rows=1 width=8) (actual time=0.240..0.246 rows=1 loops=1)
-> Index Scan using index_calendar_links_on_calendar_group_id_and_source_tracker_id on
calendar_links (cost=0.00..8.27 rows=1 width=4) (actual time=0.108..0.109 rows=1 loops=1)
Index Cond: (calendar_group_id = 3640)
-> Index Scan using harvest_trackers_pkey on calendars (cost=0.00..8.27 rows=1 width=4) (actual
time=0.127..0.129rows=1 loops=1)
Index Cond: (calendars.id = calendar_links.source_tracker_id)
Total runtime: 490709.576 ms
(11 rows)
That is, no real change in the performance.
Still stumped,
-chris
Chris Shoemaker <chris.shoemaker@cox.net> writes:
> Still stumped,
The short answer here is that the planner is guessing that scanning the
index in ID order will come across the desired row (ie, the first one
matching the join condition) in less time than it will take to select
all the joinable rows, sort them by ID, and take the first one. It's
wrong in this case, but the plan is not unreasonable on its face.
The problem boils down to a misestimate of how many join rows there are.
You might get better results by increasing the statistics targets.
There are plenty of similar cases in the list archives.
regards, tom lane