explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2Q4j

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 420.102 ↑ 1.0 1 1

Index Scan using pim_individual_pk on public.pim_individual i (cost=134.76..8,401.66 rows=1 width=8) (actual time=420.101..420.102 rows=1 loops=1)

  • Output: CASE WHEN ($0 OR $1 OR (SubPlan 3) OR (SubPlan 4)) THEN false ELSE true END
  • Index Cond: (i.id = 984,252)
  • Buffers: shared hit=118,132
2.          

Initplan (for Index Scan)

3. 0.473 2.618 ↓ 0.0 0 1

Nested Loop (cost=0.29..343.00 rows=3 width=0) (actual time=2.618..2.618 rows=0 loops=1)

  • Join Filter: (upper((d.e_code)::text) = upper((kmd.code)::text))
  • Rows Removed by Join Filter: 558
  • Buffers: shared hit=182
4. 0.009 0.009 ↑ 1.0 1 1

Index Scan using md_diagnosis_pkey on public.md_diagnosis d (cost=0.29..8.30 rows=1 width=5) (actual time=0.008..0.009 rows=1 loops=1)

  • Output: d.id, d.code, d.name, d.parent_id, d.is_injury, d.level, d.is_leaf, d.e_code, d.from_dt, d.to_dt, d.oms, d.stomat, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create, d.gender_id, d.d_accounting
  • Index Cond: (d.id = 28,178)
  • Buffers: shared hit=3
5. 2.136 2.136 ↓ 1.0 558 1

Seq Scan on billing.komi_md_diagnosis kmd (cost=0.00..324.96 rows=556 width=5) (actual time=0.010..2.136 rows=558 loops=1)

  • Output: kmd.id, kmd.code, kmd.w, kmd.soc, kmd.age, kmd.not_main, kmd.is_leaf, kmd.from_dt, kmd.to_dt, kmd.is_chronic, kmd.is_acute, kmd.is_stomat
  • Filter: kmd.not_main
  • Rows Removed by Filter: 14,085
  • Buffers: shared hit=179
6. 3.563 5.863 ↓ 0.0 0 1

Nested Loop (cost=0.29..449.35 rows=23 width=0) (actual time=5.863..5.863 rows=0 loops=1)

  • Join Filter: (upper((d_1.e_code)::text) = upper((kmd_1.code)::text))
  • Rows Removed by Join Filter: 4,563
  • Buffers: shared hit=182
7. 0.006 0.006 ↑ 1.0 1 1

Index Scan using md_diagnosis_pkey on public.md_diagnosis d_1 (cost=0.29..8.30 rows=1 width=5) (actual time=0.004..0.006 rows=1 loops=1)

  • Output: d_1.id, d_1.code, d_1.name, d_1.parent_id, d_1.is_injury, d_1.level, d_1.is_leaf, d_1.e_code, d_1.from_dt, d_1.to_dt, d_1.oms, d_1.stomat, d_1.aud_who, d_1.aud_when, d_1.aud_source, d_1.aud_who_create, d_1.aud_when_create, d_1.aud_source_create, d_1.gender_id, d_1.d_accounting
  • Index Cond: (d_1.id = 28,178)
  • Buffers: shared hit=3
8. 2.294 2.294 ↓ 1.0 4,563 1

Seq Scan on billing.komi_md_diagnosis kmd_1 (cost=0.00..361.45 rows=4,548 width=5) (actual time=0.008..2.294 rows=4,563 loops=1)

  • Output: kmd_1.id, kmd_1.code, kmd_1.w, kmd_1.soc, kmd_1.age, kmd_1.not_main, kmd_1.is_leaf, kmd_1.from_dt, kmd_1.to_dt, kmd_1.is_chronic, kmd_1.is_acute, kmd_1.is_stomat
  • Filter: (kmd_1.soc = 1)
  • Rows Removed by Filter: 10,080
  • Buffers: shared hit=179
9.          

SubPlan (for Index Scan)

10. 0.679 3.909 ↓ 0.0 0 1

Nested Loop (cost=0.29..479.31 rows=1 width=0) (actual time=3.909..3.909 rows=0 loops=1)

  • Join Filter: (upper((d_2.e_code)::text) = upper((kmd_2.code)::text))
  • Rows Removed by Join Filter: 880
  • Buffers: shared hit=182
11. 0.005 0.005 ↑ 1.0 1 1

Index Scan using md_diagnosis_pkey on public.md_diagnosis d_2 (cost=0.29..8.30 rows=1 width=5) (actual time=0.004..0.005 rows=1 loops=1)

  • Output: d_2.id, d_2.code, d_2.name, d_2.parent_id, d_2.is_injury, d_2.level, d_2.is_leaf, d_2.e_code, d_2.from_dt, d_2.to_dt, d_2.oms, d_2.stomat, d_2.aud_who, d_2.aud_when, d_2.aud_source, d_2.aud_who_create, d_2.aud_when_create, d_2.aud_source_create, d_2.gender_id, d_2.d_accounting
  • Index Cond: (d_2.id = 28,178)
  • Buffers: shared hit=3
12. 3.225 3.225 ↓ 176.0 880 1

Seq Scan on billing.komi_md_diagnosis kmd_2 (cost=0.00..470.92 rows=5 width=5) (actual time=0.046..3.225 rows=880 loops=1)

  • Output: kmd_2.id, kmd_2.code, kmd_2.w, kmd_2.soc, kmd_2.age, kmd_2.not_main, kmd_2.is_leaf, kmd_2.from_dt, kmd_2.to_dt, kmd_2.is_chronic, kmd_2.is_acute, kmd_2.is_stomat
  • Filter: (((i.gender_id = 2) AND (kmd_2.w = 1)) OR ((i.gender_id = 1) AND (kmd_2.w = 2)))
  • Rows Removed by Filter: 13,763
  • Buffers: shared hit=179
13. 0.413 407.693 ↓ 0.0 0 1

Nested Loop (cost=0.29..7,779.57 rows=1 width=0) (actual time=407.693..407.693 rows=0 loops=1)

  • Join Filter: (upper((d_3.e_code)::text) = upper((kmd_3.code)::text))
  • Rows Removed by Join Filter: 395
  • Buffers: shared hit=117,582
14. 0.003 0.003 ↑ 1.0 1 1

Index Scan using md_diagnosis_pkey on public.md_diagnosis d_3 (cost=0.29..8.30 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: d_3.id, d_3.code, d_3.name, d_3.parent_id, d_3.is_injury, d_3.level, d_3.is_leaf, d_3.e_code, d_3.from_dt, d_3.to_dt, d_3.oms, d_3.stomat, d_3.aud_who, d_3.aud_when, d_3.aud_source, d_3.aud_who_create, d_3.aud_when_create, d_3.aud_source_create, d_3.gender_id, d_3.d_accounting
  • Index Cond: (d_3.id = 28,178)
  • Buffers: shared hit=3
15. 407.277 407.277 ↓ 2.9 395 1

Seq Scan on billing.komi_md_diagnosis kmd_3 (cost=0.00..7,768.92 rows=134 width=5) (actual time=5.366..407.277 rows=395 loops=1)

  • Output: kmd_3.id, kmd_3.code, kmd_3.w, kmd_3.soc, kmd_3.age, kmd_3.not_main, kmd_3.is_leaf, kmd_3.from_dt, kmd_3.to_dt, kmd_3.is_chronic, kmd_3.is_acute, kmd_3.is_stomat
  • Filter: (((get_age_index(i.id, '2020-08-26'::date) < 18) AND (kmd_3.age = 2)) OR ((get_age_index(i.id, '2020-08-26'::date) >= 18) AND (kmd_3.age = 1)))
  • Rows Removed by Filter: 14,248
  • Buffers: shared hit=117,579
Planning time : 2.220 ms
Execution time : 420.233 ms