explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4ny6

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 6,979.281 ↓ 35.0 35 1

UERY PLANLimit (cost=3,260.98..3,260.99 rows=1 width=743) (actual time=6,979.276..6,979.281 rows=35 loops=1)

2.          

CTE get_groups

3. 0.012 23.001 ↑ 98.0 1 1

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

4. 0.025 22.989 ↑ 49.0 2 1

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

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

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

6. 9.857 12.515 ↑ 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.050..12.515 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.002 2.658 ↓ 0.0 0 1

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

8. 1.397 1.397 ↓ 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.396..1.397 rows=6,046 loops=1)

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

  • Index Cond: (valid_range @> '2020-05-22 06:28:00+00'::timestamp with time zone)
10. 0.631 0.631 ↓ 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.631..0.631 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.446 10.446 ↑ 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.517..10.446 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_shift_occurrences

13. 0.594 6,976.679 ↓ 9.5 142 1

Unique (cost=698.53..698.68 rows=15 width=742) (actual time=6,975.918..6,976.679 rows=142 loops=1)

14. 10.983 6,976.085 ↓ 184.3 2,765 1

Sort (cost=698.53..698.57 rows=15 width=742) (actual time=6,975.917..6,976.085 rows=2,765 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 2851kB
15. 6.550 6,965.102 ↓ 184.3 2,765 1

Nested Loop Left Join (cost=40.33..698.24 rows=15 width=742) (actual time=29.703..6,965.102 rows=2,765 loops=1)

  • Join Filter: (revision_shift.group_uuid = g_2.group_uuid)
16. 12.864 6,941.962 ↓ 921.7 2,765 1

Nested Loop (cost=39.91..695.45 rows=3 width=655) (actual time=29.675..6,941.962 rows=2,765 loops=1)

17. 2.799 47.013 ↓ 2,765.0 2,765 1

Nested Loop (cost=13.05..631.73 rows=1 width=349) (actual time=23.713..47.013 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 4)) AND (g_2.revision_uuid = (SubPlan 5)))))
18. 0.031 23.052 ↑ 1.0 1 1

Hash Join (cost=4.42..5.78 rows=1 width=301) (actual time=23.049..23.052 rows=1 loops=1)

  • Hash Cond: (get_groups_2.group_uuid = g_2.group_uuid)
19. 0.006 0.007 ↑ 98.0 1 1

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

  • Group Key: get_groups_2.group_uuid
20. 0.001 0.001 ↑ 98.0 1 1

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

21. 0.007 23.014 ↑ 1.0 1 1

Hash (cost=2.21..2.21 rows=1 width=285) (actual time=23.014..23.014 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 23.007 23.007 ↑ 1.0 1 1

CTE Scan on get_groups g_2 (cost=0.00..2.21 rows=1 width=285) (actual time=23.005..23.007 rows=1 loops=1)

  • Filter: ((is_visible IS TRUE) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
23. 20.650 21.162 ↓ 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.650..21.162 rows=2,765 loops=1)

  • Recheck Cond: (group_uuid = get_groups_2.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
24. 0.512 0.512 ↓ 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.512..0.512 rows=3,406 loops=1)

  • Index Cond: (group_uuid = get_groups_2.group_uuid)
25.          

SubPlan (for Nested Loop)

26. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((is_visible IS TRUE) AND (shiftoccurrence.group_uuid = group_uuid) 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)))
27. 0.000 0.000 ↓ 0.0 0

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

28. 0.000 0.000 ↓ 0.0 0

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

  • Sort Key: get_groups_1.valid_range
29. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((is_visible IS TRUE) AND (shiftoccurrence.group_uuid = group_uuid) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
30. 27.650 6,882.085 ↑ 1.0 1 2,765

Bitmap Heap Scan on shifts_org_part_6 s (cost=26.86..63.71 rows=1 width=322) (actual time=2.489..2.489 rows=1 loops=2,765)

  • 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 3))))
  • 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 2)) AND (revision_uuid = (SubPlan 3)))))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=5530
31. 91.245 6,760.425 ↓ 0.0 0 2,765

BitmapAnd (cost=26.86..26.86 rows=1 width=0) (actual time=2.445..2.445 rows=0 loops=2,765)

32. 27.650 27.650 ↓ 4.5 94 2,765

Bitmap Index Scan on shifts_org_part_6_shift_uuid_idx (cost=0.00..0.62 rows=21 width=0) (actual time=0.010..0.010 rows=94 loops=2,765)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
33. 270.970 6,641.530 ↓ 0.0 0 2,765

BitmapOr (cost=25.99..25.99 rows=1,002 width=0) (actual time=2.402..2.402 rows=0 loops=2,765)

34. 6,359.500 6,359.500 ↓ 10.9 10,884 2,765

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=2.300..2.300 rows=10,884 loops=2,765)

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

Bitmap Index Scan on shifts_org_part_6_revision_uuid_uidx (cost=0.00..18.10 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=2,765)

  • Index Cond: (revision_uuid = (SubPlan 3))
36.          

SubPlan (for Bitmap Index Scan)

37. 2.765 259.910 ↑ 1.0 1 2,765

Limit (cost=17.63..17.64 rows=1 width=37) (actual time=0.094..0.094 rows=1 loops=2,765)

38. 69.125 257.145 ↑ 21.0 1 2,765

Sort (cost=17.63..17.69 rows=21 width=37) (actual time=0.093..0.093 rows=1 loops=2,765)

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
39. 188.020 188.020 ↓ 4.5 94 2,765

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.008..0.068 rows=94 loops=2,765)

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

SubPlan (for Bitmap Heap Scan)

41. 94.010 94.010 ↑ 1.0 1 2,765

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.034..0.034 rows=1 loops=2,765)

  • 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: 37
42. 16.590 16.590 ↓ 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.006..0.006 rows=0 loops=2,765)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
43. 0.103 6,979.277 ↓ 35.0 35 1

Sort (cost=2.30..2.30 rows=1 width=743) (actual time=6,979.275..6,979.277 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
44. 0.047 6,979.174 ↓ 35.0 35 1

Hash Semi Join (cost=1.94..2.29 rows=1 width=743) (actual time=6,979.125..6,979.174 rows=35 loops=1)

  • Hash Cond: (get_shift_occurrences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
45. 6,975.939 6,975.939 ↓ 9.5 142 1

CTE Scan on get_shift_occurrences (cost=0.00..0.30 rows=15 width=743) (actual time=6,975.921..6,975.939 rows=142 loops=1)

46. 0.008 3.188 ↓ 35.0 35 1

Hash (cost=1.92..1.92 rows=1 width=16) (actual time=3.188..3.188 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
47. 0.016 3.180 ↓ 35.0 35 1

Subquery Scan on dups (cost=1.40..1.92 rows=1 width=16) (actual time=3.065..3.180 rows=35 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 123
48. 0.113 3.164 ↓ 10.5 158 1

WindowAgg (cost=1.40..1.70 rows=15 width=56) (actual time=3.061..3.164 rows=158 loops=1)

49. 0.055 3.051 ↓ 10.5 158 1

Sort (cost=1.40..1.44 rows=15 width=32) (actual time=3.042..3.051 rows=158 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
50. 2.042 2.996 ↓ 10.5 158 1

Hash Left Join (cost=0.49..1.11 rows=15 width=32) (actual time=1.007..2.996 rows=158 loops=1)

  • Hash 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
51. 0.020 0.020 ↓ 9.5 142 1

CTE Scan on get_shift_occurrences f1 (cost=0.00..0.30 rows=15 width=56) (actual time=0.001..0.020 rows=142 loops=1)

52. 0.042 0.934 ↓ 9.5 142 1

Hash (cost=0.30..0.30 rows=15 width=40) (actual time=0.934..0.934 rows=142 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
53. 0.892 0.892 ↓ 9.5 142 1

CTE Scan on get_shift_occurrences f2 (cost=0.00..0.30 rows=15 width=40) (actual time=0.007..0.892 rows=142 loops=1)

Planning time : 7.495 ms
Execution time : 6,980.902 ms