explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UgGt : Optimization for: Optimization for: plan #F4Rc; plan #TauQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.011 2,929.963 ↑ 1.0 3 1

Sort (cost=482,936.16..482,936.17 rows=3 width=48) (actual time=2,929.962..2,929.963 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.027 2,929.952 ↑ 1.0 3 1

Subquery Scan on p (cost=181,530.46..482,936.14 rows=3 width=48) (actual time=1,214.689..2,929.952 rows=3 loops=1)

3. 0.010 244.289 ↑ 1.0 3 1

HashAggregate (cost=181,530.46..181,530.49 rows=3 width=12) (actual time=244.286..244.289 rows=3 loops=1)

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

Append (cost=166,765.34..181,530.44 rows=3 width=12) (actual time=186.258..244.279 rows=3 loops=1)

5. 0.002 186.260 ↑ 1.0 2 1

Unique (cost=166,765.34..166,765.42 rows=2 width=12) (actual time=186.257..186.260 rows=2 loops=1)

6. 0.004 186.258 ↑ 1.0 2 1

Finalize GroupAggregate (cost=166,765.34..166,765.42 rows=2 width=12) (actual time=186.257..186.258 rows=2 loops=1)

  • Group Key: points.timeslot
7. 0.012 186.254 ↓ 1.2 10 1

Sort (cost=166,765.34..166,765.36 rows=8 width=12) (actual time=186.252..186.254 rows=10 loops=1)

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

Gather (cost=166,764.40..166,765.22 rows=8 width=12) (actual time=181.284..186.242 rows=10 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 5.599 147.319 ↑ 1.0 2 5

Partial HashAggregate (cost=165,764.40..165,764.42 rows=2 width=12) (actual time=147.318..147.319 rows=2 loops=5)

  • Group Key: points.timeslot
10. 130.163 141.720 ↑ 1.2 33,775 5

Parallel Bitmap Heap Scan on points (cost=3,091.34..165,554.56 rows=41,968 width=41) (actual time=4.756..141.720 rows=33,775 loops=5)

  • Recheck Cond: (timeslot IS NOT NULL)
  • Filter: ((is_timeslot_latest IS TRUE) AND (is_preview IS FALSE) AND (is_duplicate IS FALSE))
  • Rows Removed by Filter: 624
  • Heap Blocks: exact=3930
11. 11.557 11.557 ↓ 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.557..11.557 rows=171,995 loops=1)

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

Finalize Aggregate (cost=14,764.98..14,764.99 rows=1 width=12) (actual time=58.018..58.018 rows=1 loops=1)

13. 39.893 58.012 ↓ 1.2 5 1

Gather (cost=14,764.56..14,764.97 rows=4 width=8) (actual time=43.270..58.012 rows=5 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
14. 10.388 18.119 ↑ 1.0 1 5

Partial Aggregate (cost=13,764.56..13,764.57 rows=1 width=8) (actual time=18.119..18.119 rows=1 loops=5)

15. 7.731 7.731 ↑ 1.2 33,472 5

Parallel Index Only Scan using points_default_filter_idx on points points_1 (cost=0.42..13,660.46 rows=41,639 width=0) (actual time=0.032..7.731 rows=33,472 loops=5)

  • Index Cond: ((is_all_timeslots_latest = true) AND (is_duplicate = false) AND (is_preview = false))
  • Filter: ((is_all_timeslots_latest IS TRUE) AND (is_preview IS FALSE) AND (is_duplicate IS FALSE))
  • Heap Fetches: 0
16.          

SubPlan (for Subquery Scan)

17. 23.457 1,395.531 ↑ 1.0 1 3

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

18. 176.592 1,372.074 ↓ 5.8 46,453 3

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

19. 44.205 229.674 ↓ 5.0 80,484 3

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

20. 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
21. 158.748 185.451 ↑ 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=11.447..61.817 rows=80,484 loops=3)

  • Recheck Cond: (road_damage_type_id = rd.road_damage_type_id)
  • Heap Blocks: exact=36960
22. 26.703 26.703 ↑ 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=8.901..8.901 rows=160,549 loops=3)

  • Index Cond: (road_damage_type_id = rd.road_damage_type_id)
23. 241.452 965.808 ↑ 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.004..0.004 rows=1 loops=241,452)

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

SubPlan (for Index Scan)

25. 0.000 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)

26. 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
27. 23.271 1,290.105 ↑ 1.0 1 3

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

28. 107.832 1,266.834 ↓ 5.8 46,453 3

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

29. 45.309 193.194 ↓ 5.0 80,484 3

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

30. 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
31. 122.172 147.867 ↑ 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=11.220..49.289 rows=80,484 loops=3)

  • Recheck Cond: (road_damage_type_id = rd_1.road_damage_type_id)
  • Heap Blocks: exact=36960
32. 25.695 25.695 ↑ 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=8.565..8.565 rows=160,549 loops=3)

  • Index Cond: (road_damage_type_id = rd_1.road_damage_type_id)
33. 241.452 965.808 ↑ 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.004..0.004 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
34.          

SubPlan (for Index Scan)

35. 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)

36. 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.145 ms
Execution time : 2,930.499 ms