explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LZokd

Settings
# exclusive inclusive rows x rows loops node
1. 0.039 2.692 ↓ 15.0 30 1

Sort (cost=550.32..550.33 rows=2 width=1,624) (actual time=2.691..2.692 rows=30 loops=1)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 42kB
2. 0.063 2.605 ↓ 15.0 30 1

Nested Loop Left Join (cost=28.18..550.31 rows=2 width=1,624) (actual time=1.699..2.605 rows=30 loops=1)

3. 0.027 2.542 ↓ 15.0 30 1

Nested Loop Left Join (cost=27.90..542.61 rows=2 width=879) (actual time=1.691..2.542 rows=30 loops=1)

4. 0.207 2.515 ↓ 15.0 30 1

Nested Loop Left Join (cost=27.62..534.94 rows=2 width=857) (actual time=1.686..2.515 rows=30 loops=1)

  • Join Filter: ((bc.act_department_id)::text = tdv.dept_id)
  • Rows Removed by Join Filter: 3443
5. 0.024 2.098 ↓ 15.0 30 1

Nested Loop (cost=27.62..524.50 rows=2 width=825) (actual time=1.607..2.098 rows=30 loops=1)

6. 0.034 2.044 ↓ 15.0 30 1

Nested Loop (cost=27.48..524.19 rows=2 width=707) (actual time=1.598..2.044 rows=30 loops=1)

7. 0.014 1.980 ↓ 15.0 30 1

Nested Loop Left Join (cost=27.34..523.87 rows=2 width=686) (actual time=1.587..1.980 rows=30 loops=1)

8. 0.020 1.786 ↓ 15.0 30 1

Nested Loop (cost=26.91..515.24 rows=2 width=682) (actual time=1.573..1.786 rows=30 loops=1)

9. 0.033 1.679 ↓ 3.0 3 1

Nested Loop (cost=25.34..139.05 rows=1 width=59) (actual time=1.542..1.679 rows=3 loops=1)

  • Join Filter: ((ic.status = 'O'::bpchar) OR ((ic.status = 'R'::bpchar) AND (b.resubmission_status = 'O'::bpchar)))
10. 0.023 1.259 ↓ 21.5 43 1

Nested Loop (cost=24.90..130.35 rows=2 width=73) (actual time=0.364..1.259 rows=43 loops=1)

11. 0.020 0.849 ↓ 14.3 43 1

Nested Loop (cost=24.47..117.35 rows=3 width=40) (actual time=0.347..0.849 rows=43 loops=1)

12. 0.025 0.389 ↓ 14.7 44 1

Hash Join (cost=24.04..91.97 rows=3 width=16) (actual time=0.323..0.389 rows=44 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tm.tpa_id)::text)
13. 0.075 0.075 ↓ 2.8 45 1

Index Scan using patient_registration_mr_no_index on patient_registration pr (cost=0.43..68.32 rows=16 width=26) (actual time=0.023..0.075 rows=45 loops=1)

  • Index Cond: ((mr_no)::text = 'UA1300000089577'::text)
14. 0.031 0.289 ↓ 1.1 148 1

Hash (cost=21.88..21.88 rows=138 width=10) (actual time=0.289..0.289 rows=148 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
15. 0.258 0.258 ↓ 1.1 148 1

Seq Scan on tpa_master tm (cost=0.00..21.88 rows=138 width=10) (actual time=0.018..0.258 rows=148 loops=1)

  • Filter: (((inter_company_tpa)::text = 'N'::text) AND ((claim_format)::text = 'XML'::text))
  • Rows Removed by Filter: 444
16. 0.440 0.440 ↑ 1.0 1 44

Index Scan using bill_visit_id_idx on bill b (cost=0.43..8.45 rows=1 width=40) (actual time=0.010..0.010 rows=1 loops=44)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Rows Removed by Filter: 0
17. 0.387 0.387 ↑ 1.0 1 43

Index Scan using bill_claim_bill_no_idx on bill_claim bcp (cost=0.43..4.32 rows=1 width=33) (actual time=0.009..0.009 rows=1 loops=43)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (priority = 1)
18. 0.387 0.387 ↓ 0.0 0 43

Index Scan using insurance_claim_id_index on insurance_claim ic (cost=0.43..4.34 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=43)

  • Index Cond: ((claim_id)::text = (bcp.claim_id)::text)
  • Filter: ((status = 'O'::bpchar) OR (status = 'R'::bpchar))
  • Rows Removed by Filter: 1
19. 0.084 0.087 ↑ 18.2 10 3

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=1.57..374.36 rows=182 width=652) (actual time=0.017..0.029 rows=10 loops=3)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (((hashed SubPlan 1) AND ((COALESCE(act_rate_plan_item_code, ''::character varying))::text <> ''::text)) OR ((COALESCE(act_rate_plan_item_code, 'N'::character varying))::text <> 'N'::text))
20.          

SubPlan (forIndex Scan)

21. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on bundle_edit_supported_code_types (cost=0.00..1.01 rows=1 width=118) (actual time=0.003..0.003 rows=1 loops=1)

22. 0.180 0.180 ↓ 0.0 0 30

Index Scan using bill_claim_bill_no_idx on bill_claim bcs (cost=0.43..4.31 rows=1 width=19) (actual time=0.006..0.006 rows=0 loops=30)

  • Index Cond: ((bc.bill_no)::text = (bill_no)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
23. 0.030 0.030 ↑ 1.0 1 30

Index Scan using chargehead_constants_pkey on chargehead_constants (cost=0.14..0.16 rows=1 width=27) (actual time=0.001..0.001 rows=1 loops=30)

  • Index Cond: ((chargehead_id)::text = (bc.charge_head)::text)
24. 0.030 0.030 ↑ 1.0 1 30

Index Scan using chargegroup_constants_pkey on chargegroup_constants (cost=0.14..0.16 rows=1 width=156) (actual time=0.001..0.001 rows=1 loops=30)

  • Index Cond: ((chargegroup_id)::text = (bc.charge_group)::text)
25. 0.210 0.210 ↑ 1.0 115 30

Materialize (cost=0.00..7.28 rows=115 width=64) (actual time=0.000..0.007 rows=115 loops=30)

26. 0.000 0.048 ↑ 1.0 115 1

Subquery Scan on tdv (cost=0.00..6.70 rows=115 width=64) (actual time=0.007..0.048 rows=115 loops=1)

27. 0.007 0.040 ↑ 1.0 115 1

Append (cost=0.00..5.55 rows=115 width=96) (actual time=0.006..0.040 rows=115 loops=1)

28. 0.012 0.012 ↑ 1.0 44 1

Seq Scan on services_departments (cost=0.00..1.66 rows=44 width=222) (actual time=0.006..0.012 rows=44 loops=1)

29. 0.002 0.007 ↑ 1.0 12 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.27 rows=12 width=382) (actual time=0.004..0.007 rows=12 loops=1)

30. 0.005 0.005 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.15 rows=12 width=388) (actual time=0.004..0.005 rows=12 loops=1)

31. 0.005 0.014 ↑ 1.0 59 1

Subquery Scan on *SELECT* 3 (cost=0.00..2.18 rows=59 width=77) (actual time=0.004..0.014 rows=59 loops=1)

32. 0.009 0.009 ↑ 1.0 59 1

Seq Scan on department (cost=0.00..1.59 rows=59 width=53) (actual time=0.003..0.009 rows=59 loops=1)

33. 0.000 0.000 ↓ 0.0 0 30

Index Scan using doctors_pkey on doctors prd (cost=0.28..3.84 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=30)

  • Index Cond: ((doctor_id)::text = (bc.prescribing_dr_id)::text)
34. 0.030 0.030 ↓ 0.0 0 30

Index Scan using doctors_pkey on doctors prdp (cost=0.28..3.84 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=30)

  • Index Cond: ((doctor_id)::text = (bc.payee_doctor_id)::text)
Planning time : 8.181 ms
Execution time : 3.170 ms