explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GG2w

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 160.902 ↑ 1.9 35 1

Limit (cost=5,078,383.63..5,078,383.79 rows=65 width=743) (actual time=160.897..160.902 rows=35 loops=1)

2.          

CTE get_groups

3. 0.003 22.658 ↑ 98.0 1 1

Unique (cost=2,559.51..2,560.00 rows=98 width=221) (actual time=22.656..22.658 rows=1 loops=1)

4. 0.034 22.655 ↑ 49.0 2 1

Sort (cost=2,559.51..2,559.75 rows=98 width=221) (actual time=22.654..22.655 rows=2 loops=1)

  • Sort Key: g.revision_uuid
  • Sort Method: quicksort Memory: 25kB
5. 0.003 22.621 ↑ 49.0 2 1

Append (cost=290.99..2,556.27 rows=98 width=221) (actual time=7.069..22.621 rows=2 loops=1)

6. 9.926 12.590 ↑ 55.0 1 1

Bitmap Heap Scan on groups_org_part_33 g (cost=290.99..1,320.04 rows=55 width=221) (actual time=7.068..12.590 rows=1 loops=1)

  • Recheck Cond: ((valid_range @> '2020-03-25 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-25 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: 6802
  • Heap Blocks: exact=713
7. 0.003 2.664 ↓ 0.0 0 1

BitmapOr (cost=290.99..290.99 rows=12,482 width=0) (actual time=2.664..2.664 rows=0 loops=1)

8. 1.348 1.348 ↓ 1.0 6,046 1

Bitmap Index Scan on groups_org_part_33_valid_range_idx (cost=0.00..140.07 rows=6,039 width=0) (actual time=1.348..1.348 rows=6,046 loops=1)

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

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

Bitmap Index Scan on groups_org_part_33_valid_range_idx (cost=0.00..6.50 rows=230 width=0) (actual time=0.684..0.684 rows=428 loops=1)

  • Index Cond: (valid_range <@ '[""2020-03-25 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)
11. 10.028 10.028 ↑ 43.0 1 1

Seq Scan on groups_org_part_33 g_1 (cost=0.00..1,234.76 rows=43 width=221) (actual time=4.354..10.028 rows=1 loops=1)

  • Filter: (is_visible AND upper_inf(valid_range) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (lower((target_name)::text) = 'cpcsrt'::text))
  • Rows Removed by Filter: 25907
12.          

CTE get_shifts

13. 0.026 1.758 ↑ 547.0 39 1

Nested Loop (cost=4.37..5,214.49 rows=21,334 width=391) (actual time=0.302..1.758 rows=39 loops=1)

14. 0.004 0.005 ↑ 98.0 1 1

HashAggregate (cost=2.21..3.19 rows=98 width=16) (actual time=0.005..0.005 rows=1 loops=1)

  • Group Key: get_groups.group_uuid
15. 0.001 0.001 ↑ 98.0 1 1

CTE Scan on get_groups (cost=0.00..1.96 rows=98 width=16) (actual time=0.001..0.001 rows=1 loops=1)

16. 1.665 1.727 ↓ 4.3 39 1

Bitmap Heap Scan on shifts_org_part_6 s (cost=2.17..53.09 rows=9 width=391) (actual time=0.291..1.727 rows=39 loops=1)

  • Recheck Cond: (group_uuid = get_groups.group_uuid)
  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND ((valid_range @> '2020-03-25 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-25 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 295
  • Heap Blocks: exact=213
17. 0.062 0.062 ↓ 8.1 334 1

Bitmap Index Scan on shifts_org_part_6_group_uuid_idx (cost=0.00..2.17 rows=41 width=0) (actual time=0.062..0.062 rows=334 loops=1)

  • Index Cond: (group_uuid = get_groups.group_uuid)
18.          

CTE get_shift_occurrences

19. 0.605 157.125 ↑ 92.0 142 1

Unique (cost=5,047,114.33..5,047,244.91 rows=13,058 width=743) (actual time=156.337..157.125 rows=142 loops=1)

20. 6.796 156.520 ↑ 4.7 2,765 1

Sort (cost=5,047,114.33..5,047,146.98 rows=13,058 width=743) (actual time=156.335..156.520 rows=2,765 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s_1.shift_uuid
  • Sort Method: quicksort Memory: 2851kB
21. 3.522 149.724 ↑ 4.7 2,765 1

Nested Loop Left Join (cost=706.81..5,043,465.40 rows=13,058 width=743) (actual time=25.506..149.724 rows=2,765 loops=1)

22. 24.748 137.907 ↓ 102.4 2,765 1

Hash Join (cost=706.39..5,043,128.18 rows=27 width=631) (actual time=25.465..137.907 rows=2,765 loops=1)

  • Hash Cond: (shiftoccurrence.shift_uuid = s_1.shift_uuid)
  • Join Filter: ((s_1.valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone)) OR ((NOT (SubPlan 3)) AND (s_1.revision_uuid = (SubPlan 4))))
  • Rows Removed by Join Filter: 24142
23. 1.300 38.898 ↓ 38.9 2,765 1

Nested Loop (cost=13.04..61,014.90 rows=71 width=349) (actual time=23.375..38.898 rows=2,765 loops=1)

  • Join Filter: ((g_2.group_uuid = shiftoccurrence.group_uuid) AND ((g_2.valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone)) OR ((NOT (SubPlan 5)) AND (g_2.revision_uuid = (SubPlan 6)))))
24. 0.021 22.693 ↑ 98.0 1 1

Hash Join (cost=4.41..7.72 rows=98 width=301) (actual time=22.690..22.693 rows=1 loops=1)

  • Hash Cond: (g_2.group_uuid = get_groups_4.group_uuid)
25. 22.658 22.658 ↑ 98.0 1 1

CTE Scan on get_groups g_2 (cost=0.00..1.96 rows=98 width=285) (actual time=22.657..22.658 rows=1 loops=1)

26. 0.005 0.014 ↑ 98.0 1 1

Hash (cost=3.19..3.19 rows=98 width=16) (actual time=0.013..0.014 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.005 0.009 ↑ 98.0 1 1

HashAggregate (cost=2.21..3.19 rows=98 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  • Group Key: get_groups_4.group_uuid
28. 0.004 0.004 ↑ 98.0 1 1

CTE Scan on get_groups get_groups_4 (cost=0.00..1.96 rows=98 width=16) (actual time=0.002..0.004 rows=1 loops=1)

29. 14.368 14.905 ↓ 395.0 2,765 1

Bitmap Heap Scan on shift_occurrence_org_part_7 shiftoccurrence (cost=8.63..586.24 rows=7 width=80) (actual time=0.674..14.905 rows=2,765 loops=1)

  • Recheck Cond: (group_uuid = get_groups_4.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-03-25 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-03-25 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 641
  • Heap Blocks: exact=1148
30. 0.537 0.537 ↓ 6.9 3,406 1

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

  • Index Cond: (group_uuid = get_groups_4.group_uuid)
31.          

SubPlan (for Nested Loop)

32. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_groups get_groups_1 (cost=0.00..2.94 rows=1 width=0) (never executed)

  • Filter: ((shiftoccurrence.group_uuid = group_uuid) AND (valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-22 06:28:00+00'::timestamp with time zone)))
33. 0.000 0.000 ↓ 0.0 0

Limit (cost=2.21..2.22 rows=1 width=48) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Sort (cost=2.21..2.22 rows=1 width=48) (never executed)

  • Sort Key: get_groups_2.valid_range
35. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_groups get_groups_2 (cost=0.00..2.21 rows=1 width=48) (never executed)

  • Filter: (shiftoccurrence.group_uuid = group_uuid)
36. 0.020 1.835 ↑ 547.0 39 1

Hash (cost=426.68..426.68 rows=21,334 width=298) (actual time=1.835..1.835 rows=39 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 269kB
37. 1.815 1.815 ↑ 547.0 39 1

CTE Scan on get_shifts s_1 (cost=0.00..426.68 rows=21,334 width=298) (actual time=0.306..1.815 rows=39 loops=1)

38.          

SubPlan (for Hash Join)

39. 72.426 72.426 ↑ 1.0 1 24,142

CTE Scan on get_shifts (cost=0.00..640.02 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=24,142)

  • Filter: ((shiftoccurrence.shift_uuid = shift_uuid) 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: 21
40. 0.000 0.000 ↓ 0.0 0

Limit (cost=17.63..17.64 rows=1 width=37) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Sort (cost=17.63..17.69 rows=21 width=37) (never executed)

  • Sort Key: shifts_org_part_6.valid_range
42. 0.000 0.000 ↓ 0.0 0

Index Scan using shifts_org_part_6_organization_uuid_shift_uuid_sequence_low_idx on shifts_org_part_6 (cost=0.42..17.53 rows=21 width=37) (never executed)

  • Index Cond: ((organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (shift_uuid = shiftoccurrence.shift_uuid))
  • Filter: (is_visible IS TRUE)
43. 0.000 8.295 ↓ 0.0 0 2,765

Nested Loop Semi Join (cost=0.42..4.02 rows=1 width=37) (actual time=0.003..0.003 rows=0 loops=2,765)

  • Join Filter: (revision_shift.group_uuid = get_groups_3.group_uuid)
44. 8.295 8.295 ↓ 0.0 0 2,765

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=53) (actual time=0.003..0.003 rows=0 loops=2,765)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
45. 0.000 0.000 ↑ 98.0 1 384

CTE Scan on get_groups get_groups_3 (cost=0.00..1.96 rows=98 width=16) (actual time=0.000..0.000 rows=1 loops=384)

46. 0.086 160.898 ↑ 1.9 35 1

Sort (cost=23,364.22..23,364.39 rows=65 width=743) (actual time=160.896..160.898 rows=35 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: 60kB
47. 0.332 160.812 ↑ 1.9 35 1

Nested Loop Semi Join (cost=22,448.20..23,362.27 rows=65 width=743) (actual time=160.202..160.812 rows=35 loops=1)

  • Join Filter: (get_shift_occurrences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
  • Rows Removed by Join Filter: 4340
48. 156.362 156.362 ↑ 92.0 142 1

CTE Scan on get_shift_occurrences (cost=0.00..261.16 rows=13,058 width=743) (actual time=156.343..156.362 rows=142 loops=1)

49. 0.210 4.118 ↓ 31.0 31 142

Materialize (cost=22,448.20..22,905.24 rows=1 width=16) (actual time=0.027..0.029 rows=31 loops=142)

50. 0.014 3.908 ↓ 35.0 35 1

Subquery Scan on dups (cost=22,448.20..22,905.23 rows=1 width=16) (actual time=3.803..3.908 rows=35 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 123
51. 0.104 3.894 ↑ 82.6 158 1

WindowAgg (cost=22,448.20..22,709.36 rows=13,058 width=56) (actual time=3.799..3.894 rows=158 loops=1)

52. 0.074 3.790 ↑ 82.6 158 1

Sort (cost=22,448.20..22,480.85 rows=13,058 width=32) (actual time=3.781..3.790 rows=158 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
53. 2.209 3.716 ↑ 82.6 158 1

Merge Left Join (cost=2,307.69..21,555.52 rows=13,058 width=32) (actual time=1.287..3.716 rows=158 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: 5390
54. 0.076 1.132 ↑ 92.0 142 1

Sort (cost=1,153.85..1,186.49 rows=13,058 width=56) (actual time=1.123..1.132 rows=142 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 38kB
55. 1.056 1.056 ↑ 92.0 142 1

CTE Scan on get_shift_occurrences f1 (cost=0.00..261.16 rows=13,058 width=56) (actual time=0.004..1.056 rows=142 loops=1)

56. 0.317 0.375 ↑ 2.4 5,507 1

Sort (cost=1,153.85..1,186.49 rows=13,058 width=40) (actual time=0.106..0.375 rows=5,507 loops=1)

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 36kB
57. 0.058 0.058 ↑ 92.0 142 1

CTE Scan on get_shift_occurrences f2 (cost=0.00..261.16 rows=13,058 width=40) (actual time=0.002..0.058 rows=142 loops=1)

Planning time : 7.077 ms
Execution time : 162.338 ms