explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3dKg

Settings
# exclusive inclusive rows x rows loops node
1. 0.235 145,749.107 ↑ 1.0 1 1

Aggregate (cost=10,015,759,442.91..10,015,759,442.92 rows=1 width=32) (actual time=145,749.106..145,749.107 rows=1 loops=1)

2. 0.357 145,748.872 ↑ 3.1 274 1

Subquery Scan on q (cost=10,015,759,132.69..10,015,759,440.78 rows=850 width=44) (actual time=145,561.165..145,748.872 rows=274 loops=1)

3. 185.802 145,748.515 ↑ 3.1 274 1

GroupAggregate (cost=10,015,759,132.69..10,015,759,432.28 rows=850 width=255) (actual time=145,561.105..145,748.515 rows=274 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=10,000,000,000.00..10,000,000,910.46 rows=3,346 width=75,373) (never executed)

6. 84.660 145,562.713 ↓ 8.2 36,284 1

Sort (cost=15,758,222.23..15,758,233.24 rows=4,404 width=211) (actual time=145,559.584..145,562.713 rows=36,284 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: quicksort Memory: 12500kB
7. 25.215 145,478.053 ↓ 8.2 36,284 1

Nested Loop Left Join (cost=11,944.69..15,757,955.68 rows=4,404 width=211) (actual time=19,687.423..145,478.053 rows=36,284 loops=1)

  • Join Filter: NULL::boolean
8. 770.069 145,452.838 ↓ 8.2 36,284 1

Hash Join (cost=11,944.69..15,757,911.64 rows=4,404 width=211) (actual time=19,687.418..145,452.838 rows=36,284 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 843.523 144,606.802 ↓ 4.8 2,799,258 1

Nested Loop (cost=187.26..15,744,633.32 rows=579,383 width=195) (actual time=283.433..144,606.802 rows=2,799,258 loops=1)

10. 461.590 727.391 ↓ 3.4 2,881 1

Bitmap Heap Scan on v_transit_roads (cost=91.63..8,373.54 rows=850 width=187) (actual time=274.773..727.391 rows=2,881 loops=1)

  • Recheck Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=599
11. 265.801 265.801 ↓ 1.1 2,882 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..91.42 rows=2,551 width=0) (actual time=265.801..265.801 rows=2,882 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
12. 137,731.967 143,035.888 ↑ 5.2 972 2,881

Bitmap Heap Scan on road_segments (cost=95.63..18,462.60 rows=5,065 width=16) (actual time=2.079..49.648 rows=972 loops=2,881)

  • Recheck Cond: (link_id = v_transit_roads.link_id)
  • Heap Blocks: exact=2799258
13. 5,303.921 5,303.921 ↑ 5.2 972 2,881

Bitmap Index Scan on idx_road_segments_link_id (cost=0.00..94.36 rows=5,065 width=0) (actual time=1.841..1.841 rows=972 loops=2,881)

  • Index Cond: (link_id = v_transit_roads.link_id)
14. 1.727 75.967 ↑ 1.1 4,113 1

Hash (cost=11,702.07..11,702.07 rows=4,429 width=32) (actual time=75.967..75.967 rows=4,113 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 322kB
15. 32.147 74.240 ↑ 1.1 4,113 1

Bitmap Heap Scan on road_movements (cost=86.75..11,702.07 rows=4,429 width=32) (actual time=42.477..74.240 rows=4,113 loops=1)

  • Recheck Cond: (commod_id = ANY ('{2}'::integer[]))
  • Heap Blocks: exact=2378
16. 42.093 42.093 ↑ 1.1 4,113 1

Bitmap Index Scan on idx_road_movements_commod_id (cost=0.00..85.64 rows=4,429 width=0) (actual time=42.093..42.093 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
17. 0.000 0.000 ↓ 0.0 0 36,284

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=36,284)

  • One-Time Filter: false
Planning time : 550.499 ms