explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZVnl

Settings
# exclusive inclusive rows x rows loops node
1. 874.226 1,890.010 ↑ 1.0 1 1

Nested Loop Left Join (cost=8.88..47.26 rows=1 width=194) (actual time=1,889.963..1,890.010 rows=1 loops=1)

  • Output: p.id, (SubPlan 1), i.name, i.surname, i.patr_name, (SubPlan 2), g.id, g.name, g.code, i.birth_dt, i.death_dt, get_min_priority_oms_doc(i.id), get_min_priority_identy_doc(i.id), get_job_org_name(i.id), sg.id, sg.name, (SubPlan 3)
  • Join Filter: (i.gender_id = g.id)
  • Rows Removed by Join Filter: 4
  • Buffers: shared hit=30 read=19
  • I/O Timings: read=1888.003
2. 0.013 444.580 ↑ 1.0 1 1

Nested Loop (cost=8.88..19.29 rows=1 width=173) (actual time=444.544..444.580 rows=1 loops=1)

  • Output: p.id, i.name, i.surname, i.patr_name, i.birth_dt, i.death_dt, i.id, i.gender_id, sg.id, sg.name
  • Buffers: shared hit=5 read=5
  • I/O Timings: read=444.245
3. 0.038 330.042 ↑ 1.0 1 1

Hash Right Join (cost=8.46..10.84 rows=1 width=104) (actual time=330.011..330.042 rows=1 loops=1)

  • Output: p.id, sg.id, sg.name
  • Hash Cond: (sg.id = p.social_group_id)
  • Buffers: shared hit=3 read=3
  • I/O Timings: read=329.839
4. 0.023 0.023 ↑ 1.0 27 1

Seq Scan on public.md_soc_group sg (cost=0.00..2.27 rows=27 width=100) (actual time=0.010..0.023 rows=27 loops=1)

  • Output: sg.id, sg.code, sg.from_age, sg.from_dt, sg.name, sg.to_age, sg.to_dt, sg.e_code, sg.parent_id, sg.aud_who, sg.aud_when, sg.aud_source, sg.aud_who_create, sg.aud_when_create, sg.aud_source_create, sg.billing_code
  • Buffers: shared hit=2
5. 0.010 329.981 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=8) (actual time=329.981..329.981 rows=1 loops=1)

  • Output: p.id, p.social_group_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=329.839
6. 329.971 329.971 ↑ 1.0 1 1

Index Scan using pci_patient_pk on public.pci_patient p (cost=0.43..8.45 rows=1 width=8) (actual time=329.967..329.971 rows=1 loops=1)

  • Output: p.id, p.social_group_id
  • Index Cond: (p.id = 390061)
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=329.839
7. 114.525 114.525 ↑ 1.0 1 1

Index Scan using pim_individual_pk on public.pim_individual i (cost=0.43..8.45 rows=1 width=69) (actual time=114.521..114.525 rows=1 loops=1)

  • Output: i.id, i.birth_dt, i.death_dt, i.has_citizenship, i.name, i.patr_name, i.surname, i.gender_id, i.nationality_id, i.list_identity_doc, i.list_oms_doc, i.list_job_org, i.list_reg_name, i.list_snils, i.list_uid, i.aud_who, i.aud_when, i.aud_source, i.aud_who_create, i.aud_when_create, i.aud_source_create, i.birth_place, i.age_year, i.age_month, i.age_day, i.list_main_contact, i.is_only_birth_year
  • Index Cond: (i.id = 390061)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=114.406
8. 0.016 0.016 ↑ 1.0 5 1

Seq Scan on public.pim_gender g (cost=0.00..1.05 rows=5 width=25) (actual time=0.011..0.016 rows=5 loops=1)

  • Output: g.id, g.code, g.name, g.scope_id, g.e_code, g.aud_who, g.aud_when, g.aud_source, g.aud_who_create, g.aud_when_create, g.aud_source_create, g.from_dt, g.to_dt
  • Buffers: shared hit=1
9.          

SubPlan (for Nested Loop Left Join)

10. 167.619 167.619 ↑ 1.0 1 1

Index Scan using pim_party_pk on public.pim_party (cost=0.43..8.45 rows=1 width=8) (actual time=167.614..167.619 rows=1 loops=1)

  • Output: pim_party.version
  • Index Cond: (pim_party.id = p.id)
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=167.501
11. 0.011 284.118 ↑ 1.0 1 1

Limit (cost=0.43..17.10 rows=1 width=15) (actual time=284.117..284.118 rows=1 loops=1)

  • Output: pim_indiv_code.code
  • Buffers: shared hit=3 read=3
  • I/O Timings: read=283.943
12. 0.022 284.107 ↑ 1.0 1 1

Nested Loop (cost=0.43..17.10 rows=1 width=15) (actual time=284.107..284.107 rows=1 loops=1)

  • Output: pim_indiv_code.code
  • Join Filter: (pim_indiv_code.type_id = pim_code_type.id)
  • Rows Removed by Join Filter: 1
  • Buffers: shared hit=3 read=3
  • I/O Timings: read=283.943
13. 0.021 0.021 ↑ 1.0 1 1

Seq Scan on public.pim_code_type (cost=0.00..1.38 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)

  • Output: pim_code_type.id, pim_code_type.code, pim_code_type.format, pim_code_type.from_dt, pim_code_type.is_unique, pim_code_type.message_pattern, pim_code_type.name, pim_code_type.note, pim_code_type.occurrence, pim_code_type.to_dt, pim_code_type.party_type_id, pim_code_type.e_code, pim_code_type.aud_who, pim_code_type.aud_when, pim_code_type.aud_source, pim_code_type.aud_who_create, pim_code_type.aud_when_create, pim_code_type.aud_source_create
  • Filter: ((pim_code_type.code)::text = 'UID'::text)
  • Rows Removed by Filter: 7
  • Buffers: shared hit=1
14. 284.064 284.064 ↑ 1.5 2 1

Index Scan using pim_indiv_code_indiv_id_idx on public.pim_indiv_code (cost=0.43..15.69 rows=3 width=19) (actual time=249.505..284.064 rows=2 loops=1)

  • Output: pim_indiv_code.id, pim_indiv_code.code, pim_indiv_code.issue_dt, pim_indiv_code.type_id, pim_indiv_code.indiv_id, pim_indiv_code.sha256, pim_indiv_code.aud_who, pim_indiv_code.aud_when, pim_indiv_code.aud_source, pim_indiv_code.aud_who_create, pim_indiv_code.aud_when_create, pim_indiv_code.aud_source_create
  • Index Cond: (pim_indiv_code.indiv_id = p.id)
  • Buffers: shared hit=2 read=3
  • I/O Timings: read=283.943
15. 0.010 119.451 ↑ 1.0 1 1

Limit (cost=0.44..0.56 rows=1 width=4) (actual time=119.451..119.451 rows=1 loops=1)

  • Output: mc_step.admission_date
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=119.362
16. 119.441 119.441 ↑ 78.0 1 1

Index Only Scan using mc_step_patient_id_admission_date_desc_ix on public.mc_step (cost=0.44..9.80 rows=78 width=4) (actual time=119.441..119.441 rows=1 loops=1)

  • Output: mc_step.admission_date
  • Index Cond: (mc_step._patient_id = p.id)
  • Heap Fetches: 0
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=119.362
Planning time : 1.733 ms
Execution time : 1,890.223 ms