explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YfdW

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 820.906 ↑ 5.9 17 1

Limit (cost=192,328,682,040.92..192,328,682,041.17 rows=100 width=743) (actual time=820.904..820.906 rows=17 loops=1)

2.          

CTE get_shift_occurrences

3. 1.131 786.776 ↑ 117,388.1 342 1

Nested Loop Left Join (cost=1,357.47..1,503,012.31 rows=40,146,729 width=678) (actual time=29.678..786.776 rows=342 loops=1)

4. 2.170 781.883 ↑ 117.2 342 1

Nested Loop (cost=1,357.05..766,735.17 rows=40,088 width=591) (actual time=29.636..781.883 rows=342 loops=1)

5. 0.369 30.733 ↑ 33.5 342 1

Nested Loop (cost=1,330.19..37,070.84 rows=11,452 width=285) (actual time=23.881..30.733 rows=342 loops=1)

6. 0.005 14.188 ↑ 55.0 1 1

Unique (cost=1,321.53..1,321.81 rows=55 width=221) (actual time=14.185..14.188 rows=1 loops=1)

7. 0.068 14.183 ↑ 55.0 1 1

Sort (cost=1,321.53..1,321.67 rows=55 width=221) (actual time=14.182..14.183 rows=1 loops=1)

  • Sort Key: g.revision_uuid
  • Sort Method: quicksort Memory: 25kB
8. 11.257 14.115 ↑ 55.0 1 1

Bitmap Heap Scan on groups_org_part_33 g (cost=290.97..1,319.94 rows=55 width=221) (actual time=7.673..14.115 rows=1 loops=1)

  • Recheck Cond: ((valid_range @> '2020-03-26 16:30:00+00'::timestamp with time zone) OR (valid_range @> '2020-05-22 06:28:00+00'::timestamp with time zone) OR (valid_range <@ '[""2020-03-26 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange))
  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (lower((target_name)::text) = 'cpcsrt'::text))
  • Rows Removed by Filter: 6,791
  • Heap Blocks: exact=713
9. 0.004 2.858 ↓ 0.0 0 1

BitmapOr (cost=290.97..290.97 rows=12,479 width=0) (actual time=2.858..2.858 rows=0 loops=1)

10. 1.542 1.542 ↑ 1.0 6,041 1

Bitmap Index Scan on groups_org_part_33_valid_range_idx (cost=0.00..140.09 rows=6,042 width=0) (actual time=1.542..1.542 rows=6,041 loops=1)

  • Index Cond: (valid_range @> '2020-03-26 16:30:00+00'::timestamp with time zone)
11. 0.632 0.632 ↑ 1.0 6,149 1

Bitmap Index Scan on groups_org_part_33_valid_range_idx (cost=0.00..144.38 rows=6,213 width=0) (actual time=0.632..0.632 rows=6,149 loops=1)

  • Index Cond: (valid_range @> '2020-05-22 06:28:00+00'::timestamp with time zone)
12. 0.680 0.680 ↓ 1.9 425 1

Bitmap Index Scan on groups_org_part_33_valid_range_idx (cost=0.00..6.46 rows=224 width=0) (actual time=0.680..0.680 rows=425 loops=1)

  • Index Cond: (valid_range <@ '[""2020-03-26 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)
13. 15.451 16.176 ↓ 48.9 342 1

Bitmap Heap Scan on shift_occurrence_org_part_7 shiftoccurrence (cost=8.66..649.90 rows=7 width=80) (actual time=9.679..16.176 rows=342 loops=1)

  • Recheck Cond: (group_uuid = g.group_uuid)
  • Filter: ((organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND ((tstzrange((start_date)::timestamp with time zone, (end_date)::timestamp with time zone, '[)'::text) @> '2020-05-15 16:30:00+00'::timestamp with time zone) OR (tstzrange((start_date)::timestamp with time zone, (end_date)::timestamp with time zone, '[)'::text) @> '2020-05-22 06:28:00+00'::timestamp with time zone) OR (tstzrange((start_date)::timestamp with time zone, (end_date)::timestamp with time zone, '[)'::text) <@ '[""2020-05-15 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 3,064
  • Heap Blocks: exact=1,148
14. 0.725 0.725 ↓ 6.9 3,406 1

Bitmap Index Scan on shift_occurrence_org_part_7_group_uuid_idx (cost=0.00..8.66 rows=497 width=0) (actual time=0.725..0.725 rows=3,406 loops=1)

  • Index Cond: (group_uuid = g.group_uuid)
15. 4.446 748.980 ↑ 1.0 1 342

Bitmap Heap Scan on shifts_org_part_6 s (cost=26.86..63.71 rows=1 width=322) (actual time=2.190..2.190 rows=1 loops=342)

  • Recheck Cond: ((shift_uuid = shiftoccurrence.shift_uuid) AND ((valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone)) OR (revision_uuid = (SubPlan 2))))
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND ((valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone)) OR ((NOT (SubPlan 1)) AND (revision_uuid = (SubPlan 2)))))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=684
16. 13.338 731.538 ↓ 0.0 0 342

BitmapAnd (cost=26.86..26.86 rows=1 width=0) (actual time=2.139..2.139 rows=0 loops=342)

17. 4.104 4.104 ↓ 4.5 94 342

Bitmap Index Scan on shifts_org_part_6_shift_uuid_idx (cost=0.00..0.62 rows=21 width=0) (actual time=0.012..0.012 rows=94 loops=342)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
18. 42.066 714.096 ↓ 0.0 0 342

BitmapOr (cost=25.99..25.99 rows=1,002 width=0) (actual time=2.088..2.088 rows=0 loops=342)

19. 670.320 670.320 ↓ 11.4 11,448 342

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=1.960..1.960 rows=11,448 loops=342)

  • Index Cond: (valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone))
20. 0.000 1.710 ↑ 1.0 1 342

Bitmap Index Scan on shifts_org_part_6_revision_uuid_uidx (cost=0.00..18.10 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=342)

  • Index Cond: (revision_uuid = (SubPlan 2))
21.          

SubPlan (for Bitmap Index Scan)

22. 0.684 40.014 ↑ 1.0 1 342

Limit (cost=17.63..17.64 rows=1 width=37) (actual time=0.116..0.117 rows=1 loops=342)

23. 9.234 39.330 ↑ 21.0 1 342

Sort (cost=17.63..17.69 rows=21 width=37) (actual time=0.115..0.115 rows=1 loops=342)

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
24. 30.096 30.096 ↓ 4.5 94 342

Index Scan using shifts_org_part_6_organization_uuid_shift_uuid_sequence_low_idx on shifts_org_part_6 shifts_org_part_6_1 (cost=0.42..17.53 rows=21 width=37) (actual time=0.010..0.088 rows=94 loops=342)

  • Index Cond: ((organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (shift_uuid = shiftoccurrence.shift_uuid))
  • Filter: (is_visible IS TRUE)
25.          

SubPlan (for Bitmap Heap Scan)

26. 12.996 12.996 ↑ 1.0 1 342

Index Scan using shifts_org_part_6_organization_uuid_shift_uuid_sequence_low_idx on shifts_org_part_6 (cost=0.42..17.69 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=342)

  • Index Cond: ((organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (shift_uuid = shiftoccurrence.shift_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone)))
  • Rows Removed by Filter: 41
27. 3.762 3.762 ↓ 0.0 0 342

Index Scan using shifts_org_part_6_revision_uuid_uidx on shifts_org_part_6 revision_shift (cost=0.42..0.83 rows=1 width=37) (actual time=0.011..0.011 rows=0 loops=342)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
28. 0.142 820.903 ↑ 1,180,786.1 17 1

Sort (cost=192,327,179,028.61..192,327,229,212.02 rows=20,073,364 width=743) (actual time=820.902..820.903 rows=17 loops=1)

  • Sort Key: get_shift_occurrences.start_date, get_shift_occurrences.end_date, get_shift_occurrences.shift_uuid, get_shift_occurrences.shift_valid_from DESC
  • Sort Method: quicksort Memory: 42kB
29. 0.073 820.761 ↑ 1,180,786.1 17 1

Hash Join (cost=192,325,280,203.15..192,326,411,839.07 rows=20,073,364 width=743) (actual time=820.645..820.761 rows=17 loops=1)

  • Hash Cond: (get_shift_occurrences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
30. 29.767 29.767 ↑ 117,388.1 342 1

CTE Scan on get_shift_occurrences (cost=0.00..802,934.58 rows=40,146,729 width=743) (actual time=29.686..29.767 rows=342 loops=1)

31. 0.010 790.921 ↑ 11.8 17 1

Hash (cost=192,325,280,200.65..192,325,280,200.65 rows=200 width=16) (actual time=790.920..790.921 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.026 790.911 ↑ 11.8 17 1

HashAggregate (cost=192,325,280,198.65..192,325,280,200.65 rows=200 width=16) (actual time=790.908..790.911 rows=17 loops=1)

  • Group Key: dups.shift_occurrence_uuid
33. 0.861 790.885 ↑ 59,255.9 17 1

Subquery Scan on dups (cost=190,914,987,812.05..192,325,277,680.28 rows=1,007,350 width=16) (actual time=783.777..790.885 rows=17 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 15,707
34. 6.024 790.024 ↑ 2,562,579.3 15,724 1

WindowAgg (cost=190,914,987,812.05..191,720,867,736.75 rows=40,293,996,235 width=56) (actual time=782.994..790.024 rows=15,724 loops=1)

35. 7.065 784.000 ↑ 2,562,579.3 15,724 1

Sort (cost=190,914,987,812.05..191,015,722,802.64 rows=40,293,996,235 width=32) (actual time=782.966..784.000 rows=15,724 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 1,198kB
36. 15.332 776.935 ↑ 2,562,579.3 15,724 1

Merge Left Join (cost=14,922,115.26..181,338,206,273.16 rows=40,293,996,235 width=32) (actual time=759.755..776.935 rows=15,724 loops=1)

  • Merge Cond: (f1.shift_uuid = f2.shift_uuid)
  • Join Filter: (tsrange(f2.start_date, f2.end_date, '[]'::text) && tsrange(f1.start_date, f1.end_date, '[]'::text))
  • Rows Removed by Join Filter: 21,280
37. 0.521 759.451 ↑ 117,388.1 342 1

Sort (cost=7,637,482.38..7,737,849.20 rows=40,146,729 width=56) (actual time=759.405..759.451 rows=342 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 54kB
38. 758.930 758.930 ↑ 117,388.1 342 1

CTE Scan on get_shift_occurrences f1 (cost=0.00..802,934.58 rows=40,146,729 width=56) (actual time=0.004..758.930 rows=342 loops=1)

39. 1.855 2.152 ↑ 1,086.4 36,955 1

Materialize (cost=7,284,632.88..7,485,366.52 rows=40,146,729 width=40) (actual time=0.276..2.152 rows=36,955 loops=1)

40. 0.110 0.297 ↑ 117,388.1 342 1

Sort (cost=7,284,632.88..7,384,999.70 rows=40,146,729 width=40) (actual time=0.271..0.297 rows=342 loops=1)

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 51kB
41. 0.187 0.187 ↑ 117,388.1 342 1

CTE Scan on get_shift_occurrences f2 (cost=0.00..802,934.58 rows=40,146,729 width=40) (actual time=0.005..0.187 rows=342 loops=1)

Planning time : 7.437 ms
Execution time : 822.626 ms