explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mbTV : Optimization for: plan #YUOlv

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 117.654 3,888.562 ↑ 1.0 5 1

Limit (cost=156,897.12..156,897.22 rows=5 width=125) (actual time=3,888.547..3,888.562 rows=5 loops=1)

  • Functions: 42
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 14.300 ms, Inlining 0.000 ms, Optimization 4.980 ms, Emission 110.755 ms, Total 130.036 ms
2. 0.014 3,770.908 ↑ 78.8 5 1

Unique (cost=156,897.12..156,905.00 rows=394 width=125) (actual time=3,770.895..3,770.908 rows=5 loops=1)

3. 0.344 3,770.894 ↑ 78.8 5 1

Sort (cost=156,897.12..156,898.10 rows=394 width=125) (actual time=3,770.893..3,770.894 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.049 3,770.550 ↑ 2.6 152 1

Nested Loop Left Join (cost=147,096.53..156,880.13 rows=394 width=125) (actual time=3,746.874..3,770.550 rows=152 loops=1)

5. 1.252 3,770.045 ↑ 2.6 152 1

Nested Loop Left Join (cost=147,096.26..156,759.87 rows=394 width=96) (actual time=3,746.859..3,770.045 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
6. 83.978 3,762.263 ↓ 1.5 6,530 1

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

  • Hash Cond: ((t.publication_id = s.publication_id) AND (t.trip_id = st.trip_id))
7. 50.135 50.135 ↑ 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.059..50.135 rows=243,652 loops=1)

8. 3.315 3,628.150 ↓ 1.5 6,530 1

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

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

Hash Right Join (cost=170.22..147,030.57 rows=4,369 width=81) (actual time=801.606..3,624.835 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
10. 1,174.380 1,174.380 ↓ 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.116..1,174.380 rows=4,411,439 loops=1)

11. 2.471 4.435 ↑ 1.0 4,369 1

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

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

Seq Scan on gtfs_stops s (cost=0.00..104.69 rows=4,369 width=43) (actual time=0.040..1.964 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,903.527 ms