explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F4Rc

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 4,385.479 ↑ 1.0 4 1

Sort (cost=41,480.27..41,480.28 rows=4 width=48) (actual time=4,385.478..4,385.479 rows=4 loops=1)

  • Sort Key: (CASE WHEN (p.timeslot = 0) THEN 1000000 ELSE p.timeslot END) DESC
  • Sort Method: quicksort Memory: 25kB
2.          

Initplan (for Sort)

3. 0.002 10.727 ↑ 1.0 1 1

Aggregate (cost=259.84..259.85 rows=1 width=32) (actual time=10.727..10.727 rows=1 loops=1)

4. 0.002 10.725 ↓ 0.0 0 1

Nested Loop (cost=0.71..259.83 rows=1 width=3) (actual time=10.725..10.725 rows=0 loops=1)

5. 9.872 9.872 ↑ 1.0 1 1

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

  • Filter: ((road_damage_type_name)::text = 'emi_gebr'::text)
  • Rows Removed by Filter: 13
6. 0.001 0.851 ↓ 0.0 0 1

Nested Loop (cost=0.71..248.06 rows=2 width=7) (actual time=0.851..0.851 rows=0 loops=1)

7. 0.850 0.850 ↓ 0.0 0 1

Index Scan using street_segment_data_segment_id_timeslot_key on street_segment_data ssd (cost=0.29..244.91 rows=1 width=4) (actual time=0.850..0.850 rows=0 loops=1)

  • Index Cond: ((timeslot >= 201801) AND (timeslot <= 201826))
  • Filter: (SubPlan 1)
8.          

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..2.50 rows=1 width=8) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Index Scan Backward using street_segment_data_segment_id_timeslot_key on street_segment_data j (cost=0.29..2.50 rows=1 width=8) (never executed)

  • Index Cond: (segment_id = ssd.segment_id)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using street_segment_data_to_road_damage_pkey on street_segment_data_to_road_damage ssdrd (cost=0.42..3.13 rows=2 width=11) (never executed)

  • Index Cond: ((street_segment_data_id = ssd.street_segment_data_id) AND (road_damage_type_id = rd.road_damage_type_id))
12. 10.744 4,385.467 ↑ 1.0 4 1

Subquery Scan on p (cost=18,220.90..41,220.37 rows=4 width=48) (actual time=4,278.899..4,385.467 rows=4 loops=1)

13. 0.012 4,205.831 ↑ 1.0 4 1

HashAggregate (cost=18,220.90..18,220.94 rows=4 width=12) (actual time=4,205.826..4,205.831 rows=4 loops=1)

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

Append (cost=8,621.72..18,220.88 rows=4 width=12) (actual time=193.174..4,205.819 rows=4 loops=1)

15. 0.002 193.175 ↑ 1.0 3 1

Unique (cost=8,621.72..8,621.74 rows=3 width=12) (actual time=193.173..193.175 rows=3 loops=1)

16. 0.006 193.173 ↑ 1.0 3 1

Sort (cost=8,621.72..8,621.73 rows=3 width=12) (actual time=193.172..193.173 rows=3 loops=1)

  • Sort Key: points.timeslot
  • Sort Method: quicksort Memory: 25kB
17. 2.826 193.167 ↑ 1.0 3 1

HashAggregate (cost=8,621.67..8,621.70 rows=3 width=12) (actual time=193.167..193.167 rows=3 loops=1)

  • Group Key: points.timeslot
18. 186.700 190.341 ↑ 1.0 13,751 1

Bitmap Heap Scan on points (cost=211.89..8,552.91 rows=13,751 width=41) (actual time=16.709..190.341 rows=13,751 loops=1)

  • Recheck Cond: (timeslot IS NOT NULL)
  • Filter: is_timeslot_latest
  • Heap Blocks: exact=3074
19. 3.641 3.641 ↑ 1.0 13,751 1

Bitmap Index Scan on points_default_timeslot_filter_idx (cost=0.00..208.45 rows=13,751 width=0) (actual time=3.641..3.641 rows=13,751 loops=1)

  • Index Cond: ((timeslot IS NOT NULL) AND (is_timeslot_latest = true))
20. 1.241 4,012.642 ↑ 1.0 1 1

Aggregate (cost=9,599.10..9,599.11 rows=1 width=12) (actual time=4,012.642..4,012.642 rows=1 loops=1)

21. 8.838 4,011.401 ↑ 1.0 12,464 1

Bitmap Heap Scan on points points_1 (cost=1,692.01..9,567.94 rows=12,464 width=37) (actual time=4,002.893..4,011.401 rows=12,464 loops=1)

  • Filter: is_all_timeslots_latest
  • Heap Blocks: exact=2895
22. 4,002.563 4,002.563 ↑ 1.0 12,464 1

Bitmap Index Scan on idx_points_is_all_timeslots_latest (cost=0.00..1,688.89 rows=12,464 width=0) (actual time=4,002.563..4,002.563 rows=12,464 loops=1)

  • Index Cond: (is_all_timeslots_latest = true)
23.          

SubPlan (for Subquery Scan)

24. 2.140 168.892 ↑ 1.0 1 4

Aggregate (cost=5,749.83..5,749.84 rows=1 width=32) (actual time=42.223..42.223 rows=1 loops=4)

25. 19.364 166.752 ↓ 408.6 2,860 4

Nested Loop (cost=0.71..5,749.81 rows=7 width=3) (actual time=6.366..41.688 rows=2,860 loops=4)

26. 5.524 59.536 ↓ 5.0 7,321 4

Nested Loop (cost=0.42..3,152.68 rows=1,464 width=7) (actual time=0.014..14.884 rows=7,321 loops=4)

27. 0.016 0.016 ↑ 1.0 1 4

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

  • Filter: ((road_damage_type_name)::text = 'emi_subs'::text)
  • Rows Removed by Filter: 13
28. 53.996 53.996 ↑ 2.0 7,321 4

Index Scan using street_segment_data_to_road_damage_pkey on street_segment_data_to_road_damage ssdrd_1 (cost=0.42..2,994.51 rows=14,642 width=11) (actual time=0.010..13.499 rows=7,321 loops=4)

  • Index Cond: (road_damage_type_id = rd_1.road_damage_type_id)
29. 73.210 87.852 ↓ 0.0 0 29,284

Index Scan using street_segment_data_pkey on street_segment_data ssd_1 (cost=0.29..1.77 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=29,284)

  • Index Cond: (street_segment_data_id = ssdrd_1.street_segment_data_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))))) AND (SubPlan 3))
  • Rows Removed by Filter: 1
30.          

SubPlan (for Index Scan)

31. 0.000 14.642 ↑ 1.0 1 14,642

Limit (cost=0.29..2.50 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=14,642)

32. 14.642 14.642 ↑ 1.0 1 14,642

Index Scan Backward using street_segment_data_segment_id_timeslot_key on street_segment_data j_1 (cost=0.29..2.50 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=14,642)

  • Index Cond: (segment_id = ssd_1.segment_id)
Planning time : 248.763 ms
Execution time : 4,385.627 ms