explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dKcT

Settings
# exclusive inclusive rows x rows loops node
1. 27.158 18,952.266 ↑ 1.0 1 1

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

2. 3.991 18,925.108 ↓ 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,866.533..18,925.108 rows=10,037 loops=1)

3. 12,443.776 18,921.117 ↓ 1.5 10,037 1

HashAggregate (cost=2,859,765.28..2,860,152.02 rows=6,726 width=242) (actual time=18,866.525..18,921.117 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. 993.221 6,477.341 ↓ 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.882..6,477.341 rows=3,042,002 loops=1)

  • Hash Cond: (tblsegments.trip_id = v_transit_movement_extras.trip_id)
5. 2,912.433 5,385.764 ↓ 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=148.778..5,385.764 rows=3,042,002 loops=1)

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

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

7. 99.193 102.796 ↓ 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.140..102.796 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.603 3.603 ↓ 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.603..3.603 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.448 141.995 ↑ 1.0 300,768 1

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

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

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

12.          

SubPlan (forHash Left Join)

13. 0.710 0.710 ↑ 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.026..0.710 rows=4,957 loops=1)

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

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

  • Buckets: 524288 Batches: 1 Memory Usage: 20429kB
15. 47.312 47.312 ↑ 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.012..47.312 rows=298,650 loops=1)