explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nwB4

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

WindowAgg (cost=40,997.85..40,997.87 rows=1 width=248) (actual rows= loops=)

2.          

CTE cte_trip_id_map

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=50.60..64.76 rows=708 width=45) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=50.60..52.37 rows=708 width=37) (actual rows= loops=)

  • Sort Key: t.run_ref
5. 0.000 0.000 ↓ 0.0

Seq Scan on trip_id_map t (cost=0.00..17.08 rows=708 width=37) (actual rows= loops=)

6.          

CTE link_map_runs

7. 0.000 0.000 ↓ 0.0

Sort (cost=40,933.06..40,933.06 rows=1 width=107) (actual rows= loops=)

  • Sort Key: d.route_id, d.run_ref, d.direction_id, d.estimated_departure_utc
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8,410.22..40,933.05 rows=1 width=107) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=8,409.94..40,932.74 rows=1 width=112) (actual rows= loops=)

  • Join Filter: ((t_1.run_ref)::text = (d.run_ref)::text)
10. 0.000 0.000 ↓ 0.0

Gather (cost=8,409.94..40,916.76 rows=1 width=80) (actual rows= loops=)

  • Workers Planned: 2
11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=7,409.94..39,916.66 rows=1 width=80) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Parallel Hash Join (cost=7,409.66..39,915.49 rows=4 width=73) (actual rows= loops=)

  • Hash Cond: ((d.route_id = rl.route_id) AND ((d.run_ref)::text = (rl.run_ref)::text) AND (d.stop_id = rl.stop_id))
13. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on ptv_departures d (cost=0.00..20,033.55 rows=423,655 width=55) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Parallel Hash (cost=4,624.24..4,624.24 rows=110,024 width=38) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on run_links rl (cost=0.00..4,624.24 rows=110,024 width=38) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using prm_route_ptv_id on ptv_route_map prm (cost=0.28..0.29 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (route_ptv_id = d.route_id)
17. 0.000 0.000 ↓ 0.0

CTE Scan on cte_trip_id_map t_1 (cost=0.00..15.93 rows=4 width=64) (actual rows= loops=)

  • Filter: (row_num = 1)
18. 0.000 0.000 ↓ 0.0

Index Scan using links_pkey on links l_1 (cost=0.28..0.30 rows=1 width=9) (actual rows= loops=)

  • Index Cond: (id = rl.link_id)
19. 0.000 0.000 ↓ 0.0

Sort (cost=0.03..0.04 rows=1 width=248) (actual rows= loops=)

  • Sort Key: l.run_ref, l.estimated_departure_utc
20. 0.000 0.000 ↓ 0.0

CTE Scan on link_map_runs l (cost=0.00..0.02 rows=1 width=248) (actual rows= loops=)