explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ryiv

Settings
# exclusive inclusive rows x rows loops node
1. 1,719.999 4,811.666 ↓ 3.2 1,333 1

HashAggregate (cost=1,416,740.43..1,416,757.35 rows=423 width=60) (actual time=4,808.460..4,811.666 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
2.          

CTE boundaries

3. 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)

4. 198.869 3,091.667 ↓ 5.2 744,980 1

Nested Loop Left Join (cost=1,212,975.41..1,409,400.14 rows=142,887 width=59) (actual time=937.858..3,091.667 rows=744,980 loops=1)

  • Join Filter: NULL::boolean
5. 1,070.155 2,892.798 ↓ 5.2 744,980 1

Hash Left Join (cost=1,212,975.41..1,407,971.27 rows=142,887 width=59) (actual time=937.855..2,892.798 rows=744,980 loops=1)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
6. 691.123 1,680.285 ↓ 5.2 744,980 1

Hash Left Join (cost=1,197,114.13..1,387,179.91 rows=142,887 width=56) (actual time=791.747..1,680.285 rows=744,980 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
7. 90.468 902.570 ↓ 5.2 744,980 1

Nested Loop (cost=1,187,032.50..1,372,472.20 rows=142,887 width=52) (actual time=703.471..902.570 rows=744,980 loops=1)

8. 14.791 710.794 ↓ 3.2 1,333 1

Bitmap Heap Scan on v_transit_roads (cost=1,187,031.93..1,195,130.91 rows=423 width=44) (actual time=703.453..710.794 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
9. 0.460 0.460 ↓ 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.459..0.460 rows=2,605 loops=1)

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

SubPlan (forBitmap Heap Scan)

11. 303.454 695.543 ↓ 1.3 35,084 1

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

  • Group Key: tblsegments_1.link_id
12. 153.776 392.089 ↑ 14.8 1,763,521 1

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

13. 0.205 0.205 ↑ 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.018..0.205 rows=964 loops=1)

  • Index Cond: (link_id = 945895182)
  • Heap Fetches: 0
14. 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
15. 101.308 101.308 ↑ 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.006..0.076 rows=559 loops=1,333)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
16. 50.962 86.592 ↑ 1.0 298,650 1

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

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

18. 60.820 142.358 ↑ 1.0 300,768 1

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

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

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

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