explain.depesz.com

PostgreSQL's explain analyze made readable

Result: B0Ix

Settings
# exclusive inclusive rows x rows loops node
1. 0.628 14,831.412 ↑ 1.0 1 1

Aggregate (cost=108,890,023.31..108,890,023.32 rows=1 width=8) (actual time=14,831.412..14,831.412 rows=1 loops=1)

2. 5.894 14,830.784 ↑ 26.0 6,910 1

Sort (cost=108,887,326.03..108,887,775.57 rows=179,819 width=711) (actual time=14,830.223..14,830.784 rows=6,910 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: 732kB
3.          

CTE get_groups

4. 14.206 40.940 ↑ 1.8 6,116 1

HashAggregate (cost=3,021.41..3,128.68 rows=10,727 width=285) (actual time=36.602..40.940 rows=6,116 loops=1)

  • Group Key: g.revision_uuid, g.organization_uuid, g.group_uuid, g.valid_range, g.is_visible, g.by_uuid, g.target_name, g.status, g.external_key, g.externally_owned, g.timezone, g.site_uuid, g.allow_duplicates, g.observed_by_all, g.use_default_devices, g.description, g.sequence
5. 0.460 26.734 ↑ 1.8 6,116 1

Append (cost=0.00..2,565.51 rows=10,727 width=285) (actual time=0.046..26.734 rows=6,116 loops=1)

6. 14.238 14.238 ↑ 1.8 6,115 1

Seq Scan on groups_org_part_33 g (cost=0.00..1,234.76 rows=10,726 width=221) (actual time=0.045..14.238 rows=6,115 loops=1)

  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND ((valid_range @> '2020-04-30 23:59:00+00'::timestamp with time zone) OR (valid_range @> '2020-05-01 00:00:00+00'::timestamp with time zone) OR (valid_range <@ '[""2020-04-30 23:59:00+00"",""2020-05-01 00:00:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 19798
7. 0.004 12.036 ↑ 1.0 1 1

Limit (cost=1,169.85..1,169.85 rows=1 width=221) (actual time=12.035..12.036 rows=1 loops=1)

8. 5.632 12.032 ↑ 25,849.0 1 1

Sort (cost=1,169.85..1,234.47 rows=25,849 width=221) (actual time=12.032..12.032 rows=1 loops=1)

  • Sort Key: g_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
9. 6.400 6.400 ↓ 1.0 25,874 1

Seq Scan on groups_org_part_33 g_1 (cost=0.00..1,040.60 rows=25,849 width=221) (actual time=0.012..6.400 rows=25,874 loops=1)

  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
  • Rows Removed by Filter: 39
10.          

CTE get_shifts

11. 7.845 369.127 ↑ 1.1 9,825 1

Nested Loop (cost=241.78..863.46 rows=10,499 width=391) (actual time=53.048..369.127 rows=9,825 loops=1)

12. 6.736 55.532 ↓ 30.6 6,115 1

HashAggregate (cost=241.36..243.36 rows=200 width=16) (actual time=52.946..55.532 rows=6,115 loops=1)

  • Group Key: get_groups.group_uuid
13. 48.796 48.796 ↑ 1.8 6,116 1

CTE Scan on get_groups (cost=0.00..214.54 rows=10,727 width=16) (actual time=36.605..48.796 rows=6,116 loops=1)

14. 305.750 305.750 ↑ 2.0 2 6,115

Index Scan using shifts_org_part_6_group_uuid_idx on shifts_org_part_6 s (cost=0.42..3.06 rows=4 width=391) (actual time=0.011..0.050 rows=2 loops=6,115)

  • Index Cond: (group_uuid = get_groups.group_uuid)
  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND ((valid_range @> '2020-04-30 23:59:00+00'::timestamp with time zone) OR (valid_range @> '2020-05-01 00:00:00+00'::timestamp with time zone) OR (valid_range <@ '[""2020-04-30 23:59:00+00"",""2020-05-01 00:00:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 27
15.          

CTE get_shift_occurrences

16. 13.326 14,743.198 ↑ 51.8 6,943 1

Unique (cost=90,930,930.76..90,934,527.14 rows=359,638 width=711) (actual time=14,695.449..14,743.198 rows=6,943 loops=1)

17. 201.777 14,729.872 ↑ 16.6 21,658 1

Sort (cost=90,930,930.76..90,931,829.86 rows=359,638 width=711) (actual time=14,695.447..14,729.872 rows=21,658 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s_1.shift_uuid
  • Sort Method: external merge Disk: 13056kB
18. 41.790 14,528.095 ↑ 16.6 21,658 1

Nested Loop Left Join (cost=829.28..90,752,339.03 rows=359,638 width=711) (actual time=418.025..14,528.095 rows=21,658 loops=1)

19. 33.451 9,310.043 ↓ 30.2 21,658 1

Hash Join (cost=587.51..90,569,162.63 rows=717 width=631) (actual time=413.908..9,310.043 rows=21,658 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-01 00:00:00+00'::timestamp with time zone)) OR ((NOT (SubPlan 3)) AND (s_1.revision_uuid = (SubPlan 4))))
20. 26.404 8,870.206 ↓ 5.6 21,768 1

Nested Loop (cost=246.29..22,360,591.89 rows=3,906 width=349) (actual time=7.280..8,870.206 rows=21,768 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-01 00:00:00+00'::timestamp with time zone)) OR ((NOT (SubPlan 5)) AND (g_2.revision_uuid = (SubPlan 6)))))
  • Rows Removed by Join Filter: 10
21. 15.029 27.406 ↓ 1.1 6,116 1

Hash Join (cost=245.86..607.89 rows=5,364 width=301) (actual time=5.776..27.406 rows=6,116 loops=1)

  • Hash Cond: (g_2.group_uuid = get_groups_4.group_uuid)
22. 6.625 6.625 ↑ 1.8 6,116 1

CTE Scan on get_groups g_2 (cost=0.00..214.54 rows=10,727 width=285) (actual time=0.002..6.625 rows=6,116 loops=1)

23. 1.125 5.752 ↓ 30.6 6,115 1

Hash (cost=243.36..243.36 rows=200 width=16) (actual time=5.752..5.752 rows=6,115 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 351kB
24. 3.403 4.627 ↓ 30.6 6,115 1

HashAggregate (cost=241.36..243.36 rows=200 width=16) (actual time=3.901..4.627 rows=6,115 loops=1)

  • Group Key: get_groups_4.group_uuid
25. 1.224 1.224 ↑ 1.8 6,116 1

CTE Scan on get_groups get_groups_4 (cost=0.00..214.54 rows=10,727 width=16) (actual time=0.002..1.224 rows=6,116 loops=1)

26. 8,813.156 8,813.156 ↑ 1.8 4 6,116

Index Scan using shift_occurrence_org_part_7_group_uuid_idx on shift_occurrence_org_part_7 shiftoccurrence (cost=0.43..38.34 rows=7 width=80) (actual time=0.661..1.441 rows=4 loops=6,116)

  • Index 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-04-30 23:59:00+00'::timestamp with time zone) OR (tstzrange((start_date)::timestamp with time zone, (end_date)::timestamp with time zone, '[)'::text) @> '2020-05-01 00:00:00+00'::timestamp with time zone) OR (tstzrange((start_date)::timestamp with time zone, (end_date)::timestamp with time zone, '[)'::text) <@ '[""2020-04-30 23:59:00+00"",""2020-05-01 00:00:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 452
27.          

SubPlan (for Nested Loop)

28. 3.240 3.240 ↑ 1.0 1 10

CTE Scan on get_groups get_groups_1 (cost=0.00..321.81 rows=1 width=0) (actual time=0.324..0.324 rows=1 loops=10)

  • Filter: ((shiftoccurrence.group_uuid = group_uuid) AND (valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-01 00:00:00+00'::timestamp with time zone)))
  • Rows Removed by Filter: 1585
29. 0.000 0.000 ↓ 0.0 0

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

30. 0.000 0.000 ↓ 0.0 0

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

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

Result (cost=0.01..268.18 rows=1 width=48) (never executed)

  • One-Time Filter: (lower((g_2.target_name)::text) = 'cpcsrt'::text)
32. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (is_visible AND (shiftoccurrence.group_uuid = group_uuid) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
33. 14.249 406.386 ↑ 1.1 9,825 1

Hash (cost=209.98..209.98 rows=10,499 width=298) (actual time=406.386..406.386 rows=9,825 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3357kB
34. 392.137 392.137 ↑ 1.1 9,825 1

CTE Scan on get_shifts s_1 (cost=0.00..209.98 rows=10,499 width=298) (actual time=53.058..392.137 rows=9,825 loops=1)

35.          

SubPlan (for Hash Join)

36. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_shifts (cost=0.00..314.97 rows=1 width=0) (never executed)

  • Filter: ((shiftoccurrence.shift_uuid = shift_uuid) AND (valid_range @> LEAST((shiftoccurrence.end_date)::timestamp with time zone, '2020-05-01 00:00:00+00'::timestamp with time zone)))
37. 0.000 0.000 ↓ 0.0 0

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

38. 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
39. 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)
40. 1,673.483 5,176.262 ↓ 0.0 0 21,658

Nested Loop (cost=241.78..246.69 rows=1 width=37) (actual time=0.239..0.239 rows=0 loops=21,658)

  • Join Filter: (revision_shift.group_uuid = get_groups_3.group_uuid)
  • Rows Removed by Join Filter: 1245
41. 173.264 173.264 ↓ 0.0 0 21,658

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.007..0.008 rows=0 loops=21,658)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
42. 3,328.558 3,329.515 ↓ 15.4 3,071 8,785

HashAggregate (cost=241.36..243.36 rows=200 width=16) (actual time=0.001..0.379 rows=3,071 loops=8,785)

  • Group Key: get_groups_3.group_uuid
43. 0.957 0.957 ↑ 1.8 6,116 1

CTE Scan on get_groups get_groups_3 (cost=0.00..214.54 rows=10,727 width=16) (actual time=0.001..0.957 rows=6,116 loops=1)

44. 8.431 14,824.890 ↑ 26.0 6,910 1

Hash Join (cost=17,886,623.68..17,896,760.98 rows=179,819 width=711) (actual time=14,812.733..14,824.890 rows=6,910 loops=1)

  • Hash Cond: (get_shift_occurrences.shift_occurrence_uuid = f1.shift_occurrence_uuid)
45. 14,699.220 14,699.220 ↑ 51.8 6,943 1

CTE Scan on get_shift_occurrences (cost=0.00..7,192.76 rows=359,638 width=56) (actual time=14,695.455..14,699.220 rows=6,943 loops=1)

46. 1.735 117.239 ↓ 34.5 6,910 1

Hash (cost=17,886,621.18..17,886,621.18 rows=200 width=16) (actual time=117.239..117.239 rows=6,910 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 388kB
47. 5.929 115.504 ↓ 34.5 6,910 1

HashAggregate (cost=17,886,619.18..17,886,621.18 rows=200 width=16) (actual time=113.696..115.504 rows=6,910 loops=1)

  • Group Key: f1.shift_occurrence_uuid
48. 6.658 109.575 ↓ 1.3 6,910 1

Merge Left Join (cost=99,728.48..17,886,605.78 rows=5,362 width=16) (actual time=98.491..109.575 rows=6,910 loops=1)

  • Merge Cond: (f1.shift_uuid = f2.shift_uuid)
  • Join Filter: ((f2.shift_occurrence_uuid <> f1.shift_occurrence_uuid) AND (f2.created_shift_revision_date > f1.created_shift_revision_date) AND (tsrange(f2.start_date, f2.end_date, '[)'::text) && tsrange(f1.start_date, f1.end_date, '[)'::text)))
  • Rows Removed by Join Filter: 7466
  • Filter: (('(1)'::record) IS NULL)
  • Rows Removed by Filter: 87
49. 10.629 89.019 ↑ 51.8 6,943 1

Sort (cost=48,284.74..49,183.83 rows=359,638 width=56) (actual time=87.701..89.019 rows=6,943 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 959kB
50. 78.390 78.390 ↑ 51.8 6,943 1

CTE Scan on get_shift_occurrences f1 (cost=0.00..7,192.76 rows=359,638 width=56) (actual time=0.007..78.390 rows=6,943 loops=1)

51. 2.178 13.898 ↑ 47.6 7,553 1

Materialize (cost=51,443.74..53,241.93 rows=359,638 width=88) (actual time=10.769..13.898 rows=7,553 loops=1)

52. 5.199 11.720 ↑ 51.8 6,943 1

Sort (cost=51,443.74..52,342.83 rows=359,638 width=88) (actual time=10.760..11.720 rows=6,943 loops=1)

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 1169kB
53. 6.521 6.521 ↑ 51.8 6,943 1

CTE Scan on get_shift_occurrences f2 (cost=0.00..7,192.76 rows=359,638 width=88) (actual time=0.020..6.521 rows=6,943 loops=1)

Planning time : 9.762 ms
Execution time : 14,846.661 ms