explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lD5n

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 4,898.552 ↓ 0.0 0 1

Limit (cost=13,224,033.04..13,224,033.29 rows=100 width=743) (actual time=4,898.552..4,898.552 rows=0 loops=1)

2.          

CTE get_distinct_shift_occurences

3. 0.014 4,898.332 ↑ 164,741.0 2 1

Unique (cost=478,271.87..481,566.69 rows=329,482 width=678) (actual time=4,898.318..4,898.332 rows=2 loops=1)

4. 0.343 4,898.318 ↑ 8,237.0 40 1

Sort (cost=478,271.87..479,095.57 rows=329,482 width=678) (actual time=4,898.316..4,898.318 rows=40 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid
  • Sort Method: quicksort Memory: 52kB
5. 0.349 4,897.975 ↑ 8,237.0 40 1

Nested Loop Left Join (cost=2,198.93..384,366.35 rows=329,482 width=678) (actual time=3,864.652..4,897.975 rows=40 loops=1)

6. 0.539 4,848.746 ↑ 8.2 40 1

Nested Loop (cost=2,198.51..378,323.77 rows=329 width=591) (actual time=3,863.022..4,848.746 rows=40 loops=1)

7. 0.000 1,961.527 ↑ 2.4 40 1

Hash Join (cost=2,171.65..372,334.56 rows=94 width=285) (actual time=1,893.213..1,961.527 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
8. 0.000 1,782.724 ↑ 1.8 23,093 1

Gather (cost=1,000.00..112,344.95 rows=41,644 width=80) (actual time=6.817..1,782.724 rows=23,093 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
9. 2,530.267 2,530.267 ↑ 2.3 4,619 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=8.372..2,530.267 rows=4,619 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
10. 0.028 130.654 ↑ 43.0 3 1

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

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

Seq Scan on groups_org_part_33 g (cost=0.00..1,170.04 rows=129 width=221) (actual time=85.394..130.626 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: 25902
12.          

SubPlan (for Hash Join)

13. 64.480 64.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.806..0.806 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
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. 0.760 2,886.680 ↑ 1.0 1 40

Bitmap Heap Scan on shifts_org_part_6 s (cost=26.86..63.70 rows=1 width=322) (actual time=72.165..72.167 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
18. 2.640 2,883.480 ↓ 0.0 0 40

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

19. 3.400 3.400 ↓ 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.085..0.085 rows=108 loops=40)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
20. 2,494.600 2,877.440 ↓ 0.0 0 40

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

21. 378.680 378.680 ↓ 11.8 11,850 40

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=9.467..9.467 rows=11,850 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))
22. 0.000 4.160 ↑ 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.104..0.104 rows=1 loops=40)

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

SubPlan (for Bitmap Index Scan)

24. 0.120 2,494.160 ↑ 1.0 1 40

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

25. 2.960 2,494.040 ↑ 21.0 1 40

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

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
26. 2,491.080 2,491.080 ↓ 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=2.398..62.277 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)
27.          

SubPlan (for Bitmap Heap Scan)

28. 2.440 2.440 ↑ 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.061..0.061 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
29. 48.880 48.880 ↓ 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=1.222..1.222 rows=0 loops=40)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
30. 0.034 4,898.550 ↓ 0.0 0 1

Sort (cost=12,742,466.35..12,742,746.41 rows=112,024 width=743) (actual time=4,898.550..4,898.550 rows=0 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: 25kB
31. 0.021 4,898.516 ↓ 0.0 0 1

Hash Semi Join (cost=12,728,643.90..12,738,184.87 rows=112,024 width=743) (actual time=4,898.516..4,898.516 rows=0 loops=1)

  • Hash Cond: (get_distinct_shift_occurences.shift_occurrence_uuid = dups.shift_occurrence_uuid)
32. 4,898.321 4,898.321 ↑ 329,482.0 1 1

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

33. 0.001 0.174 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 0.004 0.173 ↓ 0.0 0 1

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

  • Filter: ((dups.row_number = 1) AND (dups.f1_date = dups.f2_date))
  • Rows Removed by Filter: 2
35. 0.029 0.169 ↑ 1,356,980.0 2 1

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

36. 0.010 0.140 ↑ 1,356,980.0 2 1

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

  • Sort Key: f1.shift_occurrence_uuid, f2.created_shift_revision_date
  • Sort Method: quicksort Memory: 25kB
37. 0.068 0.130 ↑ 1,356,980.0 2 1

Merge Left Join (cost=86,607.05..12,301,896.86 rows=2,713,960 width=32) (actual time=0.127..0.130 rows=2 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))
38. 0.006 0.037 ↑ 164,741.0 2 1

Sort (cost=44,026.90..44,850.61 rows=329,482 width=56) (actual time=0.037..0.037 rows=2 loops=1)

  • Sort Key: f1.shift_uuid
  • Sort Method: quicksort Memory: 25kB
39. 0.031 0.031 ↑ 164,741.0 2 1

CTE Scan on get_distinct_shift_occurences f1 (cost=0.00..6,589.64 rows=329,482 width=56) (actual time=0.005..0.031 rows=2 loops=1)

40. 0.003 0.025 ↑ 164,741.0 2 1

Materialize (cost=42,580.15..44,227.56 rows=329,482 width=40) (actual time=0.024..0.025 rows=2 loops=1)

41. 0.019 0.022 ↑ 164,741.0 2 1

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

  • Sort Key: f2.shift_uuid
  • Sort Method: quicksort Memory: 25kB
42. 0.003 0.003 ↑ 164,741.0 2 1

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

Planning time : 457.476 ms
Execution time : 4,919.400 ms