Date: Thu, 29 May 2008 11:47:34 -0400
From: Chris Shoemaker <chris.shoemaker@cox.net>
To: pgsql-performance@postgresql.org
Cc: ejones@engineyard.com
Subject: Adding "LIMIT 1" kills performance.
Message-ID: <20080529154734.GA18273@pe.Belkin>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.17 (2007-11-01)

[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.013 rows=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.27 rows=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.036 rows=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=36 loops=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