explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mLHI

Settings
# exclusive inclusive rows x rows loops node
1. 163.530 27,378.253 ↓ 0.0 0 1

Insert on transform_ehr_ndc_dxid (cost=87.34..177,057.30 rows=6 width=60) (actual time=27,378.253..27,378.253 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Tuples Inserted: 4236
  • Conflicting Tuples: 101124
2. 59.853 27,214.723 ↓ 17,560.0 105,360 1

Nested Loop Left Join (cost=87.34..177,057.30 rows=6 width=60) (actual time=58.444..27,214.723 rows=105,360 loops=1)

  • Join Filter: (sir.indication_id = edge.parent_indication_id)
  • Rows Removed by Join Filter: 99520
3. 16,620.005 27,049.510 ↓ 17,560.0 105,360 1

Nested Loop (cost=87.06..177,043.80 rows=6 width=27) (actual time=58.434..27,049.510 rows=105,360 loops=1)

  • Join Filter: ((kid.icd10)::text = ANY ((d.array_of_icds)::text[]))
  • Rows Removed by Join Filter: 50530908
4. 5.419 3,922.326 ↓ 4,867.0 4,867 1

Nested Loop (cost=86.64..175,721.29 rows=1 width=143) (actual time=55.969..3,922.326 rows=4,867 loops=1)

5. 4.526 3,887.882 ↓ 5,805.0 5,805 1

Nested Loop (cost=86.08..175,718.48 rows=1 width=31) (actual time=55.960..3,887.882 rows=5,805 loops=1)

6. 7.871 3,877.551 ↓ 5,805.0 5,805 1

Nested Loop (cost=85.80..175,712.17 rows=1 width=35) (actual time=55.932..3,877.551 rows=5,805 loops=1)

7. 73.580 3,856.941 ↓ 113.7 12,739 1

Nested Loop (cost=85.52..175,474.80 rows=112 width=39) (actual time=46.283..3,856.941 rows=12,739 loops=1)

8. 0.000 3,033.253 ↓ 236.3 375,054 1

Nested Loop (cost=85.08..171,386.56 rows=1,587 width=31) (actual time=3.161..3,033.253 rows=375,054 loops=1)

9. 71.841 1,534.218 ↓ 236.3 375,054 1

Nested Loop (cost=84.51..166,971.69 rows=1,587 width=27) (actual time=3.152..1,534.218 rows=375,054 loops=1)

10. 0.147 0.147 ↓ 3.9 55 1

Index Scan using idx_383826_prod_fdbndcreference_product_id_c020838b_fk_prod_pro on prod_fdbndcreference ndc (cost=0.42..43.54 rows=14 width=20) (actual time=0.008..0.147 rows=55 loops=1)

  • Index Cond: (product_id = 28060)
11. 1,412.070 1,462.230 ↓ 1.6 6,819 55

Bitmap Heap Scan on prod_ehrndc u (cost=84.09..11,881.47 rows=4,197 width=19) (actual time=1.397..26.586 rows=6,819 loops=55)

  • Recheck Cond: ((ndc_id)::text = (ndc.ndc)::text)
  • Rows Removed by Index Recheck: 59706
  • Heap Blocks: exact=184034 lossy=84890
12. 50.160 50.160 ↓ 1.6 6,819 55

Bitmap Index Scan on idx_383769_prod_ehrndc_ndc_id_8d127d01_fk_prod_fdbndcreference_ (cost=0.00..83.04 rows=4,197 width=0) (actual time=0.912..0.912 rows=6,819 loops=55)

  • Index Cond: ((ndc_id)::text = (ndc.ndc)::text)
13. 1,500.216 1,500.216 ↑ 1.0 1 375,054

Index Scan using idx_383775_primary on prod_ehrorder o (cost=0.56..2.78 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=375,054)

  • Index Cond: (id = u.order_id)
14. 750.108 750.108 ↓ 0.0 0 375,054

Index Scan using prod_ehrencounter_pkey on prod_ehrencounter e (cost=0.44..2.58 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=375,054)

  • Index Cond: (id = o.encounter_id)
  • Filter: ((admission_date >= '2018-07-01 00:00:00+00'::timestamp with time zone) AND (admission_date <= '2018-08-30 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 1
15. 12.739 12.739 ↓ 0.0 0 12,739

Index Scan using acct_facilityalias_pkey on acct_facilityalias fa (cost=0.28..2.12 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=12,739)

  • Index Cond: (id = e.facility_alias_id)
  • Filter: (provider_id = 1)
  • Rows Removed by Filter: 1
16. 5.805 5.805 ↑ 1.0 1 5,805

Index Scan using single_indications_reference_pkey on single_indications_reference sir (cost=0.28..6.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=5,805)

  • Index Cond: (product_id = 28060)
17. 29.025 29.025 ↑ 1.0 1 5,805

Index Scan using prod_ehrencounterdiagnosis_encounter_id_d59cfeec_uniq on prod_ehrencounterdiagnosis d (cost=0.56..2.81 rows=1 width=124) (actual time=0.005..0.005 rows=1 loops=5,805)

  • Index Cond: (encounter_id = o.encounter_id)
18. 6,507.179 6,507.179 ↓ 1.0 10,404 4,867

Index Scan using fml_nav_codes on vw_icd10_knownindication_distance_temp_results kid (cost=0.42..1,089.34 rows=10,363 width=11) (actual time=0.011..1.337 rows=10,404 loops=4,867)

  • Index Cond: ((fml_nav_code)::text = 'equal'::text)
19. 105.360 105.360 ↑ 1.0 1 105,360

Index Scan using prod_indicationedge_indication_id_e619a43d on prod_indicationedge edge (cost=0.28..2.24 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=105,360)

  • Index Cond: (kid.parent_indication_id = indication_id)