explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AqGr

Settings
# exclusive inclusive rows x rows loops node
1. 6,493.744 4,220,537.729 ↓ 0.0 0 1

Update on report_on_call_tmp r (cost=765.67..10,125.54 rows=1 width=1,358) (actual time=4,220,537.728..4,220,537.729 rows=0 loops=1)

  • PL/pgSQL function auditstore_report.xmreport_on_call_generate(uuid,uuid) line 830 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) line 95 at CALL
2. 1,677.788 4,214,043.985 ↓ 6,315,261.0 6,315,261 1

Nested Loop (cost=765.67..10,125.54 rows=1 width=1,358) (actual time=456.669..4,214,043.985 rows=6,315,261 loops=1)

3. 343.461 2,580.281 ↓ 65,171.0 65,171 1

Subquery Scan on t (cost=765.24..765.27 rows=1 width=776) (actual time=426.520..2,580.281 rows=65,171 loops=1)

4. 1,870.529 2,236.820 ↓ 65,171.0 65,171 1

WindowAgg (cost=765.24..765.26 rows=1 width=672) (actual time=426.505..2,236.820 rows=65,171 loops=1)

5. 106.858 366.291 ↓ 65,171.0 65,171 1

Sort (cost=765.24..765.24 rows=1 width=664) (actual time=345.562..366.291 rows=65,171 loops=1)

  • Sort Key: (sum(r_1.delay_time_min) OVER (?))
  • Sort Method: external merge Disk: 25312kB
6. 43.351 259.433 ↓ 65,171.0 65,171 1

WindowAgg (cost=765.20..765.22 rows=1 width=664) (actual time=192.635..259.433 rows=65,171 loops=1)

7. 119.620 216.082 ↓ 65,171.0 65,171 1

Sort (cost=765.20..765.20 rows=1 width=656) (actual time=192.616..216.082 rows=65,171 loops=1)

  • Sort Key: r_1."position
  • Sort Method: external merge Disk: 24696kB
8. 96.462 96.462 ↓ 65,171.0 65,171 1

Index Scan using report_on_call_tmp_organization_uuid_idx on report_on_call_tmp r_1 (cost=0.43..765.19 rows=1 width=656) (actual time=0.028..96.462 rows=65,171 loops=1)

  • Index Cond: (organization_uuid = '45b10f8a-f093-4589-bb89-dd367ce1c1d0'::uuid)
  • Filter: ((start_date = segment_start) AND (shift_occurrence_uuid = 'f817b330-1fa0-4e88-acc9-b1808dafe0e3'::uuid))
  • Rows Removed by Filter: 603
9. 4,209,785.916 4,209,785.916 ↓ 97.0 97 65,171

Index Scan using report_on_call_tmp_organization_uuid_idx on report_on_call_tmp r (cost=0.43..9,360.26 rows=1 width=654) (actual time=35.182..64.596 rows=97 loops=65,171)

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