Re: Strange workaround for slow query - Mailing list pgsql-performance

> Thanks - I'm sorry that I was not more specific earlier, but what would
> be *really* helpful is the output of explain analyze, since that also
> shows actual time, # rows and # loops of the inner nestloop.

No problem at all.


EXPLAIN ANALYZE SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON
eventType_id=events_event_types.id WHERE severity=20 AND (eventType_id
IN (71)) ORDER BY datetime DESC limit 50;
                                                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.23..200.31 rows=50 width=131) (actual time=0.070..0.341 rows=50 loops=1)
   ->  Nested Loop  (cost=3.23..49139.16 rows=12466 width=131) (actual time=0.069..0.309 rows=50 loops=1)
         ->  Index Scan Backward using events_events_eventtype_id_datetime_ind on events_events  (cost=0.00..48886.61 rows=12466 width=93) (actual time=0.037..0.144 rows=50 loops=1)
               Index Cond: (eventtype_id = 71)
         ->  Materialize  (cost=3.23..3.24 rows=1 width=38) (actual time=0.001..0.001 rows=1 loops=50)
               ->  Seq Scan on events_event_types  (cost=0.00..3.23 rows=1 width=38) (actual time=0.024..0.029 rows=1 loops=1)
                     Filter: ((id = 71) AND (severity = 20))
 Total runtime: 0.415 ms
(8 rows)

Time: 1.290 ms


EXPLAIN ANALYZE SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON
eventType_id=events_event_types.id WHERE severity=70 AND (eventType_id
IN (71)) ORDER BY datetime DESC limit 50;
                                                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.23..200.31 rows=50 width=131) (actual time=11641.775..11641.775 rows=0 loops=1)
   ->  Nested Loop  (cost=3.23..49139.16 rows=12466 width=131) (actual time=11641.773..11641.773 rows=0 loops=1)
         ->  Index Scan Backward using events_events_eventtype_id_datetime_ind on events_events  (cost=0.00..48886.61 rows=12466 width=93) (actual time=0.035..11573.320 rows=50389 loops=1)
               Index Cond: (eventtype_id = 71)
         ->  Materialize  (cost=3.23..3.24 rows=1 width=38) (actual time=0.000..0.000 rows=0 loops=50389)
               ->  Seq Scan on events_event_types  (cost=0.00..3.23 rows=1 width=38) (actual time=0.028..0.028 rows=0 loops=1)
                     Filter: ((id = 71) AND (severity = 70))
 Total runtime: 11641.839 ms
(8 rows)

Time: 11642.902 ms


EXPLAIN ANALYZE SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON
eventType_id=events_event_types.id WHERE severity=70 AND (eventType_id
IN (71, 999)) ORDER BY datetime DESC LIMIT 50;
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=27290.26..27290.38 rows=50 width=131) (actual time=0.118..0.118 rows=0 loops=1)
   ->  Sort  (cost=27290.26..27303.17 rows=5164 width=131) (actual time=0.117..0.117 rows=0 loops=1)
         Sort Key: events_events.datetime
         Sort Method:  quicksort  Memory: 17kB
         ->  Nested Loop  (cost=22.95..27118.71 rows=5164 width=131) (actual time=0.112..0.112 rows=0 loops=1)
               ->  Seq Scan on events_event_types  (cost=0.00..3.02 rows=17 width=38) (actual time=0.016..0.041 rows=16 loops=1)
                     Filter: (severity = 70)
               ->  Bitmap Heap Scan on events_events  (cost=22.95..1589.94 rows=408 width=93) (actual time=0.002..0.002 rows=0 loops=16)
                     Recheck Cond: ((events_events.eventtype_id = ANY ('{71,999}'::bigint[])) AND (events_events.eventtype_id = events_event_types.id))
                     ->  Bitmap Index Scan on test_events_events_eventtype_id_severity_ind  (cost=0.00..22.85 rows=408 width=0) (actual time=0.001..0.001 rows=0 loops=16)
                           Index Cond: ((events_events.eventtype_id = ANY ('{71,999}'::bigint[])) AND (events_events.eventtype_id = events_event_types.id))
 Total runtime: 0.179 ms
(12 rows)

Time: 1.510 ms


> I'm
> wondering though why you do a left outer join. From the \d output in the
> previous mail, events_event.eventtype_id has a not null constraint and a
> fk to events_event_types.id, so an inner join would be appropriate.
> Outer joins limits the amount of join orders the planner considers, so a
> better plan might arise when the join is changed to inner.

I do agree with this assessment. I'm sort of improving the performance of an existing implementation of ours, for which I'm not aware why they chose for LEFT OUTER. I did, however, test things with INNER as well, with the same results, so I decided to stick with what I encountered in the existing implementation. But it's on my mind as well ;-)