explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q737 : springtales-dp CLA road tile test

Settings
# exclusive inclusive rows x rows loops node
1. 13.513 10,152.123 ↑ 1.0 1 1

Aggregate (cost=1,185,828.16..1,185,828.17 rows=1 width=32) (actual time=10,152.123..10,152.123 rows=1 loops=1)

2. 1.348 10,138.610 ↓ 1,333.0 1,333 1

Subquery Scan on q (cost=1,185,812.05..1,185,828.16 rows=1 width=68) (actual time=7,963.529..10,138.610 rows=1,333 loops=1)

3. 2,046.151 10,137.262 ↓ 1,333.0 1,333 1

GroupAggregate (cost=1,185,812.05..1,185,828.15 rows=1 width=233) (actual time=7,963.523..10,137.262 rows=1,333 loops=1)

  • Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
4.          

CTE boundaries

5. 0.000 0.000 ↓ 0.0 0

Seq Scan on v_transit_boundaries (cost=0.00..76.63 rows=563 width=102,301) (never executed)

6. 3,427.650 8,091.111 ↓ 2,204.1 744,980 1

Sort (cost=1,185,735.42..1,185,736.26 rows=338 width=200) (actual time=7,963.338..8,091.111 rows=744,980 loops=1)

  • Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
  • Sort Method: external sort Disk: 136520kB
7. 438.764 4,663.461 ↓ 2,204.1 744,980 1

Nested Loop Left Join (cost=1,180,833.52..1,185,721.22 rows=338 width=200) (actual time=478.212..4,663.461 rows=744,980 loops=1)

  • Join Filter: NULL::boolean
8. 174.540 4,224.697 ↓ 2,204.1 744,980 1

Nested Loop Left Join (cost=1,180,833.52..1,185,717.84 rows=338 width=200) (actual time=478.211..4,224.697 rows=744,980 loops=1)

9. 425.816 2,560.197 ↓ 2,204.1 744,980 1

Nested Loop Left Join (cost=1,180,833.10..1,183,504.39 rows=338 width=204) (actual time=478.202..2,560.197 rows=744,980 loops=1)

10. 57.023 644.421 ↓ 2,204.1 744,980 1

Nested Loop (cost=1,180,832.68..1,181,291.06 rows=338 width=193) (actual time=478.191..644.421 rows=744,980 loops=1)

11. 28.663 502.086 ↓ 1,333.0 1,333 1

Bitmap Heap Scan on v_transit_roads (cost=1,180,832.11..1,180,865.81 rows=1 width=185) (actual time=478.165..502.086 rows=1,333 loops=1)

  • Recheck Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
  • Filter: ((hashed SubPlan 2) AND _st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
  • Rows Removed by Filter: 1272
  • Heap Blocks: exact=563
12. 0.508 0.508 ↓ 325.6 2,605 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..4.37 rows=8 width=0) (actual time=0.507..0.508 rows=2,605 loops=1)

  • Index Cond: ((geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
13.          

SubPlan (forBitmap Heap Scan)

14. 227.448 472.915 ↓ 1.3 35,084 1

HashAggregate (cost=1,180,493.70..1,180,760.93 rows=26,723 width=8) (actual time=468.431..472.915 rows=35,084 loops=1)

  • Group Key: tblsegments_1.link_id
15. 75.643 245.467 ↑ 16.9 1,763,521 1

Nested Loop (cost=1.14..1,105,998.94 rows=29,797,906 width=8) (actual time=0.033..245.467 rows=1,763,521 loops=1)

16. 0.160 0.160 ↑ 9.9 964 1

Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_2 (cost=0.57..320.32 rows=9,586 width=8) (actual time=0.014..0.160 rows=964 loops=1)

  • Index Cond: (link_id = 945895182)
  • Heap Fetches: 0
17. 169.664 169.664 ↑ 1.5 1,829 964

Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..88.81 rows=2,653 width=16) (actual time=0.008..0.176 rows=1,829 loops=964)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
18. 85.312 85.312 ↑ 17.5 559 1,333

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..327.54 rows=9,770 width=16) (actual time=0.006..0.064 rows=559 loops=1,333)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
19. 1,489.960 1,489.960 ↑ 1.0 1 744,980

Index Scan using idx_tblmovements_agg on tblmovements (cost=0.42..6.55 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=744,980)

  • Index Cond: (tblsegments.trip_id = trip_id)
20. 1,489.960 1,489.960 ↑ 1.0 1 744,980

Index Scan using v_transit_movementcosts_trip_id on v_transit_movementcosts (cost=0.42..6.55 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=744,980)

  • Index Cond: (tblsegments.trip_id = trip_id)
21. 0.000 0.000 ↓ 0.0 0 744,980

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=744,980)

  • One-Time Filter: false