explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8xyi : Optimization for: Optimization for: plan #YUOlv; plan #5tp

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 59.635 3,372.462 ↑ 1.0 5 1

Limit (cost=156,886.20..156,886.30 rows=5 width=125) (actual time=3,372.449..3,372.462 rows=5 loops=1)

  • Functions: 42
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 11.971 ms, Inlining 0.000 ms, Optimization 3.209 ms, Emission 55.614 ms, Total 70.794 ms
2. 0.013 3,312.827 ↑ 78.8 5 1

Unique (cost=156,886.20..156,894.08 rows=394 width=125) (actual time=3,312.815..3,312.827 rows=5 loops=1)

3. 0.292 3,312.814 ↑ 78.8 5 1

Sort (cost=156,886.20..156,887.19 rows=394 width=125) (actual time=3,312.814..3,312.814 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. 0.113 3,312.522 ↑ 2.6 152 1

Nested Loop Left Join (cost=147,096.53..156,869.22 rows=394 width=125) (actual time=3,290.787..3,312.522 rows=152 loops=1)

5. 1.106 3,311.953 ↑ 2.6 152 1

Nested Loop Left Join (cost=147,096.26..156,748.96 rows=394 width=96) (actual time=3,290.772..3,311.953 rows=152 loops=1)

  • Filter: (((p.active IS TRUE) AND (s.stop_id = 'at:49:357:0:4'::text) AND (s.location_type = 0)) OR ((s.location_type IS NULL) 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: 6378
6. 66.273 3,304.317 ↓ 1.5 6,530 1

Hash Right Join (cost=147,096.10..155,843.70 rows=4,369 width=122) (actual time=3,215.386..3,304.317 rows=6,530 loops=1)

  • Hash Cond: ((t.publication_id = s.publication_id) AND (t.trip_id = st.trip_id))
7. 38.389 38.389 ↑ 1.0 243,652 1

Seq Scan on gtfs_trips t (cost=0.00..6,876.52 rows=243,652 width=70) (actual time=0.047..38.389 rows=243,652 loops=1)

8. 2.670 3,199.655 ↓ 1.5 6,530 1

Hash (cost=147,030.57..147,030.57 rows=4,369 width=81) (actual time=3,199.655..3,199.655 rows=6,530 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 617kB
9. 2,363.817 3,196.985 ↓ 1.5 6,530 1

Hash Right Join (cost=170.22..147,030.57 rows=4,369 width=81) (actual time=708.939..3,196.985 rows=6,530 loops=1)

  • Hash Cond: ((st.publication_id = s.publication_id) AND (st.stop_id = s.stop_id))
  • Join Filter: (s.stop_id = 'at:49:357:0:4'::text)
  • Rows Removed by Join Filter: 4409277
10. 829.713 829.713 ↓ 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.109..829.713 rows=4,411,439 loops=1)

11. 1.845 3.455 ↑ 1.0 4,369 1

Hash (cost=104.69..104.69 rows=4,369 width=43) (actual time=3.455..3.455 rows=4,369 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 379kB
12. 1.610 1.610 ↑ 1.0 4,369 1

Seq Scan on gtfs_stops s (cost=0.00..104.69 rows=4,369 width=43) (actual time=0.023..1.610 rows=4,369 loops=1)

13. 6.530 6.530 ↑ 1.0 1 6,530

Index Scan using publication_pkey on publication p (cost=0.15..0.18 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=6,530)

  • Index Cond: (id = s.publication_id)
14. 0.456 0.456 ↑ 1.0 1 152

Index Scan using gtfs_routes_pkey on gtfs_routes r (cost=0.27..0.31 rows=1 width=63) (actual time=0.003..0.003 rows=1 loops=152)

  • Index Cond: ((publication_id = s.publication_id) AND (route_id = t.route_id))
Execution time : 3,384.875 ms