explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TauQ : Optimization for: plan #F4Rc

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.009 3,858.146 ↑ 1.0 3 1

Sort (cost=643,848.39..643,848.40 rows=3 width=48) (actual time=3,858.146..3,858.146 rows=3 loops=1)

  • Sort Key: (CASE WHEN (p.timeslot = 0) THEN 1000000 ELSE p.timeslot END) DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.024 3,858.137 ↑ 1.0 3 1

Subquery Scan on p (cost=342,442.69..643,848.37 rows=3 width=48) (actual time=1,248.642..3,858.137 rows=3 loops=1)

3. 0.011 437.525 ↑ 1.0 3 1

HashAggregate (cost=342,442.69..342,442.72 rows=3 width=12) (actual time=437.521..437.525 rows=3 loops=1)

  • Group Key: points.timeslot, (count(points.id))
4. 0.002 437.514 ↑ 1.0 3 1

Append (cost=166,769.22..342,442.67 rows=3 width=12) (actual time=168.398..437.514 rows=3 loops=1)

5. 0.002 168.400 ↑ 1.0 2 1

Unique (cost=166,769.22..166,769.30 rows=2 width=12) (actual time=168.397..168.400 rows=2 loops=1)

6. 0.005 168.398 ↑ 1.0 2 1

Finalize GroupAggregate (cost=166,769.22..166,769.30 rows=2 width=12) (actual time=168.397..168.398 rows=2 loops=1)

  • Group Key: points.timeslot
7. 0.008 168.393 ↓ 1.2 10 1

Sort (cost=166,769.22..166,769.24 rows=8 width=12) (actual time=168.392..168.393 rows=10 loops=1)

  • Sort Key: points.timeslot
  • Sort Method: quicksort Memory: 25kB
8. 28.489 168.385 ↓ 1.2 10 1

Gather (cost=166,768.28..166,769.10 rows=8 width=12) (actual time=164.301..168.385 rows=10 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 22.545 139.896 ↑ 1.0 2 5

Partial HashAggregate (cost=165,768.28..165,768.30 rows=2 width=12) (actual time=139.895..139.896 rows=2 loops=5)

  • Group Key: points.timeslot
10. 105.421 117.351 ↑ 1.2 34,399 5

Parallel Bitmap Heap Scan on points (cost=3,091.98..165,555.21 rows=42,614 width=41) (actual time=2.824..117.351 rows=34,399 loops=5)

  • Recheck Cond: (timeslot IS NOT NULL)
  • Filter: is_timeslot_latest
  • Heap Blocks: exact=5127
11. 11.930 11.930 ↓ 1.0 171,995 1

Bitmap Index Scan on points_default_timeslot_filter_idx (cost=0.00..3,049.37 rows=170,457 width=0) (actual time=11.930..11.930 rows=171,995 loops=1)

  • Index Cond: ((timeslot IS NOT NULL) AND (is_timeslot_latest = true))
12. 49.811 269.112 ↑ 1.0 1 1

Aggregate (cost=175,673.33..175,673.34 rows=1 width=12) (actual time=269.112..269.112 rows=1 loops=1)

13. 206.007 219.301 ↓ 1.0 170,478 1

Bitmap Heap Scan on points points_1 (cost=12,644.39..175,250.54 rows=169,119 width=37) (actual time=14.969..219.301 rows=170,478 loops=1)

  • Filter: is_all_timeslots_latest
  • Heap Blocks: exact=13292
14. 13.294 13.294 ↓ 1.0 170,478 1

Bitmap Index Scan on points_default_filter_idx (cost=0.00..12,602.11 rows=169,119 width=0) (actual time=13.294..13.294 rows=170,478 loops=1)

  • Index Cond: (is_all_timeslots_latest = true)
15.          

SubPlan (for Subquery Scan)

16. 24.120 1,947.837 ↑ 1.0 1 3

Aggregate (cost=50,234.25..50,234.27 rows=1 width=32) (actual time=649.279..649.279 rows=1 loops=3)

17. 144.699 1,923.717 ↓ 5.8 46,453 3

Nested Loop (cost=1,912.80..50,214.18 rows=8,027 width=3) (actual time=27.004..641.239 rows=46,453 loops=3)

18. 74.910 330.306 ↓ 5.0 80,484 3

Nested Loop (cost=1,912.38..17,980.49 rows=16,055 width=7) (actual time=20.983..110.102 rows=80,484 loops=3)

19. 0.018 0.018 ↑ 1.0 1 3

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

  • Filter: ((road_damage_type_name)::text = 'emi_gebr'::text)
  • Rows Removed by Filter: 13
20. 210.219 255.378 ↑ 2.0 80,484 3

Bitmap Heap Scan on street_segment_data_to_road_damage ssdrd (cost=1,912.38..16,363.25 rows=160,549 width=11) (actual time=20.974..85.126 rows=80,484 loops=3)

  • Recheck Cond: (road_damage_type_id = rd.road_damage_type_id)
  • Heap Blocks: exact=36960
21. 45.159 45.159 ↑ 1.0 160,549 3

Bitmap Index Scan on street_segment_data_to_road_damage_road_damage_type_id_idx (cost=0.00..1,872.25 rows=160,549 width=0) (actual time=15.053..15.053 rows=160,549 loops=3)

  • Index Cond: (road_damage_type_id = rd.road_damage_type_id)
22. 482.904 1,448.712 ↑ 1.0 1 241,452

Index Scan using street_segment_data_pkey on street_segment_data ssd (cost=0.42..2.01 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=241,452)

  • Index Cond: (street_segment_data_id = ssdrd.street_segment_data_id)
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 0
23.          

SubPlan (for Index Scan)

24. 241.452 965.808 ↑ 1.0 1 241,452

Limit (cost=0.42..2.71 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=241,452)

25. 724.356 724.356 ↑ 1.0 1 241,452

Index Scan Backward using street_segment_data_segment_id_timeslot_key on street_segment_data j (cost=0.42..2.71 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=241,452)

  • Index Cond: (segment_id = ssd.segment_id)
  • Filter: ((p.timeslot = 0) OR ((timeslot >= ((((round(((p.timeslot / 100))::double precision))::integer * 100) + (((((((p.timeslot)::numeric / '100'::numeric) % '1'::numeric) * '100'::numeric))::integer - 1) * 26)) + 1)) AND (timeslot <= (((round(((p.timeslot / 100))::double precision))::integer * 100) + ((((((p.timeslot)::numeric / '100'::numeric) % '1'::numeric) * '100'::numeric))::integer * 26)))))
  • Rows Removed by Filter: 0
26. 23.448 1,472.751 ↑ 1.0 1 3

Aggregate (cost=50,234.25..50,234.27 rows=1 width=32) (actual time=490.917..490.917 rows=1 loops=3)

27. 5.910 1,449.303 ↓ 5.8 46,453 3

Nested Loop (cost=1,912.80..50,214.18 rows=8,027 width=3) (actual time=25.573..483.101 rows=46,453 loops=3)

28. 48.117 236.133 ↓ 5.0 80,484 3

Nested Loop (cost=1,912.38..17,980.49 rows=16,055 width=7) (actual time=13.667..78.711 rows=80,484 loops=3)

29. 0.018 0.018 ↑ 1.0 1 3

Seq Scan on road_damage_types rd_1 (cost=0.00..11.75 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=3)

  • Filter: ((road_damage_type_name)::text = 'emi_subs'::text)
  • Rows Removed by Filter: 13
30. 154.584 187.998 ↑ 2.0 80,484 3

Bitmap Heap Scan on street_segment_data_to_road_damage ssdrd_1 (cost=1,912.38..16,363.25 rows=160,549 width=11) (actual time=13.658..62.666 rows=80,484 loops=3)

  • Recheck Cond: (road_damage_type_id = rd_1.road_damage_type_id)
  • Heap Blocks: exact=36960
31. 33.414 33.414 ↑ 1.0 160,549 3

Bitmap Index Scan on street_segment_data_to_road_damage_road_damage_type_id_idx (cost=0.00..1,872.25 rows=160,549 width=0) (actual time=11.138..11.138 rows=160,549 loops=3)

  • Index Cond: (road_damage_type_id = rd_1.road_damage_type_id)
32. 482.904 1,207.260 ↑ 1.0 1 241,452

Index Scan using street_segment_data_pkey on street_segment_data ssd_1 (cost=0.42..2.01 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=241,452)

  • Index Cond: (street_segment_data_id = ssdrd_1.street_segment_data_id)
  • Filter: (SubPlan 3)
  • Rows Removed by Filter: 0
33.          

SubPlan (for Index Scan)

34. 241.452 724.356 ↑ 1.0 1 241,452

Limit (cost=0.42..2.71 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=241,452)

35. 482.904 482.904 ↑ 1.0 1 241,452

Index Scan Backward using street_segment_data_segment_id_timeslot_key on street_segment_data j_1 (cost=0.42..2.71 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=241,452)

  • Index Cond: (segment_id = ssd_1.segment_id)
  • Filter: ((p.timeslot = 0) OR ((timeslot >= ((((round(((p.timeslot / 100))::double precision))::integer * 100) + (((((((p.timeslot)::numeric / '100'::numeric) % '1'::numeric) * '100'::numeric))::integer - 1) * 26)) + 1)) AND (timeslot <= (((round(((p.timeslot / 100))::double precision))::integer * 100) + ((((((p.timeslot)::numeric / '100'::numeric) % '1'::numeric) * '100'::numeric))::integer * 26)))))
  • Rows Removed by Filter: 0
Planning time : 1.070 ms
Execution time : 3,859.036 ms