explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MFEf

Settings
# exclusive inclusive rows x rows loops node
1. 3.211 2,627.631 ↑ 1.0 1 1

Aggregate (cost=392,105.99..392,106.00 rows=1 width=32) (actual time=2,627.631..2,627.631 rows=1 loops=1)

2. 0.517 2,624.420 ↓ 1.6 1,333 1

Subquery Scan on q (cost=392,059.46..392,103.88 rows=846 width=68) (actual time=2,618.440..2,624.420 rows=1,333 loops=1)

3. 424.865 2,623.903 ↓ 1.6 1,333 1

HashAggregate (cost=392,059.46..392,095.42 rows=846 width=234) (actual time=2,618.431..2,623.903 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. 41.630 2,199.038 ↓ 5.1 148,397 1

Nested Loop Left Join (cost=21,867.74..389,776.47 rows=28,897 width=201) (actual time=402.719..2,199.038 rows=148,397 loops=1)

  • Join Filter: NULL::boolean
7. 1,568.674 2,157.408 ↓ 5.1 148,397 1

Hash Left Join (cost=21,867.74..389,487.50 rows=28,897 width=201) (actual time=402.716..2,157.408 rows=148,397 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
8. 95.797 372.273 ↓ 5.1 148,397 1

Hash Join (cost=11,786.11..376,233.02 rows=28,897 width=205) (actual time=75.441..372.273 rows=148,397 loops=1)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
9. 85.030 201.680 ↓ 2.6 750,626 1

Nested Loop (cost=92.11..363,374.98 rows=285,775 width=194) (actual time=0.595..201.680 rows=750,626 loops=1)

10. 9.374 9.845 ↓ 3.1 2,605 1

Bitmap Heap Scan on v_transit_roads (cost=91.54..8,184.17 rows=846 width=186) (actual time=0.579..9.845 rows=2,605 loops=1)

  • Recheck Cond: (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry)
  • Heap Blocks: exact=579
11. 0.471 0.471 ↓ 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.471..0.471 rows=2,605 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000003D0A977A991F7041AE47A194164A47C13D0A977A991F7041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041AE47A194164A47C13D0A977A991F7041AE47A194164A47C1'::geometry)
12. 106.805 106.805 ↑ 34.0 288 2,605

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..321.79 rows=9,806 width=16) (actual time=0.004..0.041 rows=288 loops=2,605)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
13. 0.281 74.796 ↑ 10.1 964 1

Hash (cost=11,572.47..11,572.47 rows=9,723 width=27) (actual time=74.796..74.796 rows=964 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 189kB
14. 30.994 74.515 ↑ 10.1 964 1

Hash Join (cost=444.26..11,572.47 rows=9,723 width=27) (actual time=0.568..74.515 rows=964 loops=1)

  • Hash Cond: (tblmovements.trip_id = tblsegments_1.trip_id)
15. 43.208 43.208 ↑ 1.0 300,768 1

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

16. 0.141 0.313 ↑ 10.1 964 1

Hash (cost=322.72..322.72 rows=9,723 width=8) (actual time=0.313..0.313 rows=964 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 166kB
17. 0.172 0.172 ↑ 10.1 964 1

Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_1 (cost=0.57..322.72 rows=9,723 width=8) (actual time=0.021..0.172 rows=964 loops=1)

  • Index Cond: (link_id = 945895182)
  • Heap Fetches: 0
18. 174.422 216.461 ↑ 1.0 298,650 1

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

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

20. 0.000 0.000 ↓ 0.0 0 148,397

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=148,397)

  • One-Time Filter: false