explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rrLQ

Settings
# exclusive inclusive rows x rows loops node
1. 13.052 95.775 ↓ 4,511.0 4,511 1

Nested Loop (cost=1,056.42..1,865.33 rows=1 width=60) (actual time=4.779..95.775 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(($1)::timestamp with time zone, (i.birth_dt)::timestamp with time zone))
  • Buffers: shared hit=24066
2.          

CTE prm

3. 0.015 0.015 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=20) (actual time=0.014..0.015 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.          

Initplan (for Nested Loop)

5. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm.d2
6. 0.017 0.017 ↑ 1.0 1 1

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

  • Output: prm_1.distr
7. 0.000 0.000 ↑ 1.0 1 1

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

  • Output: prm_2.distr
8. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_3.d2
9. 0.000 0.000 ↑ 1.0 1 1

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

  • Output: prm_4.cln
10. 0.000 0.000 ↓ 0.0 0

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

  • Output: prm_5.d1
11. 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.001..0.001 rows=1 loops=1)

  • Output: prm_6.d1
12. 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.000..0.001 rows=1 loops=1)

  • Output: prm_7.d2
13. 0.000 0.000 ↑ 1.0 1 1

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

  • Output: prm_8.d1
14. 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.dep
15. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_10.dep
16. 0.001 0.001 ↑ 1.0 1 1

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

  • Output: prm_11.cln
17. 1.477 43.627 ↓ 4,884.0 4,884 1

Nested Loop (cost=1,055.74..1,856.59 rows=1 width=16) (actual time=4.744..43.627 rows=4,884 loops=1)

  • Output: r.id, r.patient_id, r.reg_dt, r.unreg_dt
  • Buffers: shared hit=4502
18. 0.518 0.745 ↓ 7.0 7 1

Nested Loop Semi Join (cost=0.00..17.73 rows=1 width=4) (actual time=0.121..0.745 rows=7 loops=1)

  • Output: cd.id
  • Join Filter: ((cd.separation_id = cs.id) AND (cd.type_id = dt.id))
  • Rows Removed by Join Filter: 987
  • Buffers: shared hit=10
19. 0.227 0.227 ↓ 165.7 497 1

Seq Scan on public.md_clinic_district cd (cost=0.00..13.20 rows=3 width=12) (actual time=0.028..0.227 rows=497 loops=1)

  • Output: cd.id, cd.separation_id, cd.type_id
  • Filter: ((cd.id = $2) OR ($3 IS NULL))
  • Buffers: shared hit=7
20. 0.000 0.000 ↑ 2.0 2 497

Materialize (cost=0.00..4.32 rows=4 width=8) (actual time=0.000..0.000 rows=2 loops=497)

  • Output: cs.id, dt.id
  • Buffers: shared hit=3
21. 0.003 0.028 ↑ 2.0 2 1

Nested Loop (cost=0.00..4.30 rows=4 width=8) (actual time=0.021..0.028 rows=2 loops=1)

  • Output: cs.id, dt.id
  • Buffers: shared hit=3
22. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on public.md_clinic_district_type dt (cost=0.00..1.04 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • 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
  • Filter: ((dt.code)::text = '2'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1
23. 0.021 0.021 ↑ 2.0 2 1

Seq Scan on public.md_clinic_separation cs (cost=0.00..3.23 rows=4 width=4) (actual time=0.016..0.021 rows=2 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
  • Filter: (cs.clinic_id = $12)
  • Rows Removed by Filter: 96
  • Buffers: shared hit=2
24. 17.458 41.405 ↓ 698.0 698 7

Bitmap Heap Scan on public.pci_patient_reg r (cost=1,055.74..1,838.85 rows=1 width=24) (actual time=3.502..5.915 rows=698 loops=7)

  • 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: ((COALESCE(r.district_by_addr_id, r.district_id) = cd.id) AND (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: 100
  • Heap Blocks: exact=3867
  • Buffers: shared hit=4492
25. 0.455 23.947 ↓ 0.0 0 7

BitmapAnd (cost=1,055.74..1,055.74 rows=215 width=0) (actual time=3.421..3.421 rows=0 loops=7)

  • Buffers: shared hit=625
26. 0.966 0.966 ↑ 6.7 798 7

Bitmap Index Scan on pci_patient_reg_coalesce_addresses_idx (cost=0.00..100.72 rows=5,372 width=0) (actual time=0.138..0.138 rows=798 loops=7)

  • Index Cond: (COALESCE(r.district_by_addr_id, r.district_id) = cd.id)
  • Buffers: shared hit=37
27. 22.526 22.526 ↑ 1.6 26,595 7

Bitmap Index Scan on pci_patient_reg_clinic_id_idx (cost=0.00..954.76 rows=42,978 width=0) (actual time=3.218..3.218 rows=26,595 loops=7)

  • Index Cond: (r.clinic_id = $5)
  • Buffers: shared hit=588
28. 39.072 39.072 ↑ 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.008..0.008 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(($4)::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.945 ms
Execution time : 96.723 ms