explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nSbm

Settings
# exclusive inclusive rows x rows loops node
1. 166.963 338.958 ↑ 1.0 1 1

Nested Loop Left Join (cost=8.88..47.26 rows=1 width=194) (actual time=338.909..338.958 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=1348 read=17
  • I/O Timings: read=319.643
2. 0.009 37.243 ↑ 1.0 1 1

Nested Loop (cost=8.88..19.29 rows=1 width=173) (actual time=37.204..37.243 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=6 read=4
  • I/O Timings: read=36.952
3. 0.035 12.457 ↑ 1.0 1 1

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

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

Seq Scan on public.md_soc_group sg (cost=0.00..2.27 rows=27 width=100) (actual time=0.007..0.024 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.008 12.398 ↑ 1.0 1 1

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

  • Output: p.id, p.social_group_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=12.282
6. 12.390 12.390 ↑ 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=12.388..12.390 rows=1 loops=1)

  • Output: p.id, p.social_group_id
  • Index Cond: (p.id = 181619)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=12.282
7. 24.777 24.777 ↑ 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=24.773..24.777 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 = 181619)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=24.670
8. 0.010 0.010 ↑ 1.0 5 1

Seq Scan on public.pim_gender g (cost=0.00..1.05 rows=5 width=25) (actual time=0.005..0.010 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. 9.684 9.684 ↑ 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=9.682..9.684 rows=1 loops=1)

  • Output: pim_party.version
  • Index Cond: (pim_party.id = p.id)
  • Buffers: shared hit=2 read=2
  • I/O Timings: read=9.610
11. 0.007 16.259 ↑ 1.0 1 1

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

  • Output: pim_indiv_code.code
  • Buffers: shared hit=3 read=2
  • I/O Timings: read=16.122
12. 0.010 16.252 ↑ 1.0 1 1

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

  • Output: pim_indiv_code.code
  • Join Filter: (pim_indiv_code.type_id = pim_code_type.id)
  • Buffers: shared hit=3 read=2
  • I/O Timings: read=16.122
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. 16.221 16.221 ↑ 3.0 1 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=16.221..16.221 rows=1 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=2
  • I/O Timings: read=16.122
15. 0.009 108.799 ↑ 1.0 1 1

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

  • Output: mc_step.admission_date
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=108.627
16. 108.790 108.790 ↑ 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=108.790..108.790 rows=1 loops=1)

  • Output: mc_step.admission_date
  • Index Cond: (mc_step._patient_id = p.id)
  • Heap Fetches: 0
  • Buffers: shared hit=1 read=3
  • I/O Timings: read=108.627
Planning time : 5.976 ms
Execution time : 339.182 ms