explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m8O3

Settings
# exclusive inclusive rows x rows loops node
1. 3.709 5,268.339 ↑ 1.0 1 1

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

2. 0.525 5,264.630 ↓ 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,258.808..5,264.630 rows=1,333 loops=1)

3. 1,940.859 5,264.105 ↓ 3.2 1,333 1

HashAggregate (cost=1,427,145.65..1,427,163.63 rows=423 width=234) (actual time=5,258.800..5,264.105 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. 193.296 3,323.246 ↓ 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=998.110..3,323.246 rows=744,980 loops=1)

  • Join Filter: NULL::boolean
7. 986.552 3,129.950 ↓ 5.2 744,980 1

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

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

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

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

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

10. 19.898 763.996 ↓ 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=752.165..763.996 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.666 0.666 ↓ 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.666..0.666 rows=2,605 loops=1)

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

SubPlan (forBitmap Heap Scan)

13. 309.534 743.432 ↓ 1.3 35,084 1

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

  • Group Key: tblsegments_1.link_id
14. 154.550 433.898 ↑ 14.8 1,763,521 1

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

15. 0.752 0.752 ↑ 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.017..0.752 rows=964 loops=1)

  • Index Cond: (link_id = 945895182)
  • Heap Fetches: 0
16. 278.596 278.596 ↑ 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.018..0.289 rows=1,829 loops=964)

  • Index Cond: (trip_id = tblsegments_2.trip_id)
  • Heap Fetches: 0
17. 121.303 121.303 ↑ 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.009..0.091 rows=559 loops=1,333)

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

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

  • Buckets: 131072 Batches: 8 Memory Usage: 2784kB
19. 37.339 37.339 ↑ 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.011..37.339 rows=298,650 loops=1)

20. 61.903 145.568 ↑ 1.0 300,768 1

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

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

Seq Scan on tblmovements (cost=0.00..10,338.68 rows=300,768 width=19) (actual time=0.016..83.665 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