explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rUgr

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 44,913.803 ↓ 3.0 3 1

Limit (cost=1,392,368.75..1,392,368.75 rows=1 width=718) (actual time=44,913.802..44,913.803 rows=3 loops=1)

  • Output: audit.aud_rec, (((audit.delta -> 'cancel_reason_id'::text) IS NOT NULL)), app.id, audit.type, audit.aud_when, service.name, (((audit.aud_who)::text = 'system'::text)), service.id, ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 9))
  • Buffers: shared hit=2,321,212 read=2,448,270
  • I/O Timings: read=20,219.475
2. 0.275 44,913.799 ↓ 3.0 3 1

Sort (cost=1,392,368.75..1,392,368.75 rows=1 width=718) (actual time=44,913.799..44,913.799 rows=3 loops=1)

  • Output: audit.aud_rec, (((audit.delta -> 'cancel_reason_id'::text) IS NOT NULL)), app.id, audit.type, audit.aud_when, service.name, (((audit.aud_who)::text = 'system'::text)), service.id, ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 9))
  • Sort Key: audit.aud_when DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=2,321,212 read=2,448,270
  • I/O Timings: read=20,219.475
3. 0.012 44,913.524 ↓ 3.0 3 1

Unique (cost=1,392,368.72..1,392,368.73 rows=1 width=183) (actual time=44,913.512..44,913.524 rows=3 loops=1)

  • Output: audit.aud_rec, (((audit.delta -> 'cancel_reason_id'::text) IS NOT NULL)), app.id, audit.type, audit.aud_when, service.name, (((audit.aud_who)::text = 'system'::text)), service.id, ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 9))
  • Buffers: shared hit=2,321,209 read=2,448,270
  • I/O Timings: read=20,219.475
4. 0.143 44,913.512 ↓ 6.0 6 1

Sort (cost=1,392,368.72..1,392,368.73 rows=1 width=183) (actual time=44,913.510..44,913.512 rows=6 loops=1)

  • Output: audit.aud_rec, (((audit.delta -> 'cancel_reason_id'::text) IS NOT NULL)), app.id, audit.type, audit.aud_when, service.name, (((audit.aud_who)::text = 'system'::text)), service.id, ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 9))
  • Sort Key: audit.aud_rec
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=2,321,209 read=2,448,270
  • I/O Timings: read=20,219.475
5. 37,217.564 44,913.369 ↓ 6.0 6 1

Nested Loop (cost=11.39..1,392,368.71 rows=1 width=183) (actual time=13,846.573..44,913.369 rows=6 loops=1)

  • Output: audit.aud_rec, ((audit.delta -> 'cancel_reason_id'::text) IS NOT NULL), app.id, audit.type, audit.aud_when, service.name, ((audit.aud_who)::text = 'system'::text), service.id, (SubPlan 4), (SubPlan 5), (SubPlan 9)
  • Buffers: shared hit=2,321,206 read=2,448,270
  • I/O Timings: read=20,219.475
6. 0.046 7,670.545 ↓ 6.0 6 1

Nested Loop (cost=10.97..435,037.99 rows=1 width=73) (actual time=5,217.641..7,670.545 rows=6 loops=1)

  • Output: audit.aud_rec, audit.delta, audit.type, audit.aud_when, audit.aud_who, audit.id, app.id, app.service_id
  • Buffers: shared hit=178,493 read=188,326
  • I/O Timings: read=1,541.334
7. 0.019 7,670.415 ↓ 6.0 6 1

Nested Loop (cost=10.53..435,037.43 rows=1 width=73) (actual time=5,217.617..7,670.415 rows=6 loops=1)

  • Output: audit.aud_rec, audit.delta, audit.type, audit.aud_when, audit.aud_who, audit.id, st.id, sst.ticket_id
  • Buffers: shared hit=178,469 read=188,326
  • I/O Timings: read=1,541.334
8. 0.053 7,670.042 ↓ 6.0 6 1

Nested Loop (cost=9.96..435,028.83 rows=1 width=77) (actual time=5,217.354..7,670.042 rows=6 loops=1)

  • Output: audit.aud_rec, audit.delta, audit.type, audit.aud_when, audit.aud_who, audit.id, st.id, sst.ticket_id, sst.session_id
  • Buffers: shared hit=178,438 read=188,326
  • I/O Timings: read=1,541.334
9. 910.980 7,669.821 ↑ 9.5 6 1

Hash Join (cost=9.53..435,000.21 rows=57 width=73) (actual time=5,217.252..7,669.821 rows=6 loops=1)

  • Output: audit.aud_rec, audit.delta, audit.type, audit.aud_when, audit.aud_who, audit.id, sst.ticket_id, sst.session_id
  • Hash Cond: (audit.id = sst.ticket_id)
  • Buffers: shared hit=178,413 read=188,326
  • I/O Timings: read=1,541.334
10. 6,758.659 6,758.659 ↑ 1.0 4,804,757 1

Seq Scan on audit."public$md_appointment" audit (cost=0.00..416,374.81 rows=4,964,081 width=65) (actual time=0.071..6,758.659 rows=4,804,757 loops=1)

  • Output: audit.aud_rec, audit.id, audit.type, audit.delta_deprecated, audit.aud_when, audit.aud_who, audit.aud_source, audit.data, audit.delta
  • Buffers: shared hit=178,408 read=188,326
  • I/O Timings: read=1,541.334
11. 0.024 0.182 ↑ 1.0 2 1

Hash (cost=9.50..9.50 rows=2 width=8) (actual time=0.182..0.182 rows=2 loops=1)

  • Output: sst.ticket_id, sst.session_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
12. 0.084 0.158 ↑ 1.0 2 1

Bitmap Heap Scan on audit."public$sr_session_ticket" sst (cost=4.16..9.50 rows=2 width=8) (actual time=0.154..0.158 rows=2 loops=1)

  • Output: sst.ticket_id, sst.session_id
  • Recheck Cond: (sst.session_id = 225,291,510)
  • Heap Blocks: exact=1
  • Buffers: shared hit=2
13. 0.074 0.074 ↑ 1.0 2 1

Bitmap Index Scan on "public$sr_session_ticket_session_id_ticket_id_idx" (cost=0.00..4.16 rows=2 width=0) (actual time=0.074..0.074 rows=2 loops=1)

  • Index Cond: (sst.session_id = 225,291,510)
  • Buffers: shared hit=1
14. 0.168 0.168 ↑ 1.0 1 6

Index Only Scan using sr_ticket_pk on public.sr_ticket st (cost=0.43..0.49 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=6)

  • Output: st.id
  • Index Cond: (st.id = audit.id)
  • Heap Fetches: 6
  • Buffers: shared hit=25
15. 0.354 0.354 ↑ 1.0 1 6

Index Only Scan using sr_session_pk on public.sr_session session (cost=0.57..8.59 rows=1 width=4) (actual time=0.056..0.059 rows=1 loops=6)

  • Output: session.id
  • Index Cond: (session.id = 225,291,510)
  • Heap Fetches: 6
  • Buffers: shared hit=31
16. 0.084 0.084 ↑ 1.0 1 6

Index Scan using md_appointment_pk on public.md_appointment app (cost=0.43..0.56 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=6)

  • Output: app.id, app.srv_rendered_id, app.executor_id, app.bdatetime, app.customer_id, app.funding_id, app.state_id, app.service_id, app.cancel_reason_id, app.care_regimen_id, app.care_level_id, app.referral_id, app.sou (...)
  • Index Cond: (app.id = st.id)
  • Buffers: shared hit=24
17. 0.156 0.156 ↑ 1.0 1 6

Index Scan using sr_service_pk on public.sr_service service (cost=0.43..0.50 rows=1 width=114) (actual time=0.023..0.026 rows=1 loops=6)

  • Output: service.id, service.code, service.cul, service.is_death, service.duration, service.is_fictitious, service.is_independent, service.is_multuplicity, service.name, service.terms, service.org_id, service.category_id, ser (...)
  • Index Cond: (service.id = app.service_id)
  • Buffers: shared hit=24
18.          

SubPlan (for Nested Loop)

19. 0.000 0.468 ↑ 1.0 1 6

Index Scan using pim_individual_pk on public.pim_individual (cost=478,434.86..478,442.88 rows=1 width=45) (actual time=0.076..0.078 rows=1 loops=6)

  • Output: concat_ws(' '::text, pim_individual.surname, pim_individual.name, pim_individual.patr_name)
  • Index Cond: (pim_individual.id = $4)
  • Buffers: shared hit=24
20.          

Initplan (for Index Scan)

21. 0.048 20,153.232 ↑ 1.0 1 6

Limit (cost=478,434.43..478,434.43 rows=1 width=40) (actual time=3,358.871..3,358.872 rows=1 loops=6)

  • Output: (((appointment.delta -> 'customer_id'::text))::integer), appointment.aud_when
  • Buffers: shared hit=1,070,416 read=1,129,988
  • I/O Timings: read=10,634.530
22.          

CTE audit_rec

23. 0.000 0.000 ↓ 0.0 0

Index Scan using "public$md_appointment_pk" on audit."public$md_appointment" (cost=0.43..8.45 rows=1 width=572) (never executed)

  • Output: "public$md_appointment".aud_rec, "public$md_appointment".id, "public$md_appointment".type, "public$md_appointment".delta_deprecated, "public$md_appointment".aud_when, "public$md_appointment".aud_who (...)
  • Index Cond: ("public$md_appointment".aud_rec = audit.aud_rec)
24.          

Initplan (for Limit)

25. 0.000 0.000 ↓ 0.0 0

CTE Scan on audit_rec (cost=0.00..0.02 rows=1 width=8) (never executed)

  • Output: audit_rec.aud_when
26. 0.264 20,153.184 ↑ 11.0 1 6

Sort (cost=478,425.96..478,425.99 rows=11 width=40) (actual time=3,358.864..3,358.864 rows=1 loops=6)

  • Output: (((appointment.delta -> 'customer_id'::text))::integer), appointment.aud_when
  • Sort Key: appointment.aud_when
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,070,416 read=1,129,988
  • I/O Timings: read=10,634.530
27. 20,152.920 20,152.920 ↑ 11.0 1 6

Seq Scan on audit."public$md_appointment" appointment (cost=0.00..478,425.91 rows=11 width=40) (actual time=623.567..3,358.820 rows=1 loops=6)

  • Output: ((appointment.delta -> 'customer_id'::text))::integer, appointment.aud_when
  • Filter: (((appointment.delta -> 'customer_id'::text) IS NOT NULL) AND (appointment.id = audit.id) AND (((appointment.type = 'U'::text) AND (appointment.aud_when < $2)) OR (appointment.type = 'I'::text)) (...)
  • Rows Removed by Filter: 4,804,756
  • Buffers: shared hit=1,070,416 read=1,129,988
  • I/O Timings: read=10,634.530
28. 0.114 24.462 ↑ 1.0 1 6

Nested Loop (cost=1.15..444.59 rows=1 width=45) (actual time=1.560..4.077 rows=1 loops=6)

  • Output: concat_ws(' '::text, pi.surname, pi.name, pi.patr_name)
  • Buffers: shared hit=1,783
29. 0.084 24.174 ↑ 1.0 1 6

Nested Loop (cost=0.72..443.85 rows=1 width=8) (actual time=1.514..4.029 rows=1 loops=6)

  • Output: pp.id, sup.party_id
  • Buffers: shared hit=1,759
30. 0.138 23.862 ↑ 1.0 1 6

Nested Loop (cost=0.29..439.93 rows=1 width=4) (actual time=1.463..3.977 rows=1 loops=6)

  • Output: sup.party_id
  • Buffers: shared hit=1,740
31. 23.652 23.652 ↑ 1.0 1 6

Seq Scan on public.sec_user sr (cost=0.00..431.61 rows=1 width=4) (actual time=1.429..3.942 rows=1 loops=6)

  • Output: sr.id, sr.close_dt, sr.comment, sr.cr_dt, sr.email, sr.login, sr.password, sr.version, sr.blocked, sr.scope_id, sr.use_global_context, sr.aud_who, sr.aud_when, sr.aud_source, sr.aud_who_create, sr (...)
  • Filter: ((sr.login)::text = (audit.aud_who)::text)
  • Rows Removed by Filter: 11,583
  • Buffers: shared hit=1,722
32. 0.072 0.072 ↑ 1.0 1 6

Index Scan using pk_sec_user_party on public.sec_user_party sup (cost=0.29..8.30 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=6)

  • Output: sup.id, sup.party_id, sup.aud_who, sup.aud_when, sup.aud_source, sup.aud_who_create, sup.aud_when_create, sup.aud_source_create
  • Index Cond: (sup.id = sr.id)
  • Buffers: shared hit=18
33. 0.228 0.228 ↑ 1.0 1 6

Index Only Scan using pim_party_pk on public.pim_party pp (cost=0.43..3.91 rows=1 width=4) (actual time=0.037..0.038 rows=1 loops=6)

  • Output: pp.id
  • Index Cond: (pp.id = sup.party_id)
  • Heap Fetches: 0
  • Buffers: shared hit=19
34. 0.174 0.174 ↑ 1.0 1 6

Index Scan using pim_individual_pk on public.pim_individual pi (cost=0.43..0.73 rows=1 width=49) (actual time=0.028..0.029 rows=1 loops=6)

  • Output: pi.id, pi.birth_dt, pi.death_dt, pi.has_citizenship, pi.name, pi.patr_name, pi.surname, pi.gender_id, pi.nationality_id, pi.list_identity_doc, pi.list_oms_doc, pi.list_job_org, pi.list_reg_name, pi.list_snils (...)
  • Index Cond: (pi.id = pp.id)
  • Buffers: shared hit=24
35. 0.000 0.174 ↑ 1.0 1 6

Index Scan using pim_organization_pk on public.pim_organization (cost=478,434.72..478,442.74 rows=1 width=44) (actual time=0.028..0.029 rows=1 loops=6)

  • Output: pim_organization.short_name
  • Index Cond: (pim_organization.id = $13)
  • Buffers: shared hit=18
36.          

Initplan (for Index Scan)

37. 0.066 17,062.002 ↑ 1.0 1 6

Limit (cost=478,434.43..478,434.43 rows=1 width=40) (actual time=2,843.666..2,843.667 rows=1 loops=6)

  • Output: (((appointment_1.delta -> 'organization_id'::text))::integer), appointment_1.aud_when
  • Buffers: shared hit=1,070,448 read=1,129,956
  • I/O Timings: read=8,043.611
38.          

CTE audit_rec

39. 0.000 0.000 ↓ 0.0 0

Index Scan using "public$md_appointment_pk" on audit."public$md_appointment" "public$md_appointment_1" (cost=0.43..8.45 rows=1 width=572) (never executed)

  • Output: "public$md_appointment_1".aud_rec, "public$md_appointment_1".id, "public$md_appointment_1".type, "public$md_appointment_1".delta_deprecated, "public$md_appointment_1".aud_when, "public$md_appointmen (...)
  • Index Cond: ("public$md_appointment_1".aud_rec = audit.aud_rec)
40.          

Initplan (for Limit)

41. 0.000 0.000 ↓ 0.0 0

CTE Scan on audit_rec audit_rec_1 (cost=0.00..0.02 rows=1 width=8) (never executed)

  • Output: audit_rec_1.aud_when
42. 0.282 17,061.936 ↑ 11.0 1 6

Sort (cost=478,425.96..478,425.99 rows=11 width=40) (actual time=2,843.656..2,843.656 rows=1 loops=6)

  • Output: (((appointment_1.delta -> 'organization_id'::text))::integer), appointment_1.aud_when
  • Sort Key: appointment_1.aud_when
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,070,448 read=1,129,956
  • I/O Timings: read=8,043.611
43. 17,061.654 17,061.654 ↑ 11.0 1 6

Seq Scan on audit."public$md_appointment" appointment_1 (cost=0.00..478,425.91 rows=11 width=40) (actual time=635.222..2,843.609 rows=1 loops=6)

  • Output: ((appointment_1.delta -> 'organization_id'::text))::integer, appointment_1.aud_when
  • Filter: (((appointment_1.delta -> 'organization_id'::text) IS NOT NULL) AND (appointment_1.id = audit.id) AND (((appointment_1.type = 'U'::text) AND (appointment_1.aud_when < $11)) OR (appointment_1.typ (...)
  • Rows Removed by Filter: 4,804,756
  • Buffers: shared hit=1,070,448 read=1,129,956
  • I/O Timings: read=8,043.611
Planning time : 7.504 ms
Execution time : 44,915.301 ms