explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3LqB

Settings
# exclusive inclusive rows x rows loops node
1. 0.107 13.547 ↓ 167.0 167 1

Sort (cost=578.05..578.05 rows=1 width=68) (actual time=13.513..13.547 rows=167 loops=1)

  • Sort Key: merged_stop_events.actual_arrival DESC
  • Sort Method: quicksort Memory: 48kB
2.          

CTE merged_stop_events

3. 1.857 12.407 ↓ 2,262.0 2,262 1

Sort (cost=578.00..578.01 rows=1 width=52) (actual time=11.838..12.407 rows=2,262 loops=1)

  • Sort Key: (age(se2.actual_time, se1.actual_time))
  • Sort Method: quicksort Memory: 415kB
4. 3.896 10.550 ↓ 2,262.0 2,262 1

Merge Join (cost=510.12..577.99 rows=1 width=52) (actual time=5.507..10.550 rows=2,262 loops=1)

  • Merge Cond: ((se1.stop_id = se2.stop_id) AND (se1.route_id = se2.route_id) AND (se1.trip_id = se2.trip_id) AND (se1.bus_id = se2.bus_id))
  • Join Filter: ((se1.actual_time < se2.actual_time) AND (age(se2.actual_time, se1.actual_time) < '00:30:00'::interval))
  • Rows Removed by Join Filter: 36
5. 2.263 3.487 ↑ 1.1 2,368 1

Sort (cost=255.06..261.85 rows=2,714 width=36) (actual time=2.909..3.487 rows=2,368 loops=1)

  • Sort Key: se1.stop_id, se1.route_id, se1.trip_id, se1.bus_id
  • Sort Method: quicksort Memory: 282kB
6. 1.224 1.224 ↑ 1.1 2,368 1

Seq Scan on recent_stop_events se1 (cost=0.00..100.28 rows=2,714 width=36) (actual time=0.009..1.224 rows=2,368 loops=1)

  • Filter: is_arrival
  • Rows Removed by Filter: 2,497
7. 2.084 3.167 ↑ 1.1 2,515 1

Sort (cost=255.06..261.85 rows=2,714 width=32) (actual time=2.569..3.167 rows=2,515 loops=1)

  • Sort Key: se2.stop_id, se2.route_id, se2.trip_id, se2.bus_id
  • Sort Method: quicksort Memory: 292kB
8. 1.083 1.083 ↑ 1.1 2,497 1

Seq Scan on recent_stop_events se2 (cost=0.00..100.28 rows=2,714 width=32) (actual time=0.003..1.083 rows=2,497 loops=1)

  • Filter: (NOT is_arrival)
  • Rows Removed by Filter: 2,368
9. 13.440 13.440 ↓ 167.0 167 1

CTE Scan on merged_stop_events (cost=0.00..0.03 rows=1 width=68) (actual time=11.846..13.440 rows=167 loops=1)

  • Filter: (actual_arrival >= (now() - '01:00:00'::interval))
  • Rows Removed by Filter: 2,095
Planning time : 0.251 ms
Execution time : 13.709 ms