explain.depesz.com

PostgreSQL's explain analyze made readable

Result: joYx

Settings
# exclusive inclusive rows x rows loops node
1. 0.253 186,921.606 ↑ 1.0 1 1

Aggregate (cost=12,716,228.35..12,716,228.36 rows=1 width=32) (actual time=186,921.606..186,921.606 rows=1 loops=1)

2. 0.330 186,921.353 ↑ 3.1 274 1

Subquery Scan on q (cost=12,715,918.13..12,716,226.23 rows=850 width=44) (actual time=186,685.065..186,921.353 rows=274 loops=1)

3. 189.474 186,921.023 ↑ 3.1 274 1

GroupAggregate (cost=12,715,918.13..12,716,217.73 rows=850 width=255) (actual time=186,685.044..186,921.023 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=0.00..910.46 rows=3,346 width=75,373) (never executed)

6. 222.506 186,731.549 ↓ 8.2 36,284 1

Sort (cost=12,715,007.67..12,715,018.68 rows=4,404 width=211) (actual time=186,683.224..186,731.549 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: external merge Disk: 6824kB
7. 14.018 186,509.043 ↓ 8.2 36,284 1

Nested Loop Left Join (cost=268,707.17..12,714,741.13 rows=4,404 width=211) (actual time=893.047..186,509.043 rows=36,284 loops=1)

  • Join Filter: NULL::boolean
8. 297.941 186,495.025 ↓ 8.2 36,284 1

Hash Join (cost=268,707.17..12,714,697.09 rows=4,404 width=211) (actual time=893.041..186,495.025 rows=36,284 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
9. 87,363.592 186,186.806 ↓ 4.8 2,799,258 1

Hash Join (cost=256,949.74..12,701,418.76 rows=579,383 width=195) (actual time=844.136..186,186.806 rows=2,799,258 loops=1)

  • Hash Cond: (road_segments.link_id = v_transit_roads.link_id)
10. 97,983.143 97,983.143 ↑ 1.0 593,771,497 1

Seq Scan on road_segments (cost=0.00..10,885,811.20 rows=593,771,520 width=16) (actual time=0.013..97,983.143 rows=593,771,497 loops=1)

11. 2.484 840.071 ↓ 3.4 2,881 1

Hash (cost=256,939.11..256,939.11 rows=850 width=187) (actual time=840.069..840.071 rows=2,881 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 523kB
12. 837.587 837.587 ↓ 3.4 2,881 1

Seq Scan on v_transit_roads (cost=0.00..256,939.11 rows=850 width=187) (actual time=127.036..837.587 rows=2,881 loops=1)

  • Filter: ((geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry) AND _st_intersects(geometry, '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry))
  • Rows Removed by Filter: 868228
13. 1.055 10.278 ↑ 1.1 4,113 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 322kB
14. 8.675 9.223 ↑ 1.1 4,113 1

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

  • Recheck Cond: (commod_id = ANY ('{2}'::integer[]))
  • Heap Blocks: exact=2378
15. 0.548 0.548 ↑ 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=0.548..0.548 rows=4,113 loops=1)

  • Index Cond: (commod_id = ANY ('{2}'::integer[]))
16. 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 : 7.039 ms