explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6WSx

Settings
# exclusive inclusive rows x rows loops node
1. 27.075 19,028.160 ↑ 1.0 1 1

Aggregate (cost=2,860,236.10..2,860,236.11 rows=1 width=32) (actual time=19,028.160..19,028.160 rows=1 loops=1)

2. 3.916 19,001.085 ↓ 1.5 10,037 1

Subquery Scan on q (cost=2,859,765.28..2,860,219.28 rows=6,726 width=68) (actual time=18,942.524..19,001.085 rows=10,037 loops=1)

3. 12,479.681 18,997.169 ↓ 1.5 10,037 1

HashAggregate (cost=2,859,765.28..2,860,152.02 rows=6,726 width=242) (actual time=18,942.516..18,997.169 rows=10,037 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. 1,010.916 6,517.488 ↓ 2.7 3,042,002 1

Hash Left Join (cost=23,781.80..2,794,444.99 rows=1,136,005 width=209) (actual time=247.288..6,517.488 rows=3,042,002 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
5. 2,928.409 5,409.235 ↓ 2.7 3,042,002 1

Hash Left Join (cost=15,159.17..2,782,840.31 rows=1,136,005 width=205) (actual time=149.250..5,409.235 rows=3,042,002 loops=1)

  • Hash Cond: (tblsegments.trip_id = tblmovements.trip_id)
  • Filter: (hashed SubPlan 1)
  • Rows Removed by Filter: 4043457
6. 954.322 2,337.318 ↓ 3.1 7,085,459 1

Nested Loop (cost=713.86..2,762,430.86 rows=2,272,010 width=194) (actual time=4.109..2,337.318 rows=7,085,459 loops=1)

7. 98.957 102.500 ↓ 4.0 26,677 1

Bitmap Heap Scan on v_transit_roads (cost=713.29..30,978.25 rows=6,726 width=186) (actual time=4.079..102.500 rows=26,677 loops=1)

  • Recheck Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
  • Filter: _st_intersects(geometry, '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=3509
8. 3.543 3.543 ↓ 1.3 26,679 1

Bitmap Index Scan on v_transit_roads_geometry_geom_idx (cost=0.00..711.61 rows=20,177 width=0) (actual time=3.542..3.543 rows=26,679 loops=1)

  • Index Cond: (geometry && '0103000020110F00000100000005000000E17ADC72B5327041CDCCCC56F6E247C1E17ADC72B5327041EC51F818D67B48C185EB216BD1457041EC51F818D67B48C185EB216BD1457041CDCCCC56F6E247C1E17ADC72B5327041CDCCCC56F6E247C1'::geometry)
9. 1,280.496 1,280.496 ↑ 36.9 266 26,677

Index Only Scan using idx_tblsegments_key on tblsegments (cost=0.57..308.04 rows=9,806 width=16) (actual time=0.004..0.048 rows=266 loops=26,677)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
10. 56.565 142.795 ↑ 1.0 300,768 1

Hash (cost=10,338.68..10,338.68 rows=300,768 width=19) (actual time=142.794..142.795 rows=300,768 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20545kB
11. 86.230 86.230 ↑ 1.0 300,768 1

Seq Scan on tblmovements (cost=0.00..10,338.68 rows=300,768 width=19) (actual time=0.011..86.230 rows=300,768 loops=1)

12.          

SubPlan (forHash Left Join)

13. 0.713 0.713 ↑ 2.0 4,957 1

Index Only Scan using idx_tblsegments_key on tblsegments tblsegments_1 (cost=0.57..322.72 rows=9,723 width=8) (actual time=0.024..0.713 rows=4,957 loops=1)

  • Index Cond: (link_id = 1205053071)
  • Heap Fetches: 0
14. 50.330 97.337 ↑ 1.0 298,650 1

Hash (cost=4,889.50..4,889.50 rows=298,650 width=20) (actual time=97.337..97.337 rows=298,650 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20429kB
15. 47.007 47.007 ↑ 1.0 298,650 1

Seq Scan on v_transit_movement_extras (cost=0.00..4,889.50 rows=298,650 width=20) (actual time=0.009..47.007 rows=298,650 loops=1)