explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dn2O

Settings
# exclusive inclusive rows x rows loops node
1. 778.094 95,701.852 ↓ 9.3 36,284 1

Hash Join (cost=10,889.90..15,756,866.56 rows=3,882 width=304) (actual time=9,644.886..95,701.852 rows=36,284 loops=1)

  • Hash Cond: (road_segments.trip_id = road_movements.trip_id)
2. 816.336 94,912.551 ↓ 4.8 2,799,258 1

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

3. 558.168 558.788 ↓ 3.4 2,881 1

Bitmap Heap Scan on v_transit_roads (cost=91.63..8,373.54 rows=850 width=187) (actual time=0.805..558.788 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
4. 0.620 0.620 ↓ 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.620..0.620 rows=2,882 loops=1)

  • Index Cond: (geometry && '0103000020110F000001000000050000008FC2B976272970415C8FE237662F48C18FC2B97627297041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B53270415C8FE237662F48C18FC2B976272970415C8FE237662F48C1'::geometry)
5. 88,083.694 93,537.427 ↑ 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.146..32.467 rows=972 loops=2,881)

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

  • Index Cond: (link_id = v_transit_roads.link_id)
7. 2.087 11.207 ↓ 1.1 4,113 1

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

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 694kB
8. 8.538 9.120 ↓ 1.1 4,113 1

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

  • Recheck Cond: (commod_id = 2)
  • Heap Blocks: exact=2378
9. 0.582 0.582 ↓ 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=0.582..0.582 rows=4,113 loops=1)

  • Index Cond: (commod_id = 2)
Planning time : 7.895 ms