explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jba2

Settings
# exclusive inclusive rows x rows loops node
1. 7,962.693 185,215.702 ↑ 7.3 36,284 1

Hash Join (cost=1,104,003.51..13,713,228.57 rows=265,200 width=192) (actual time=13,435.870..185,215.702 rows=36,284 loops=1)

  • Hash Cond: (road_segments.link_id = road_network.link_id)
2. 71,283.031 165,003.576 ↓ 1.8 7,329,912 1

Hash Join (cost=10,702.64..12,455,175.42 rows=3,978,007 width=125) (actual time=800.783..165,003.576 rows=7,329,912 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
3. 93,009.308 93,009.308 ↑ 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.228..93,009.308 rows=593,771,497 loops=1)

4. 6.302 711.237 ↓ 1.1 4,113 1

Hash (cost=10,653.84..10,653.84 rows=3,904 width=117) (actual time=711.236..711.237 rows=4,113 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 694kB
5. 629.853 704.935 ↓ 1.1 4,113 1

Bitmap Heap Scan on road_movements (cost=74.68..10,653.84 rows=3,904 width=117) (actual time=82.431..704.935 rows=4,113 loops=1)

  • Recheck Cond: (commod_id = 2)
  • Heap Blocks: exact=2378
6. 75.082 75.082 ↓ 1.1 4,113 1

Bitmap Index Scan on idx_road_movements_commod_id (cost=0.00..73.70 rows=3,904 width=0) (actual time=75.082..75.082 rows=4,113 loops=1)

  • Index Cond: (commod_id = 2)
7. 102.846 12,249.433 ↑ 37.4 5,872 1

Hash (cost=1,085,193.97..1,085,193.97 rows=219,592 width=176) (actual time=12,249.433..12,249.433 rows=5,872 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 322kB
8. 12,146.587 12,146.587 ↑ 37.4 5,872 1

Seq Scan on road_network (cost=0.00..1,085,193.97 rows=219,592 width=176) (actual time=32.563..12,146.587 rows=5,872 loops=1)

  • Filter: ((st_transform(geom, 3857) && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry) AND _st_intersects(st_transform(geom, 3857), '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry))
  • Rows Removed by Filter: 3288012
Planning time : 140.678 ms