explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b5Hu

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 31,286.939 ↑ 1.0 100 1

Limit (cost=56,806,584.94..56,806,676.10 rows=100 width=678) (actual time=31,286.741..31,286.939 rows=100 loops=1)

2. 0.102 31,286.931 ↑ 8,328.0 100 1

Unique (cost=56,806,584.94..57,565,735.47 rows=832,800 width=678) (actual time=31,286.739..31,286.931 rows=100 loops=1)

3. 93.729 31,286.829 ↑ 233,584.8 325 1

Sort (cost=56,806,584.94..56,996,372.57 rows=75,915,053 width=678) (actual time=31,286.736..31,286.829 rows=325 loops=1)

  • Sort Key: shiftoccurrence.start_date, shiftoccurrence.end_date, s.shift_uuid, (lower(s.valid_range)) DESC
  • Sort Method: external merge Disk: 5880kB
4. 36.736 31,193.100 ↑ 7,429.5 10,218 1

Nested Loop Left Join (cost=1,403.58..2,833,445.70 rows=75,915,053 width=678) (actual time=32.296..31,193.100 rows=10,218 loops=1)

5. 70.793 31,074.620 ↑ 7.4 10,218 1

Nested Loop (cost=1,403.16..1,441,189.85 rows=75,804 width=591) (actual time=32.265..31,074.620 rows=10,218 loops=1)

6. 8.592 114.813 ↑ 2.1 10,218 1

Nested Loop (cost=1,376.30..61,441.25 rows=21,655 width=285) (actual time=25.676..114.813 rows=10,218 loops=1)

7. 0.013 24.924 ↑ 34.7 3 1

Unique (cost=1,367.68..1,368.20 rows=104 width=221) (actual time=24.911..24.924 rows=3 loops=1)

8. 0.033 24.911 ↑ 34.7 3 1

Sort (cost=1,367.68..1,367.94 rows=104 width=221) (actual time=24.909..24.911 rows=3 loops=1)

  • Sort Key: g.revision_uuid
  • Sort Method: quicksort Memory: 25kB
9. 24.878 24.878 ↑ 34.7 3 1

Seq Scan on groups_org_part_33 g (cost=0.00..1,364.20 rows=104 width=221) (actual time=10.062..24.878 rows=3 loops=1)

  • Filter: (is_visible AND (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid) AND (lower((target_name)::text) = 'cpcsrt'::text) AND ((valid_range @> '2019-01-01 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 <@ '[""2019-01-01 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Rows Removed by Filter: 25905
10. 80.112 81.297 ↓ 486.6 3,406 3

Bitmap Heap Scan on shift_occurrence_org_part_7 shiftoccurrence (cost=8.62..577.55 rows=7 width=80) (actual time=0.533..27.099 rows=3,406 loops=3)

  • Recheck Cond: (group_uuid = g.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) @> '2019-01-01 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) <@ '[""2019-01-01 16:30:00+00"",""2020-05-22 06:28:00+00""]'::tstzrange)))
  • Heap Blocks: exact=3444
11. 1.185 1.185 ↓ 6.9 3,406 3

Bitmap Index Scan on shift_occurrence_org_part_7_group_uuid_idx (cost=0.00..8.61 rows=497 width=0) (actual time=0.395..0.395 rows=3,406 loops=3)

  • Index Cond: (group_uuid = g.group_uuid)
12. 122.616 30,889.014 ↑ 1.0 1 10,218

Bitmap Heap Scan on shifts_org_part_6 s (cost=26.86..63.71 rows=1 width=322) (actual time=3.022..3.023 rows=1 loops=10,218)

  • 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=20430
13. 214.578 30,439.422 ↓ 0.0 0 10,218

BitmapAnd (cost=26.86..26.86 rows=1 width=0) (actual time=2.979..2.979 rows=0 loops=10,218)

14. 132.834 132.834 ↓ 4.4 92 10,218

Bitmap Index Scan on shifts_org_part_6_shift_uuid_idx (cost=0.00..0.62 rows=21 width=0) (actual time=0.013..0.013 rows=92 loops=10,218)

  • Index Cond: (shift_uuid = shiftoccurrence.shift_uuid)
15. 1,461.174 30,092.010 ↓ 0.0 0 10,218

BitmapOr (cost=25.99..25.99 rows=1,002 width=0) (actual time=2.945..2.945 rows=0 loops=10,218)

16. 28,569.528 28,569.528 ↓ 10.8 10,791 10,218

Bitmap Index Scan on shifts_org_part_6_valid_range_idx (cost=0.00..7.89 rows=1,001 width=0) (actual time=2.796..2.796 rows=10,791 loops=10,218)

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

Bitmap Index Scan on shifts_org_part_6_revision_uuid_uidx (cost=0.00..18.10 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=10,218)

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

SubPlan (for Bitmap Index Scan)

19. 20.448 1,380.240 ↑ 1.0 1 10,224

Limit (cost=17.63..17.64 rows=1 width=37) (actual time=0.135..0.135 rows=1 loops=10,224)

20. 306.720 1,359.792 ↑ 21.0 1 10,224

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

  • Sort Key: shifts_org_part_6_1.valid_range
  • Sort Method: top-N heapsort Memory: 25kB
21. 1,053.072 1,053.072 ↓ 4.4 92 10,224

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.013..0.103 rows=92 loops=10,224)

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

SubPlan (for Bitmap Heap Scan)

23. 326.976 326.976 ↑ 1.0 1 10,218

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.032..0.032 rows=1 loops=10,218)

  • 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: 36
24. 81.744 81.744 ↓ 0.0 0 10,218

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=10,218)

  • Index Cond: (revision_uuid = shiftoccurrence.shift_revision_uuid)
  • Filter: (organization_uuid = 'fcb0b139-ed68-4220-a7a6-7e56e8214519'::uuid)
Planning time : 5.971 ms
Execution time : 31,290.080 ms