explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KVCx

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 868.530 ↑ 25.0 4 1

Limit (cost=112,237,353.52..112,237,353.77 rows=100 width=743) (actual time=868.529..868.530 rows=4 loops=1)

2.          

CTE get_shift_occurrences

3. 0.089 868.214 ↑ 48,988.2 17 1

Unique (cost=29,861,541.61..30,263,008.90 rows=832,800 width=678) (actual time=868.108..868.214 rows=17 loops=1)

4. 1.375 868.125 ↑ 117,388.1 342 1

Sort (cost=29,861,541.61..29,961,908.43 rows=40,146,729 width=678) (actual time=868.107..868.125 rows=342 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 318kB
5. 1.264 866.750 ↑ 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.764..866.750 rows=342 loops=1)

6. 2.322 861.382 ↑ 117.2 342 1

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

7. 0.295 31.078 ↑ 33.5 342 1

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

8. 0.011 14.056 ↑ 55.0 1 1

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

9. 0.021 14.045 ↑ 55.0 1 1

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

  • Sort Key: g.revision_uuid
  • Sort Method: quicksort Memory: 25kB
10. 10.881 14.024 ↑ 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=8.086..14.024 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: 6791
  • Heap Blocks: exact=713
11. 0.005 3.143 ↓ 0.0 0 1

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

12. 1.736 1.736 ↑ 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.736..1.736 rows=6,041 loops=1)

  • Index Cond: (valid_range @> '2020-03-26 16:30:00+00'::timestamp with time zone)
13. 0.677 0.677 ↑ 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.677..0.677 rows=6,149 loops=1)

  • Index Cond: (valid_range @> '2020-05-22 06:28:00+00'::timestamp with time zone)
14. 0.725 0.725 ↓ 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.725..0.725 rows=425 loops=1)

  • Index Cond: (valid_range <@ '[""2020-03-26 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)
15. 16.174 16.727 ↓ 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.704..16.727 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: 3064
  • Heap Blocks: exact=1148
16. 0.553 0.553 ↓ 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.552..0.553 rows=3,406 loops=1)

  • Index Cond: (group_uuid = g.group_uuid)
17. 4.788 827.982 ↑ 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.421..2.421 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
18. 14.706 808.488 ↓ 0.0 0 342

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

19. 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)
20. 45.144 789.678 ↓ 0.0 0 342

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

21. 742.482 742.482 ↓ 11.4 11,447 342

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=2.171..2.171 rows=11,447 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))
22. 0.000 2.052 ↑ 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.006..0.006 rows=1 loops=342)

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

SubPlan (for Bitmap Index Scan)

24. 0.684 43.092 ↑ 1.0 1 342

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

25. 10.602 42.408 ↑ 21.0 1 342

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

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
26. 31.806 31.806 ↓ 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.011..0.093 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)
27.          

SubPlan (for Bitmap Heap Scan)

28. 14.706 14.706 ↑ 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.043..0.043 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
29. 4.104 4.104 ↓ 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.012..0.012 rows=0 loops=342)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
30. 0.044 868.528 ↑ 104,100.0 4 1

Sort (cost=81,974,344.62..81,975,385.62 rows=416,400 width=743) (actual time=868.528..868.528 rows=4 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: 29kB
31. 0.013 868.484 ↑ 104,100.0 4 1

Merge Semi Join (cost=81,329,373.55..81,958,430.12 rows=416,400 width=743) (actual time=868.463..868.484 rows=4 loops=1)

  • Merge Cond: (get_shift_occurrences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
32. 0.031 868.258 ↑ 52,050.0 16 1

Sort (cost=449,889.43..451,971.43 rows=832,800 width=743) (actual time=868.257..868.258 rows=16 loops=1)

  • Sort Key: get_shift_occurrences.shift_occurrence_uuid
  • Sort Method: quicksort Memory: 40kB
33. 868.227 868.227 ↑ 48,988.2 17 1

CTE Scan on get_shift_occurrences (cost=0.00..16,656.00 rows=832,800 width=743) (actual time=868.112..868.227 rows=17 loops=1)

34. 0.006 0.213 ↑ 108.2 4 1

Subquery Scan on dups (cost=80,879,484.12..81,486,345.48 rows=433 width=16) (actual time=0.197..0.213 rows=4 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 15
35. 0.032 0.207 ↑ 912,573.5 19 1

WindowAgg (cost=80,879,484.12..81,226,262.04 rows=17,338,896 width=56) (actual time=0.193..0.207 rows=19 loops=1)

36. 0.015 0.175 ↑ 912,573.5 19 1

Sort (cost=80,879,484.12..80,922,831.36 rows=17,338,896 width=32) (actual time=0.174..0.175 rows=19 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 26kB
37. 0.104 0.160 ↑ 912,573.5 19 1

Merge Left Join (cost=230,043.86..78,261,321.86 rows=17,338,896 width=32) (actual time=0.115..0.160 rows=19 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: 60
38. 0.011 0.021 ↑ 48,988.2 17 1

Sort (cost=116,851.18..118,933.18 rows=832,800 width=56) (actual time=0.020..0.021 rows=17 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 26kB
39. 0.010 0.010 ↑ 48,988.2 17 1

CTE Scan on get_shift_occurrences f1 (cost=0.00..16,656.00 rows=832,800 width=56) (actual time=0.003..0.010 rows=17 loops=1)

40. 0.009 0.035 ↑ 11,104.0 75 1

Materialize (cost=113,192.68..117,356.68 rows=832,800 width=40) (actual time=0.028..0.035 rows=75 loops=1)

41. 0.018 0.026 ↑ 48,988.2 17 1

Sort (cost=113,192.68..115,274.68 rows=832,800 width=40) (actual time=0.025..0.026 rows=17 loops=1)

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 26kB
42. 0.008 0.008 ↑ 48,988.2 17 1

CTE Scan on get_shift_occurrences f2 (cost=0.00..16,656.00 rows=832,800 width=40) (actual time=0.002..0.008 rows=17 loops=1)

Planning time : 7.889 ms
Execution time : 869.703 ms