explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hcET

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

Limit (cost=13,224,033.04..13,224,033.29 rows=100 width=743) (actual time=1,490.509..1,490.510 rows=3 loops=1)

2.          

CTE get_distinct_shift_occurences

3. 0.141 1,490.010 ↑ 20,592.6 16 1

Unique (cost=478,271.87..481,566.69 rows=329,482 width=678) (actual time=1,489.837..1,490.010 rows=16 loops=1)

4. 1.368 1,489.869 ↑ 986.5 334 1

Sort (cost=478,271.87..479,095.58 rows=329,482 width=678) (actual time=1,489.834..1,489.869 rows=334 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 310kB
5. 1.179 1,488.501 ↑ 986.5 334 1

Nested Loop Left Join (cost=2,198.93..384,366.36 rows=329,482 width=678) (actual time=452.130..1,488.501 rows=334 loops=1)

6. 2.021 1,483.648 ↓ 1.0 334 1

Nested Loop (cost=2,198.51..378,323.77 rows=329 width=591) (actual time=452.102..1,483.648 rows=334 loops=1)

7. 8.956 740.481 ↓ 3.6 334 1

Hash Join (cost=2,171.65..372,334.56 rows=94 width=285) (actual time=446.722..740.481 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 3)) AND (g.revision_uuid = (SubPlan 4))))
  • Rows Removed by Join Filter: 668
8. 0.000 703.262 ↓ 4.0 164,747 1

Gather (cost=1,000.00..112,344.95 rows=41,644 width=80) (actual time=1.903..703.262 rows=164,747 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 718.409 718.409 ↓ 3.2 32,949 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.623..718.409 rows=32,949 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.018 24.923 ↑ 43.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 24.905 24.905 ↑ 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.310..24.905 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. 3.340 3.340 ↑ 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.005..0.005 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. 3.674 741.146 ↑ 1.0 1 334

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

  • 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=668
18. 13.026 724.112 ↓ 0.0 0 334

BitmapAnd (cost=26.86..26.86 rows=1 width=0) (actual time=2.168..2.168 rows=0 loops=334)

19. 3.674 3.674 ↓ 4.5 95 334

Bitmap Index Scan on shifts_org_part_6_shift_uuid_idx (cost=0.00..0.62 rows=21 width=0) (actual time=0.011..0.011 rows=95 loops=334)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
20. 40.414 707.412 ↓ 0.0 0 334

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

21. 665.328 665.328 ↓ 11.4 11,410 334

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=1.992..1.992 rows=11,410 loops=334)

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

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=334)

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

SubPlan (for Bitmap Index Scan)

24. 0.334 38.410 ↑ 1.0 1 334

Limit (cost=17.63..17.64 rows=1 width=37) (actual time=0.115..0.115 rows=1 loops=334)

25. 9.018 38.076 ↑ 21.0 1 334

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

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
26. 29.058 29.058 ↓ 4.5 95 334

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.087 rows=95 loops=334)

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

SubPlan (for Bitmap Heap Scan)

28. 13.360 13.360 ↑ 1.0 1 334

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.040..0.040 rows=1 loops=334)

  • 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: 41
29. 3.674 3.674 ↓ 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.011..0.011 rows=0 loops=334)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
30. 0.082 1,490.508 ↑ 37,341.3 3 1

Sort (cost=12,742,466.35..12,742,746.41 rows=112,024 width=743) (actual time=1,490.507..1,490.508 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
31. 0.039 1,490.426 ↑ 37,341.3 3 1

Hash Semi Join (cost=12,728,643.90..12,738,184.87 rows=112,024 width=743) (actual time=1,490.423..1,490.426 rows=3 loops=1)

  • Hash Cond: (get_distinct_shift_occurences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
32. 1,489.843 1,489.843 ↑ 20,592.6 16 1

CTE Scan on get_distinct_shift_occurences (cost=0.00..6,589.64 rows=329,482 width=743) (actual time=1,489.840..1,489.843 rows=16 loops=1)

33. 0.009 0.544 ↑ 22.7 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.006 0.535 ↑ 22.7 3 1

Subquery Scan on dups (cost=12,633,654.45..12,728,643.05 rows=68 width=16) (actual time=0.522..0.535 rows=3 loops=1)

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 15
35. 0.048 0.529 ↑ 150,775.6 18 1

WindowAgg (cost=12,633,654.45..12,687,933.65 rows=2,713,960 width=56) (actual time=0.515..0.529 rows=18 loops=1)

36. 0.021 0.481 ↑ 150,775.6 18 1

Sort (cost=12,633,654.45..12,640,439.35 rows=2,713,960 width=32) (actual time=0.480..0.481 rows=18 loops=1)

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
37. 0.140 0.460 ↑ 150,775.6 18 1

Merge Left Join (cost=86,607.05..12,301,896.86 rows=2,713,960 width=32) (actual time=0.387..0.460 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
38. 0.025 0.260 ↑ 20,592.6 16 1

Sort (cost=44,026.90..44,850.61 rows=329,482 width=56) (actual time=0.259..0.260 rows=16 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 26kB
39. 0.235 0.235 ↑ 20,592.6 16 1

CTE Scan on get_distinct_shift_occurences f1 (cost=0.00..6,589.64 rows=329,482 width=56) (actual time=0.024..0.235 rows=16 loops=1)

40. 0.037 0.060 ↑ 4,992.2 66 1

Materialize (cost=42,580.15..44,227.56 rows=329,482 width=40) (actual time=0.049..0.060 rows=66 loops=1)

41. 0.015 0.023 ↑ 20,592.6 16 1

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

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 26kB
42. 0.008 0.008 ↑ 20,592.6 16 1

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

Planning time : 7.196 ms
Execution time : 1,503.142 ms