explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VIYQ : Optimization for: plan #rjKT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 23.956 1,032.653 ↑ 8.3 24 1

HashAggregate (cost=809,243.90..809,252.90 rows=200 width=72) (actual time=1,032.614..1,032.653 rows=24 loops=1)

  • Group Key: round(avg(((ssdrd.road_damage_value * '4'::numeric) + '1'::numeric)), 2)
2.          

Initplan (for HashAggregate)

3. 22.404 320.752 ↑ 1.0 1 1

Aggregate (cost=379,986.82..379,986.83 rows=1 width=8) (actual time=320.752..320.752 rows=1 loops=1)

4. 121.145 298.348 ↓ 1.2 108,219 1

Seq Scan on segment_data sd_1 (cost=0.00..379,757.94 rows=91,554 width=8) (actual time=0.034..298.348 rows=108,219 loops=1)

  • Filter: ((SubPlan 1) OR (SubPlan 2))
  • Rows Removed by Filter: 13853
5.          

SubPlan (for Seq Scan)

6. 0.000 122.072 ↑ 1.0 1 122,072

Limit (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=122,072)

7. 122.072 122.072 ↑ 1.0 1 122,072

Index Scan Backward using segment_data_segment_id_timeslot_direction_key on segment_data j (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=122,072)

  • Index Cond: ((segment_id = sd_1.segment_id) AND (direction = 1))
8. 0.000 55.131 ↑ 1.0 1 55,131

Limit (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=55,131)

9. 55.131 55.131 ↑ 1.0 1 55,131

Index Scan Backward using segment_data_segment_id_timeslot_direction_key on segment_data j_1 (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=55,131)

  • Index Cond: ((segment_id = sd_1.segment_id) AND (direction = '-1'::integer))
10. 96.482 687.945 ↓ 1.2 69,150 1

GroupAggregate (cost=422,936.17..427,827.74 rows=57,173 width=56) (actual time=575.923..687.945 rows=69,150 loops=1)

  • Group Key: sd.segment_id
11.          

Initplan (for GroupAggregate)

12. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on road_damage_types rd (cost=0.00..11.75 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((road_damage_type_name)::text = 'emi_raw'::text)
  • Rows Removed by Filter: 13
13. 88.536 591.458 ↓ 1.2 108,219 1

Sort (cost=422,924.42..423,153.30 rows=91,554 width=63) (actual time=575.905..591.458 rows=108,219 loops=1)

  • Sort Key: sd.segment_id
  • Sort Method: external merge Disk: 7808kB
14. 46.410 502.922 ↓ 1.2 108,219 1

Hash Join (cost=34,465.50..415,379.30 rows=91,554 width=63) (actual time=150.743..502.922 rows=108,219 loops=1)

  • Hash Cond: (sd.segment_data_id = ssdrd.segment_data_id)
15. 128.712 305.915 ↓ 1.2 108,219 1

Seq Scan on segment_data sd (cost=0.00..379,757.94 rows=91,554 width=60) (actual time=0.018..305.915 rows=108,219 loops=1)

  • Filter: ((SubPlan 5) OR (SubPlan 6))
  • Rows Removed by Filter: 13853
16.          

SubPlan (for Seq Scan)

17. 0.000 122.072 ↑ 1.0 1 122,072

Limit (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=122,072)

18. 122.072 122.072 ↑ 1.0 1 122,072

Index Scan Backward using segment_data_segment_id_timeslot_direction_key on segment_data j_2 (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=122,072)

  • Index Cond: ((segment_id = sd.segment_id) AND (direction = 1))
19. 0.000 55.131 ↑ 1.0 1 55,131

Limit (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=55,131)

20. 55.131 55.131 ↑ 1.0 1 55,131

Index Scan Backward using segment_data_segment_id_timeslot_direction_key on segment_data j_3 (cost=0.42..2.65 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=55,131)

  • Index Cond: ((segment_id = sd.segment_id) AND (direction = '-1'::integer))
21. 29.891 150.597 ↑ 1.0 122,072 1

Hash (cost=32,939.60..32,939.60 rows=122,072 width=7) (actual time=150.597..150.597 rows=122,072 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6114kB
22. 120.706 120.706 ↑ 1.0 122,072 1

Seq Scan on segment_data_to_road_damage ssdrd (cost=0.00..32,939.60 rows=122,072 width=7) (actual time=0.013..120.706 rows=122,072 loops=1)

  • Filter: (road_damage_type_id = $5)
  • Rows Removed by Filter: 1586936
Planning time : 0.514 ms
Execution time : 1,034.423 ms