explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gwUz

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 823.957 ↑ 50.0 2 1

Limit (cost=13,126,832.71..13,126,832.96 rows=100 width=743) (actual time=823.956..823.957 rows=2 loops=1)

2.          

CTE get_distinct_shift_occurences

3. 0.107 822.118 ↑ 8,237.0 40 1

Nested Loop Left Join (cost=2,198.93..384,366.36 rows=329,482 width=678) (actual time=501.715..822.118 rows=40 loops=1)

4. 0.229 821.291 ↑ 8.2 40 1

Nested Loop (cost=2,198.51..378,323.77 rows=329 width=591) (actual time=501.669..821.291 rows=40 loops=1)

5. 0.000 734.622 ↑ 2.4 40 1

Hash Join (cost=2,171.65..372,334.56 rows=94 width=285) (actual time=496.317..734.622 rows=40 loops=1)

  • Hash Cond: (shiftoccurrence.group_uuid = g.group_uuid)
  • Join Filter: ((g.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 (g.revision_uuid = (SubPlan 4))))
  • Rows Removed by Join Filter: 80
6. 0.000 715.341 ↑ 1.5 27,796 1

Gather (cost=1,000.00..112,344.95 rows=41,644 width=80) (actual time=2.636..715.341 rows=27,796 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
7. 717.146 717.146 ↑ 1.9 5,559 5 / 5

Parallel Seq Scan on shift_occurrence_org_part_7 shiftoccurrence (cost=0.00..107,180.55 rows=10,411 width=80) (actual time=3.762..717.146 rows=5,559 loops=5)

  • 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-05-21 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-05-21 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 562617
8. 0.024 26.002 ↑ 43.0 3 1

Hash (cost=1,170.04..1,170.04 rows=129 width=221) (actual time=26.001..26.002 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 25.978 25.978 ↑ 43.0 3 1

Seq Scan on groups_org_part_33 g (cost=0.00..1,170.04 rows=129 width=221) (actual time=10.245..25.978 rows=3 loops=1)

  • Filter: ((is_visible IS TRUE) AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (lower((target_name)::text) = 'cpcsrt'::text))
  • Rows Removed by Filter: 25905
10.          

SubPlan (for Hash Join)

11. 0.480 0.480 ↑ 1.0 1 80

Index Scan using groups_org_part_33_organization_uuid_group_uuid_sequence_lo_idx on groups_org_part_33 (cost=0.41..4.81 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=80)

  • Index Cond: ((organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (group_uuid = shiftoccurrence.group_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: 1
12. 0.000 0.000 ↓ 0.0 0

Limit (cost=4.80..4.80 rows=1 width=36) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Sort (cost=4.80..4.81 rows=3 width=36) (never executed)

  • Sort Key: groups_org_part_33_1.valid_range
14. 0.000 0.000 ↓ 0.0 0

Index Scan using groups_org_part_33_organization_uuid_group_uuid_sequence_lo_idx on groups_org_part_33 groups_org_part_33_1 (cost=0.41..4.79 rows=3 width=36) (never executed)

  • Index Cond: ((organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (group_uuid = shiftoccurrence.group_uuid))
  • Filter: (is_visible IS TRUE)
15. 0.400 86.440 ↑ 1.0 1 40

Bitmap Heap Scan on shifts_org_part_6 s (cost=26.86..63.71 rows=1 width=322) (actual time=2.161..2.161 rows=1 loops=40)

  • 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 2))))
  • 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 1)) AND (revision_uuid = (SubPlan 2)))))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=80
16. 1.560 84.360 ↓ 0.0 0 40

BitmapAnd (cost=26.86..26.86 rows=1 width=0) (actual time=2.109..2.109 rows=0 loops=40)

17. 0.480 0.480 ↓ 5.1 108 40

Bitmap Index Scan on shifts_org_part_6_shift_uuid_idx (cost=0.00..0.62 rows=21 width=0) (actual time=0.012..0.012 rows=108 loops=40)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
18. 5.600 82.320 ↓ 0.0 0 40

BitmapOr (cost=25.99..25.99 rows=1,002 width=0) (actual time=2.058..2.058 rows=0 loops=40)

19. 76.520 76.520 ↓ 11.9 11,948 40

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=1.913..1.913 rows=11,948 loops=40)

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

Bitmap Index Scan on shifts_org_part_6_revision_uuid_uidx (cost=0.00..18.10 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=40)

  • Index Cond: (revision_uuid = (SubPlan 2))
21.          

SubPlan (for Bitmap Index Scan)

22. 0.040 5.360 ↑ 1.0 1 40

Limit (cost=17.63..17.64 rows=1 width=37) (actual time=0.134..0.134 rows=1 loops=40)

23. 1.280 5.320 ↑ 21.0 1 40

Sort (cost=17.63..17.69 rows=21 width=37) (actual time=0.133..0.133 rows=1 loops=40)

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
24. 4.040 4.040 ↓ 5.1 108 40

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.010..0.101 rows=108 loops=40)

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

SubPlan (for Bitmap Heap Scan)

26. 1.680 1.680 ↑ 1.0 1 40

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.042..0.042 rows=1 loops=40)

  • 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: 44
27. 0.720 0.720 ↓ 0.0 0 40

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.018..0.018 rows=0 loops=40)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
28. 0.109 823.955 ↑ 56,012.0 2 1

Sort (cost=12,742,466.35..12,742,746.41 rows=112,024 width=743) (actual time=823.954..823.955 rows=2 loops=1)

  • Sort Key: get_distinct_shift_occurences.start_date, get_distinct_shift_occurences.end_date, get_distinct_shift_occurences.shift_uuid, get_distinct_shift_occurences.shift_valid_from DESC
  • Sort Method: quicksort Memory: 27kB
29. 0.027 823.846 ↑ 56,012.0 2 1

Hash Semi Join (cost=12,728,643.90..12,738,184.87 rows=112,024 width=743) (actual time=823.840..823.846 rows=2 loops=1)

  • Hash Cond: (get_distinct_shift_occurences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
30. 501.727 501.727 ↑ 8,237.0 40 1

CTE Scan on get_distinct_shift_occurences (cost=0.00..6,589.64 rows=329,482 width=743) (actual time=501.721..501.727 rows=40 loops=1)

31. 0.017 322.092 ↑ 34.0 2 1

Hash (cost=12,728,643.05..12,728,643.05 rows=68 width=16) (actual time=322.092..322.092 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.056 322.075 ↑ 34.0 2 1

Subquery Scan on dups (cost=12,633,654.45..12,728,643.05 rows=68 width=16) (actual time=321.693..322.075 rows=2 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 998
33. 0.418 322.019 ↑ 2,714.0 1,000 1

WindowAgg (cost=12,633,654.45..12,687,933.65 rows=2,713,960 width=56) (actual time=321.564..322.019 rows=1,000 loops=1)

34. 0.303 321.601 ↑ 2,714.0 1,000 1

Sort (cost=12,633,654.45..12,640,439.35 rows=2,713,960 width=32) (actual time=321.546..321.601 rows=1,000 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date
  • Sort Method: quicksort Memory: 78kB
35. 0.530 321.298 ↑ 2,714.0 1,000 1

Merge Left Join (cost=86,607.05..12,301,896.86 rows=2,713,960 width=32) (actual time=320.792..321.298 rows=1,000 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))
36. 0.063 320.641 ↑ 8,237.0 40 1

Sort (cost=44,026.90..44,850.61 rows=329,482 width=56) (actual time=320.639..320.641 rows=40 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 28kB
37. 320.578 320.578 ↑ 8,237.0 40 1

CTE Scan on get_distinct_shift_occurences f1 (cost=0.00..6,589.64 rows=329,482 width=56) (actual time=0.003..320.578 rows=40 loops=1)

38. 0.069 0.127 ↑ 332.5 991 1

Materialize (cost=42,580.15..44,227.56 rows=329,482 width=40) (actual time=0.074..0.127 rows=991 loops=1)

39. 0.028 0.058 ↑ 8,237.0 40 1

Sort (cost=42,580.15..43,403.86 rows=329,482 width=40) (actual time=0.056..0.058 rows=40 loops=1)

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 28kB
40. 0.030 0.030 ↑ 8,237.0 40 1

CTE Scan on get_distinct_shift_occurences f2 (cost=0.00..6,589.64 rows=329,482 width=40) (actual time=0.005..0.030 rows=40 loops=1)

Planning time : 10.977 ms
Execution time : 836.413 ms