explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7eo

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 6,948.969 ↑ 1.0 100 1

Limit (cost=3,532.76..3,533.01 rows=100 width=743) (actual time=6,948.956..6,948.969 rows=100 loops=1)

2.          

CTE get_groups

3. 0.003 22.935 ↑ 98.0 1 1

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

4. 0.030 22.932 ↑ 49.0 2 1

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

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

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

6. 9.978 12.879 ↑ 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.379..12.879 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.901 ↓ 0.0 0 1

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

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

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

  • Index Cond: (valid_range @> '2020-05-22 06:28:00+00'::timestamp with time zone)
10. 0.677 0.677 ↓ 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.677..0.677 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.020 10.020 ↑ 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.342..10.020 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.624 6,948.783 ↑ 13.8 142 1

Unique (cost=838.85..858.48 rows=1,963 width=742) (actual time=6,947.989..6,948.783 rows=142 loops=1)

14. 10.185 6,948.159 ↓ 1.4 2,765 1

Sort (cost=838.85..843.75 rows=1,963 width=742) (actual time=6,947.988..6,948.159 rows=2,765 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 2851kB
15. 5.529 6,937.974 ↓ 1.4 2,765 1

Nested Loop Left Join (cost=40.33..731.48 rows=1,963 width=742) (actual time=29.897..6,937.974 rows=2,765 loops=1)

16. 12.010 6,915.855 ↓ 1,382.5 2,765 1

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

17. 2.535 46.645 ↓ 2,765.0 2,765 1

Nested Loop (cost=13.05..631.73 rows=1 width=349) (actual time=23.831..46.645 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.027 22.985 ↑ 1.0 1 1

Hash Join (cost=4.42..5.78 rows=1 width=301) (actual time=22.982..22.985 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.001..0.001 rows=1 loops=1)

21. 0.007 22.951 ↑ 1.0 1 1

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

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

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

  • Filter: ((is_visible IS TRUE) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid))
23. 20.433 21.125 ↓ 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.836..21.125 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.692 0.692 ↓ 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.692..0.692 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. 24.885 6,857.200 ↑ 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.480..2.480 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 5))))
  • 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: 1
  • Heap Blocks: exact=5530
31. 91.245 6,738.305 ↓ 0.0 0 2,765

BitmapAnd (cost=26.86..26.86 rows=1 width=0) (actual time=2.437..2.437 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. 265.440 6,622.175 ↓ 0.0 0 2,765

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

34. 6,345.675 6,345.675 ↓ 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.295..2.295 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 5))
36.          

SubPlan (for Bitmap Index Scan)

37. 5.530 254.380 ↑ 1.0 1 2,765

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

38. 63.595 248.850 ↑ 21.0 1 2,765

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

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
39. 185.255 185.255 ↓ 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.067 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=37) (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.087 6,948.961 ↑ 19.6 100 1

Sort (cost=114.28..119.19 rows=1,963 width=743) (actual time=6,948.955..6,948.961 rows=100 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: 167kB
44. 6,948.874 6,948.874 ↑ 13.8 142 1

CTE Scan on get_shift_occurrences (cost=0.00..39.26 rows=1,963 width=743) (actual time=6,947.995..6,948.874 rows=142 loops=1)

Planning time : 7.431 ms
Execution time : 6,950.428 ms