explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4yfh

Settings
# exclusive inclusive rows x rows loops node
1. 5,574.090 3,356,532.226 ↓ 0.0 0 1

Update on report_on_call_tmp r (cost=3.93..7.00 rows=1 width=1,360) (actual time=3,356,532.225..3,356,532.226 rows=0 loops=1)

  • PL/pgSQL function auditstore_report.xmreport_on_call_generate(uuid,uuid[],boolean) line 1040 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 115 at CALL
2. 522.089 3,350,958.136 ↓ 52,743.0 52,743 1

Nested Loop (cost=3.93..7.00 rows=1 width=1,360) (actual time=793.340..3,350,958.136 rows=52,743 loops=1)

3. 416.789 3,154.295 ↓ 52,743.0 52,743 1

Subquery Scan on t (cost=3.50..3.53 rows=1 width=777) (actual time=760.007..3,154.295 rows=52,743 loops=1)

4. 2,150.774 2,737.506 ↓ 52,743.0 52,743 1

WindowAgg (cost=3.50..3.52 rows=1 width=673) (actual time=759.988..2,737.506 rows=52,743 loops=1)

5. 230.800 586.732 ↓ 52,743.0 52,743 1

Sort (cost=3.50..3.51 rows=1 width=665) (actual time=553.156..586.732 rows=52,743 loops=1)

  • Sort Key: (sum(r_1.delay_time_min) OVER (?))
  • Sort Method: external merge Disk: 55680kB
6. 41.345 355.932 ↓ 52,743.0 52,743 1

WindowAgg (cost=3.46..3.48 rows=1 width=665) (actual time=269.877..355.932 rows=52,743 loops=1)

7. 258.122 314.587 ↓ 52,743.0 52,743 1

Sort (cost=3.46..3.47 rows=1 width=657) (actual time=269.857..314.587 rows=52,743 loops=1)

  • Sort Key: r_1."position
  • Sort Method: external merge Disk: 55120kB
8. 56.465 56.465 ↓ 52,743.0 52,743 1

Index Scan using report_on_call_org_sh_occ_idx on report_on_call_tmp r_1 (cost=0.43..3.45 rows=1 width=657) (actual time=0.037..56.465 rows=52,743 loops=1)

  • Index Cond: ((organization_uuid = $27) AND (shift_occurrence_uuid = $37))
  • Filter: (start_date = segment_start)
9. 3,347,281.752 3,347,281.752 ↑ 1.0 1 52,743

Index Scan using report_on_call_org_sh_occ_idx on report_on_call_tmp r (cost=0.43..3.46 rows=1 width=655) (actual time=38.911..63.464 rows=1 loops=52,743)

  • Index Cond: ((organization_uuid = t.organization_uuid) AND (shift_occurrence_uuid = t.shift_occurrence_uuid))
  • Filter: ((t.participant_uuid = participant_uuid) OR (t.replacement_user_uuid = participant_uuid))
  • Rows Removed by Filter: 52742
  • 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 = r_sho.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 t.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)