explain.depesz.com

PostgreSQL's explain analyze made readable

Result: i6LB : Optimization for: Optimization for: plan #YUOlv; plan #mbTV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 61.792 3,649.155 ↑ 1.0 5 1

Limit (cost=150,418.69..150,418.79 rows=5 width=125) (actual time=3,649.142..3,649.155 rows=5 loops=1)

  • Functions: 35
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 11.196 ms, Inlining 0.000 ms, Optimization 4.483 ms, Emission 56.842 ms, Total 72.522 ms
2. 0.013 3,587.363 ↑ 78.8 5 1

Unique (cost=150,418.69..150,426.57 rows=394 width=125) (actual time=3,587.351..3,587.363 rows=5 loops=1)

3. 0.309 3,587.350 ↑ 78.8 5 1

Sort (cost=150,418.69..150,419.67 rows=394 width=125) (actual time=3,587.349..3,587.350 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. 3.340 3,587.041 ↑ 2.6 152 1

Nested Loop Left Join (cost=171.07..150,401.70 rows=394 width=125) (actual time=3,154.440..3,587.041 rows=152 loops=1)

  • Filter: (((p.active IS TRUE) AND (s.publication_id = 1) 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
5. 6.661 3,583.701 ↓ 1.5 6,530 1

Nested Loop Left Join (cost=170.92..150,257.00 rows=4,369 width=155) (actual time=833.803..3,583.701 rows=6,530 loops=1)

6. 3.699 3,570.510 ↓ 1.5 6,530 1

Nested Loop Left Join (cost=170.64..148,978.24 rows=4,369 width=122) (actual time=831.754..3,570.510 rows=6,530 loops=1)

7. 2,396.733 3,547.221 ↓ 1.5 6,530 1

Hash Right Join (cost=170.22..147,030.57 rows=4,369 width=81) (actual time=831.714..3,547.221 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) AND (s.publication_id = 1))
  • Rows Removed by Join Filter: 4409277
8. 1,147.712 1,147.712 ↓ 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.089..1,147.712 rows=4,411,439 loops=1)

9. 1.502 2.776 ↑ 1.0 4,369 1

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

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

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

11. 19.590 19.590 ↓ 0.0 0 6,530

Index Scan using gtfs_trips_pkey on gtfs_trips t (cost=0.42..0.45 rows=1 width=66) (actual time=0.003..0.003 rows=0 loops=6,530)

  • Index Cond: ((publication_id = 1) AND (trip_id = st.trip_id))
12. 6.530 6.530 ↓ 0.0 0 6,530

Index Scan using gtfs_routes_pkey on gtfs_routes r (cost=0.27..0.29 rows=1 width=59) (actual time=0.001..0.001 rows=0 loops=6,530)

  • Index Cond: ((publication_id = 1) AND (route_id = t.route_id))
13. 0.000 0.000 ↑ 1.0 1 6,530

Materialize (cost=0.15..8.18 rows=1 width=1) (actual time=0.000..0.000 rows=1 loops=6,530)

14. 0.008 0.008 ↑ 1.0 1 1

Index Scan using publication_pkey on publication p (cost=0.15..8.17 rows=1 width=1) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (id = 1)
Execution time : 3,660.883 ms