explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UWJq

Settings
# exclusive inclusive rows x rows loops node
1. 12,156.736 9,752,145.870 ↓ 0.0 0 1

Update on report_on_call_tmp r (cost=86,680.98..174,239.86 rows=1 width=1,720) (actual time=9,752,145.870..9,752,145.870 rows=0 loops=1)

  • Update on report_on_call_tmp_org_part_default r_1
  • PL/pgSQL function auditstore_report.xmreport_on_call_generate(uuid,uuid[],boolean) line 1046 at SQL statement
  • SQL statement "CALL auditstore_report.xmreport_on_call_generate(r_sho.organization_uuid,
  • PL/pgSQL function auditstore_report.xmreport_on_call_generate_report_for_org(uuid,date,date,boolean) line 131 at CALL
2. 960.164 9,739,989.134 ↓ 113,909.0 113,909 1

Nested Loop (cost=86,680.98..174,239.86 rows=1 width=1,720) (actual time=1,295.598..9,739,989.134 rows=113,909 loops=1)

3. 687.676 5,277.879 ↓ 748.5 62,871 1

Subquery Scan on t (cost=86,680.42..86,682.73 rows=84 width=941) (actual time=1,248.188..5,277.879 rows=62,871 loops=1)

4. 3,528.357 4,590.203 ↓ 748.5 62,871 1

WindowAgg (cost=86,680.42..86,681.89 rows=84 width=853) (actual time=1,248.167..4,590.203 rows=62,871 loops=1)

5. 299.342 1,061.846 ↓ 748.5 62,871 1

Sort (cost=86,680.42..86,680.63 rows=84 width=845) (actual time=997.461..1,061.846 rows=62,871 loops=1)

  • Sort Key: (sum(r_2.delay_time_min) OVER (?))
  • Sort Method: external merge Disk: 66112kB
6. 86.935 762.504 ↓ 748.5 62,871 1

WindowAgg (cost=86,675.43..86,676.90 rows=84 width=845) (actual time=588.933..762.504 rows=62,871 loops=1)

7. 429.124 675.569 ↓ 748.5 62,871 1

Sort (cost=86,675.43..86,675.64 rows=84 width=837) (actual time=588.904..675.569 rows=62,871 loops=1)

  • Sort Key: r_2."position
  • Sort Method: external merge Disk: 65448kB
8. 225.292 246.445 ↓ 748.5 62,871 1

Bitmap Heap Scan on report_on_call_tmp_org_part_default r_2 (cost=1,603.91..86,672.74 rows=84 width=837) (actual time=25.351..246.445 rows=62,871 loops=1)

  • Recheck Cond: (shift_occurrence_uuid = '547ca891-b649-44d4-b7ea-37414d74c4bf'::uuid)
  • Filter: ((organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid) AND (start_date = segment_start))
  • Heap Blocks: exact=11185
9. 21.153 21.153 ↓ 2.0 125,742 1

Bitmap Index Scan on report_on_call_tmp_org_part_default_shift_occurrence_uuid_idx (cost=0.00..1,603.88 rows=62,577 width=0) (actual time=21.153..21.153 rows=125,742 loops=1)

  • Index Cond: (shift_occurrence_uuid = '547ca891-b649-44d4-b7ea-37414d74c4bf'::uuid)
10. 9,733,751.091 9,733,751.091 ↓ 2.0 2 62,871

Index Scan using report_on_call_tmp_org_part_default_shift_occurrence_uuid_idx on report_on_call_tmp_org_part_default r_1 (cost=0.56..1,042.34 rows=1 width=835) (actual time=68.227..154.821 rows=2 loops=62,871)

  • Index Cond: (shift_occurrence_uuid = t.shift_occurrence_uuid)
  • Filter: (('f3185a16-1604-4839-ba8b-c251a95023d2'::uuid = organization_uuid) AND ((t.participant_uuid = participant_uuid) OR (t.replacement_user_uuid = participant_uuid)))
  • Rows Removed by Filter: 62869
  • set escalation_position = t.escalation_position_calc,
  • escalation_time_min = t.escalation_time
  • from (select dense_rank() OVER (ORDER BY t.escalation_time) AS escalation_position_calc,
  • t.*
  • from (select sum(delay_time_min)
  • over ( order by position
  • rows between unbounded preceding and current row) as escalation_time,
  • r.*
  • from auditstore_report.report_on_call_tmp as r
  • where organization_uuid = p_organization_uuid
  • --- calculate at the start_date
  • and start_date = segment_start
  • and shift_occurrence_uuid = r_sho.shift_occurrence_uuid) as t) as t
  • where p_organization_uuid = r.organization_uuid
  • and t.shift_occurrence_uuid = r.shift_occurrence_uuid
  • and (t.participant_uuid = r.participant_uuid
  • or t.replacement_user_uuid = r.participant_uuid)
  • v_batch,
  • p_debug)