explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YUOlv

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 37.968 1,760.056 ↓ 5.0 5 1

Limit (cost=155,586.86..155,586.88 rows=1 width=125) (actual time=1,760.043..1,760.056 rows=5 loops=1)

2. 0.016 1,722.088 ↓ 5.0 5 1

Unique (cost=155,586.86..155,586.88 rows=1 width=125) (actual time=1,722.075..1,722.088 rows=5 loops=1)

3. 0.292 1,722.072 ↓ 5.0 5 1

Sort (cost=155,586.86..155,586.86 rows=1 width=125) (actual time=1,722.071..1,722.072 rows=5 loops=1)

  • Sort Key: st.departure_time, s.stop_name, r.route_short_name, r.route_long_name, t.trip_id, t.direction_id, t.trip_headsign
  • Sort Method: quicksort Memory: 65kB
4. 6.490 1,721.780 ↓ 152.0 152 1

Nested Loop Left Join (cost=166.90..155,586.85 rows=1 width=125) (actual time=1,508.267..1,721.780 rows=152 loops=1)

  • Join Filter: ((s.publication_id = r.publication_id) AND (r.route_id = t.route_id))
  • Rows Removed by Join Filter: 52842
5. 1.128 1,711.034 ↓ 152.0 152 1

Hash Left Join (cost=166.90..155,566.27 rows=1 width=96) (actual time=1,508.171..1,711.034 rows=152 loops=1)

  • Hash Cond: (s.publication_id = p.id)
  • Filter: (((p.active IS TRUE) AND (s.location_type = 0)) OR ((s.location_type IS NULL) AND (s.stop_id = 'at:49:357:0:4'::text) AND (st.departure_time_in_seconds > 36000) AND (t.service_id = ANY ('{T0,T0+31,T0#4,T0#5,T0#6,T5#1,TA}'::text[]))))
  • Rows Removed by Filter: 2010
6. 1.846 1,709.897 ↓ 2.1 2,162 1

Nested Loop Left Join (cost=126.97..155,523.67 rows=1,010 width=122) (actual time=408.253..1,709.897 rows=2,162 loops=1)

7. 1,045.154 1,695.079 ↓ 2.1 2,162 1

Hash Right Join (cost=126.55..146,999.27 rows=1,010 width=81) (actual time=408.228..1,695.079 rows=2,162 loops=1)

  • Hash Cond: ((st.publication_id = s.publication_id) AND (st.stop_id = s.stop_id))
8. 648.374 648.374 ↓ 1.0 4,411,439 1

Seq Scan on gtfs_stop_times st (cost=0.00..123,702.39 rows=4,411,039 width=56) (actual time=0.075..648.374 rows=4,411,439 loops=1)

9. 0.013 1.551 ↑ 1.0 1 1

Hash (cost=126.53..126.53 rows=1 width=43) (actual time=1.551..1.551 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 1.538 1.538 ↑ 1.0 1 1

Seq Scan on gtfs_stops s (cost=0.00..126.53 rows=1 width=43) (actual time=1.131..1.538 rows=1 loops=1)

  • Filter: ((location_type = 0) OR ((location_type IS NULL) AND (stop_id = 'at:49:357:0:4'::text)))
  • Rows Removed by Filter: 4368
11. 12.972 12.972 ↑ 1.0 1 2,162

Index Scan using gtfs_trips_pkey on gtfs_trips t (cost=0.42..8.44 rows=1 width=70) (actual time=0.006..0.006 rows=1 loops=2,162)

  • Index Cond: ((publication_id = s.publication_id) AND (trip_id = st.trip_id))
12. 0.003 0.009 ↑ 1,330.0 1 1

Hash (cost=23.30..23.30 rows=1,330 width=5) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
13. 0.006 0.006 ↑ 1,330.0 1 1

Seq Scan on publication p (cost=0.00..23.30 rows=1,330 width=5) (actual time=0.006..0.006 rows=1 loops=1)

14. 4.256 4.256 ↑ 1.3 349 152

Seq Scan on gtfs_routes r (cost=0.00..13.63 rows=463 width=63) (actual time=0.001..0.028 rows=349 loops=152)