explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ErZE

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 733.400 ↑ 33.3 3 1

Limit (cost=1,459,170.73..1,459,170.98 rows=100 width=743) (actual time=733.399..733.400 rows=3 loops=1)

2.          

CTE get_distinct_shift_occurences

3. 0.076 732.950 ↑ 5,883.6 16 1

Unique (cost=400,171.75..401,113.13 rows=94,138 width=678) (actual time=732.857..732.950 rows=16 loops=1)

4. 1.008 732.874 ↑ 281.9 334 1

Sort (cost=400,171.75..400,407.09 rows=94,138 width=678) (actual time=732.854..732.874 rows=334 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 310kB
5. 0.337 731.866 ↑ 281.9 334 1

Nested Loop Left Join (cost=2,172.49..374,192.28 rows=94,138 width=678) (actual time=422.776..731.866 rows=334 loops=1)

6. 0.177 728.857 ↓ 3.6 334 1

Nested Loop (cost=2,172.07..372,465.82 rows=94 width=591) (actual time=422.727..728.857 rows=334 loops=1)

7. 7.313 709.308 ↓ 3.6 334 1

Hash Join (cost=2,171.65..372,334.56 rows=94 width=285) (actual time=422.663..709.308 rows=334 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 1)) AND (g.revision_uuid = (SubPlan 2))))
  • Rows Removed by Join Filter: 668
8. 0.000 673.521 ↓ 4.0 166,068 1

Gather (cost=1,000.00..112,344.95 rows=41,644 width=80) (actual time=2.109..673.521 rows=166,068 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 684.025 684.025 ↓ 3.2 33,214 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.744..684.025 rows=33,214 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-15 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-15 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 535227
10. 0.022 26.470 ↑ 43.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 26.448 26.448 ↑ 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.203..26.448 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
12.          

SubPlan (for Hash Join)

13. 2.004 2.004 ↑ 1.0 1 668

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.003..0.003 rows=1 loops=668)

  • 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
14. 0.000 0.000 ↓ 0.0 0

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

15. 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
16. 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)
17. 19.372 19.372 ↑ 1.0 1 334

Index Scan using shifts_org_part_6_shift_uuid_idx on shifts_org_part_6 s (cost=0.42..1.39 rows=1 width=322) (actual time=0.006..0.058 rows=1 loops=334)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_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)))
  • Rows Removed by Filter: 94
18. 2.672 2.672 ↓ 0.0 0 334

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.008..0.008 rows=0 loops=334)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
19. 0.044 733.398 ↑ 941.3 3 1

Sort (cost=1,058,057.60..1,058,064.66 rows=2,824 width=743) (actual time=733.398..733.398 rows=3 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: 28kB
20. 0.011 733.354 ↑ 941.3 3 1

Nested Loop Semi Join (cost=1,039,840.26..1,057,949.67 rows=2,824 width=743) (actual time=733.347..733.354 rows=3 loops=1)

  • Join Filter: (get_distinct_shift_occurences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
  • Rows Removed by Join Filter: 42
21. 732.863 732.863 ↑ 5,883.6 16 1

CTE Scan on get_distinct_shift_occurences (cost=0.00..1,882.76 rows=94,138 width=743) (actual time=732.861..732.863 rows=16 loops=1)

22. 0.018 0.480 ↑ 2.0 3 16

Materialize (cost=1,039,840.26..1,047,594.51 rows=6 width=16) (actual time=0.029..0.030 rows=3 loops=16)

23. 0.006 0.462 ↑ 2.0 3 1

Subquery Scan on dups (cost=1,039,840.26..1,047,594.48 rows=6 width=16) (actual time=0.456..0.462 rows=3 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 15
24. 0.031 0.456 ↑ 12,308.3 18 1

WindowAgg (cost=1,039,840.26..1,044,271.24 rows=221,549 width=56) (actual time=0.443..0.456 rows=18 loops=1)

25. 0.036 0.425 ↑ 12,308.3 18 1

Sort (cost=1,039,840.26..1,040,394.13 rows=221,549 width=32) (actual time=0.424..0.425 rows=18 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
26. 0.217 0.389 ↑ 12,308.3 18 1

Merge Left Join (cost=19,319.46..1,016,760.99 rows=221,549 width=32) (actual time=0.351..0.389 rows=18 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))
  • Rows Removed by Join Filter: 52
27. 0.023 0.140 ↑ 5,883.6 16 1

Sort (cost=9,659.73..9,895.08 rows=94,138 width=56) (actual time=0.139..0.140 rows=16 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 26kB
28. 0.117 0.117 ↑ 5,883.6 16 1

CTE Scan on get_distinct_shift_occurences f1 (cost=0.00..1,882.76 rows=94,138 width=56) (actual time=0.005..0.117 rows=16 loops=1)

29. 0.022 0.032 ↑ 1,426.3 66 1

Sort (cost=9,659.73..9,895.08 rows=94,138 width=40) (actual time=0.028..0.032 rows=66 loops=1)

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 26kB
30. 0.010 0.010 ↑ 5,883.6 16 1

CTE Scan on get_distinct_shift_occurences f2 (cost=0.00..1,882.76 rows=94,138 width=40) (actual time=0.002..0.010 rows=16 loops=1)

Planning time : 6.636 ms
Execution time : 746.173 ms