explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pv7oe

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 5,748.819 ↓ 35.0 35 1

Limit (cost=3,231.44..3,231.44 rows=1 width=743) (actual time=5,748.815..5,748.819 rows=35 loops=1)

2.          

CTE get_groups

3. 0.007 23.580 ↑ 98.0 1 1

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

4. 0.039 23.573 ↑ 49.0 2 1

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

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

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

6. 10.400 13.314 ↑ 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.649..13.314 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.914 ↓ 0.0 0 1

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

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

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

  • Index Cond: (valid_range @> '2020-05-22 06:28:00+00'::timestamp with time zone)
10. 0.668 0.668 ↓ 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.668..0.668 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.217 10.217 ↑ 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.414..10.217 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.591 5,746.014 ↓ 71.0 142 1

Unique (cost=671.11..671.13 rows=2 width=742) (actual time=5,745.249..5,746.014 rows=142 loops=1)

14. 12.924 5,745.423 ↓ 1,382.5 2,765 1

Sort (cost=671.11..671.12 rows=2 width=742) (actual time=5,745.246..5,745.423 rows=2,765 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 2851kB
15. 5.670 5,732.499 ↓ 1,382.5 2,765 1

Nested Loop Left Join (cost=15.10..671.10 rows=2 width=742) (actual time=29.414..5,732.499 rows=2,765 loops=1)

  • Join Filter: (revision_shift.group_uuid = g_2.group_uuid)
16. 8.677 5,710.239 ↓ 2,765.0 2,765 1

Nested Loop (cost=14.68..670.22 rows=1 width=655) (actual time=29.367..5,710.239 rows=2,765 loops=1)

17. 2.104 47.137 ↓ 2,765.0 2,765 1

Nested Loop (cost=13.05..631.73 rows=1 width=381) (actual time=24.464..47.137 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 2)) AND (g_2.revision_uuid = (SubPlan 3)))))
18. 0.025 23.621 ↑ 1.0 1 1

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

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

HashAggregate (cost=2.21..3.19 rows=98 width=16) (actual time=0.004..0.006 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.005 23.590 ↑ 1.0 1 1

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

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

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

  • Filter: ((is_visible IS TRUE) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
23. 20.728 21.412 ↓ 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.822..21.412 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.684 0.684 ↓ 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.684..0.684 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. 410.370 5,654.425 ↑ 1.0 1 2,765

Bitmap Heap Scan on shifts_org_part_6 s (cost=1.63..38.48 rows=1 width=338) (actual time=1.955..2.045 rows=1 loops=2,765)

  • Recheck Cond: ((shift_uuid = shiftoccurrence.shift_uuid) AND (group_uuid = shiftoccurrence.group_uuid))
  • 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 4)) AND (revision_uuid = (SubPlan 5)))))
  • Rows Removed by Filter: 93
  • Heap Blocks: exact=178642
31. 8.295 96.775 ↓ 0.0 0 2,765

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

32. 24.885 24.885 ↓ 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.009..0.009 rows=94 loops=2,765)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
33. 63.595 63.595 ↓ 8.1 334 2,765

Bitmap Index Scan on shifts_org_part_6_group_uuid_idx (cost=0.00..0.77 rows=41 width=0) (actual time=0.023..0.023 rows=334 loops=2,765)

  • Index Cond: (group_uuid = shiftoccurrence.group_uuid)
34.          

SubPlan (for Bitmap Heap Scan)

35. 5,147.280 5,147.280 ↑ 1.0 1 257,364

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.020..0.020 rows=1 loops=257,364)

  • 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: 39
36. 0.000 0.000 ↓ 0.0 0

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

37. 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_1.valid_range
38. 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 shifts_org_part_6_1 (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)
39. 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)
40. 0.096 5,748.815 ↓ 35.0 35 1

Sort (cost=0.31..0.31 rows=1 width=743) (actual time=5,748.813..5,748.815 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
41. 0.052 5,748.719 ↓ 35.0 35 1

Hash Semi Join (cost=0.24..0.30 rows=1 width=743) (actual time=5,748.669..5,748.719 rows=35 loops=1)

  • Hash Cond: (get_shift_occurrences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
42. 5,745.269 5,745.269 ↓ 71.0 142 1

CTE Scan on get_shift_occurrences (cost=0.00..0.04 rows=2 width=743) (actual time=5,745.251..5,745.269 rows=142 loops=1)

43. 0.012 3.398 ↓ 35.0 35 1

Hash (cost=0.23..0.23 rows=1 width=16) (actual time=3.398..3.398 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 0.015 3.386 ↓ 35.0 35 1

Subquery Scan on dups (cost=0.16..0.23 rows=1 width=16) (actual time=3.273..3.386 rows=35 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 123
45. 0.123 3.371 ↓ 79.0 158 1

WindowAgg (cost=0.16..0.20 rows=2 width=56) (actual time=3.269..3.371 rows=158 loops=1)

46. 0.070 3.248 ↓ 79.0 158 1

Sort (cost=0.16..0.16 rows=2 width=32) (actual time=3.240..3.248 rows=158 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 33kB
47. 2.069 3.178 ↓ 79.0 158 1

Hash Left Join (cost=0.07..0.15 rows=2 width=32) (actual time=1.182..3.178 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
48. 0.021 0.021 ↓ 71.0 142 1

CTE Scan on get_shift_occurrences f1 (cost=0.00..0.04 rows=2 width=56) (actual time=0.001..0.021 rows=142 loops=1)

49. 0.044 1.088 ↓ 71.0 142 1

Hash (cost=0.04..0.04 rows=2 width=40) (actual time=1.088..1.088 rows=142 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
50. 1.044 1.044 ↓ 71.0 142 1

CTE Scan on get_shift_occurrences f2 (cost=0.00..0.04 rows=2 width=40) (actual time=0.007..1.044 rows=142 loops=1)

Planning time : 8.503 ms
Execution time : 5,750.387 ms