explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c4xN

Settings
# exclusive inclusive rows x rows loops node
1. 3.699 5,293.105 ↑ 1.0 1 1

Aggregate (cost=1,427,168.92..1,427,168.93 rows=1 width=32) (actual time=5,293.105..5,293.105 rows=1 loops=1)

2. 0.546 5,289.406 ↓ 3.2 1,333 1

Subquery Scan on q (cost=1,427,145.65..1,427,167.86 rows=423 width=68) (actual time=5,283.592..5,289.406 rows=1,333 loops=1)

3. 1,935.195 5,288.860 ↓ 3.2 1,333 1

HashAggregate (cost=1,427,145.65..1,427,163.63 rows=423 width=234) (actual time=5,283.585..5,288.860 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..910.38 rows=3,338 width=66,619) (never executed)

6. 191.169 3,353.665 ↓ 5.2 744,980 1

Nested Loop Left Join (cost=1,212,975.41..1,419,448.14 rows=142,887 width=201) (actual time=980.001..3,353.665 rows=744,980 loops=1)

  • Join Filter: NULL::boolean
7. 1,059.112 3,162.496 ↓ 5.2 744,980 1

Hash Left Join (cost=1,212,975.41..1,418,019.27 rows=142,887 width=201) (actual time=979.998..3,162.496 rows=744,980 loops=1)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
8. 930.411 1,959.881 ↓ 5.2 744,980 1

Hash Left Join (cost=1,197,114.13..1,392,203.91 rows=142,887 width=198) (actual time=813.197..1,959.881 rows=744,980 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
9. 89.678 922.453 ↓ 5.2 744,980 1

Nested Loop (cost=1,187,032.50..1,372,472.20 rows=142,887 width=194) (actual time=704.371..922.453 rows=744,980 loops=1)

10. 19.309 715.471 ↓ 3.2 1,333 1

Bitmap Heap Scan on v_transit_roads (cost=1,187,031.93..1,195,130.91 rows=423 width=186) (actual time=704.355..715.471 rows=1,333 loops=1)

  • Recheck Cond: (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry)
  • Filter: ((hashed SubPlan 2) AND _st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry))
  • Rows Removed by Filter: 1272
  • Heap Blocks: exact=579
11. 0.474 0.474 ↓ 1.0 2,605 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..91.33 rows=2,539 width=0) (actual time=0.474..0.474 rows=2,605 loops=1)

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

SubPlan (forBitmap Heap Scan)

13. 306.121 695.688 ↓ 1.3 35,084 1

HashAggregate (cost=1,186,605.47..1,186,873.49 rows=26,802 width=8) (actual time=690.586..695.688 rows=35,084 loops=1)

  • Group Key: tblsegments_1.link_id
14. 151.250 389.567 ↑ 14.8 1,763,521 1

Nested Loop (cost=1.14..1,121,421.41 rows=26,073,626 width=8) (actual time=0.045..389.567 rows=1,763,521 loops=1)

15. 0.209 0.209 ↑ 10.0 964 1

Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_2 (cost=0.57..321.53 rows=9,655 width=8) (actual time=0.022..0.209 rows=964 loops=1)

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

Index Only Scan using idx_tblsegments_keyrev on tblsegments tblsegments_1 (cost=0.57..89.30 rows=2,682 width=16) (actual time=0.009..0.247 rows=1,829 loops=964)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
17. 117.304 117.304 ↑ 17.4 559 1,333

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..321.84 rows=9,741 width=16) (actual time=0.008..0.088 rows=559 loops=1,333)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
18. 67.864 107.017 ↑ 1.0 298,650 1

Hash (cost=4,889.50..4,889.50 rows=298,650 width=12) (actual time=107.017..107.017 rows=298,650 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2784kB
19. 39.153 39.153 ↑ 1.0 298,650 1

Seq Scan on v_transit_movement_extras (cost=0.00..4,889.50 rows=298,650 width=12) (actual time=0.013..39.153 rows=298,650 loops=1)

20. 61.589 143.503 ↑ 1.0 300,768 1

Hash (cost=10,338.68..10,338.68 rows=300,768 width=19) (actual time=143.503..143.503 rows=300,768 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2575kB
21. 81.914 81.914 ↑ 1.0 300,768 1

Seq Scan on tblmovements (cost=0.00..10,338.68 rows=300,768 width=19) (actual time=0.025..81.914 rows=300,768 loops=1)

22. 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