explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bQpD

Settings
# exclusive inclusive rows x rows loops node
1. 850.322 20,855.458 ↓ 18.3 38,932 1

Merge Join (cost=15,806,789.86..15,809,723.84 rows=2,125 width=304) (actual time=20,110.788..20,855.458 rows=38,932 loops=1)

  • Merge Cond: (road_segments.trip_id = road_movements.trip_id)
2. 2,937.258 19,991.212 ↓ 4.7 2,746,691 1

Sort (cost=15,800,092.32..15,801,540.78 rows=579,383 width=195) (actual time=19,345.803..19,991.212 rows=2,746,691 loops=1)

  • Sort Key: road_segments.trip_id
  • Sort Method: quicksort Memory: 772445kB
3. 850.367 17,053.954 ↓ 4.8 2,799,258 1

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

4. 54.730 55.582 ↓ 3.4 2,881 1

Bitmap Heap Scan on v_transit_roads (cost=91.63..8,373.54 rows=850 width=187) (actual time=1.042..55.582 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
5. 0.852 0.852 ↓ 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=0.852..0.852 rows=2,882 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
6. 15,591.972 16,148.005 ↑ 5.2 972 2,881

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

  • Recheck Cond: (link_id = v_transit_roads.link_id)
  • Heap Blocks: exact=2799258
7. 556.033 556.033 ↑ 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=0.193..0.193 rows=972 loops=2,881)

  • Index Cond: (link_id = v_transit_roads.link_id)
8. 1.836 13.924 ↓ 1.1 2,367 1

Sort (cost=6,697.53..6,702.87 rows=2,137 width=117) (actual time=13.496..13.924 rows=2,367 loops=1)

  • Sort Key: road_movements.trip_id
  • Sort Method: quicksort Memory: 725kB
9. 11.682 12.088 ↓ 1.1 2,367 1

Bitmap Heap Scan on road_movements (cost=40.99..6,579.34 rows=2,137 width=117) (actual time=0.603..12.088 rows=2,367 loops=1)

  • Recheck Cond: (commod_id = 3)
  • Heap Blocks: exact=1220
10. 0.406 0.406 ↓ 1.1 2,367 1

Bitmap Index Scan on idx_road_movements_commod_id (cost=0.00..40.45 rows=2,137 width=0) (actual time=0.406..0.406 rows=2,367 loops=1)

  • Index Cond: (commod_id = 3)
Planning time : 6.467 ms