explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kuZE

Settings
# exclusive inclusive rows x rows loops node
1. 13.588 101.108 ↓ 4,511.0 4,511 1

Nested Loop (cost=962.55..19,306.88 rows=1 width=60) (actual time=7.478..101.108 rows=4,511 loops=1)

  • Output: r.id, r.patient_id, r.reg_dt, r.unreg_dt, concat(i.surname, (' '::text || (i.name)::text), (' '::text || (i.patr_name)::text)), i.birth_dt, date_part('year'::text, age(($4)::timestamp with time zone, (i.birth_dt)::timestamp with time zone))
  • Buffers: shared hit=28106
2.          

CTE prm

3. 0.093 0.093 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=20) (actual time=0.091..0.093 rows=1 loops=1)

  • Output: 3311022, NULL::integer, to_date('01.01.2019'::text, 'dd.mm.yyyy'::text), to_date('01.01.2019'::text, 'dd.mm.yyyy'::text), NULL::integer
4.          

CTE districts

5. 0.009 0.324 ↓ 7.0 7 1

Nested Loop (cost=0.44..7.09 rows=1 width=4) (actual time=0.275..0.324 rows=7 loops=1)

  • Output: cd.id
  • Buffers: shared hit=44
6. 0.012 0.283 ↓ 16.0 16 1

Nested Loop (cost=0.31..6.91 rows=1 width=8) (actual time=0.240..0.283 rows=16 loops=1)

  • Output: cd.id, cd.type_id
  • Join Filter: ((cd.id = prm.distr) OR (prm.distr IS NULL))
  • Buffers: shared hit=12
7. 0.044 0.247 ↑ 2.0 2 1

Hash Join (cost=0.03..3.42 rows=4 width=8) (actual time=0.230..0.247 rows=2 loops=1)

  • Output: prm.distr, cs.id
  • Hash Cond: (cs.clinic_id = prm.cln)
  • Buffers: shared hit=2
8. 0.103 0.103 ↑ 1.0 98 1

Seq Scan on public.md_clinic_separation cs (cost=0.00..2.98 rows=98 width=8) (actual time=0.006..0.103 rows=98 loops=1)

  • Output: cs.id, cs.name, cs.clinic_id, cs.profile_id, cs.reg_type_id, cs.from_dt, cs.to_dt, cs.aud_who, cs.aud_when, cs.aud_source, cs.aud_who_create, cs.aud_when_create, cs.aud_source_create, cs.department_id
  • Buffers: shared hit=2
9. 0.005 0.100 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=8) (actual time=0.100..0.100 rows=1 loops=1)

  • Output: prm.cln, prm.distr
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.095 0.095 ↑ 1.0 1 1

CTE Scan on prm (cost=0.00..0.02 rows=1 width=8) (actual time=0.094..0.095 rows=1 loops=1)

  • Output: prm.cln, prm.distr
11. 0.024 0.024 ↓ 1.6 8 2

Index Scan using idx_md_clinic_district on public.md_clinic_district cd (cost=0.27..0.81 rows=5 width=12) (actual time=0.005..0.012 rows=8 loops=2)

  • Output: cd.id, cd.separation_id, cd.type_id
  • Index Cond: (cd.separation_id = cs.id)
  • Buffers: shared hit=10
12. 0.032 0.032 ↓ 0.0 0 16

Index Scan using md_clinic_district_type_pk on public.md_clinic_district_type dt (cost=0.13..0.17 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=16)

  • Output: dt.id, dt.code, dt.name, dt.aud_who, dt.aud_when, dt.aud_source, dt.aud_who_create, dt.aud_when_create, dt.aud_source_create
  • Index Cond: (dt.id = cd.type_id)
  • Filter: ((dt.code)::text = '2'::text)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=32
13.          

Initplan (for Nested Loop)

14. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on prm prm_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

  • Output: prm_1.d2
15. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: prm_2.cln
16. 0.000 0.000 ↓ 0.0 0

CTE Scan on prm prm_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

  • Output: prm_3.d1
17. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on prm prm_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: prm_4.d1
18. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

  • Output: prm_5.d2
19. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

  • Output: prm_6.d1
20. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_7 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: prm_7.dep
21. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_8 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: prm_8.dep
22. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on prm prm_9 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

  • Output: prm_9.d2
23. 5.705 53.324 ↓ 4,884.0 4,884 1

Hash Join (cost=954.84..19,291.11 rows=1 width=16) (actual time=7.281..53.324 rows=4,884 loops=1)

  • Output: r.id, r.patient_id, r.reg_dt, r.unreg_dt
  • Hash Cond: (COALESCE(r.district_by_addr_id, r.district_id) = districts.id)
  • Buffers: shared hit=8542
24. 42.070 47.280 ↓ 481.8 22,164 1

Bitmap Heap Scan on public.pci_patient_reg r (cost=954.80..19,290.90 rows=46 width=24) (actual time=6.919..47.280 rows=22,164 loops=1)

  • Output: r.id, r.reg_dt, r.request_dt, r.request_uid, r.unreg_dt, r.clinic_id, r.district_id, r.district_by_addr_id, r.address_id, r.patient_id, r.state_id, r.type_id, r.unreg_cause_id, r.is_assertion, r.automatic_district_dt, r.number_attach, r.aud_who, r.aud_when, r.aud_source, r.aud_who_create, r.aud_when_create, r.aud_source_create, r.department_id, r.pat_selection, r.note
  • Recheck Cond: (r.clinic_id = $5)
  • Filter: ((COALESCE(r.reg_dt, r.request_dt, $6) <= $7) AND (COALESCE(r.unreg_dt, $8) >= $9) AND ((r.department_id = $10) OR ($11 IS NULL)) AND (r.type_id = 1))
  • Rows Removed by Filter: 4377
  • Heap Blocks: exact=8414
  • Buffers: shared hit=8498
25. 5.210 5.210 ↑ 1.6 26,595 1

Bitmap Index Scan on pci_patient_reg_clinic_id_idx (cost=0.00..954.79 rows=42,982 width=0) (actual time=5.210..5.210 rows=26,595 loops=1)

  • Index Cond: (r.clinic_id = $5)
  • Buffers: shared hit=84
26. 0.008 0.339 ↓ 7.0 7 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.339..0.339 rows=7 loops=1)

  • Output: districts.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=44
27. 0.331 0.331 ↓ 7.0 7 1

CTE Scan on districts (cost=0.00..0.02 rows=1 width=4) (actual time=0.278..0.331 rows=7 loops=1)

  • Output: districts.id
  • Buffers: shared hit=44
28. 34.188 34.188 ↑ 1.0 1 4,884

Index Scan using pim_individual_pk on public.pim_individual i (cost=0.42..8.46 rows=1 width=57) (actual time=0.007..0.007 rows=1 loops=4,884)

  • Output: i.surname, i.name, i.patr_name, i.birth_dt, i.id
  • Index Cond: (i.id = r.patient_id)
  • Filter: (date_part('year'::text, age(($12)::timestamp with time zone, (i.birth_dt)::timestamp with time zone)) < '18'::double precision)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=19564
Planning time : 3.407 ms
Execution time : 102.710 ms