explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gq5l

Settings
# exclusive inclusive rows x rows loops node
1. 2.497 59.555 ↓ 40.5 81 1

Sort (cost=550.02..550.02 rows=2 width=1,214) (actual time=59.550..59.555 rows=81 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 44kB
2. 9.486 57.058 ↓ 40.5 81 1

Nested Loop Left Join (cost=19.92..550.01 rows=2 width=1,214) (actual time=31.334..57.058 rows=81 loops=1)

3. 0.082 47.572 ↓ 40.5 81 1

Nested Loop Left Join (cost=19.65..546.00 rows=2 width=1,192) (actual time=22.151..47.572 rows=81 loops=1)

4. 0.945 47.490 ↓ 40.5 81 1

Nested Loop Left Join (cost=19.37..541.80 rows=2 width=676) (actual time=22.146..47.490 rows=81 loops=1)

5. 0.782 44.682 ↓ 40.5 81 1

Nested Loop Left Join (cost=19.10..537.60 rows=2 width=164) (actual time=21.245..44.682 rows=81 loops=1)

6. 0.854 43.819 ↓ 40.5 81 1

Nested Loop Left Join (cost=18.96..537.27 rows=2 width=159) (actual time=20.561..43.819 rows=81 loops=1)

7. 0.058 42.884 ↓ 40.5 81 1

Nested Loop Left Join (cost=18.83..536.95 rows=2 width=157) (actual time=19.812..42.884 rows=81 loops=1)

8. 0.058 42.664 ↓ 40.5 81 1

Nested Loop (cost=18.69..536.62 rows=2 width=159) (actual time=19.788..42.664 rows=81 loops=1)

  • Join Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
9. 2.998 2.998 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..1.30 rows=1 width=13) (actual time=2.996..2.998 rows=1 loops=1)

  • Filter: ((center_name)::text = 'BRS'::text)
  • Rows Removed by Filter: 23
10. 0.802 39.608 ↓ 4.0 81 1

Nested Loop (cost=18.69..534.97 rows=20 width=162) (actual time=16.784..39.608 rows=81 loops=1)

11. 0.317 35.809 ↓ 4.0 81 1

Nested Loop (cost=18.55..531.52 rows=20 width=151) (actual time=16.054..35.809 rows=81 loops=1)

12. 0.050 18.257 ↓ 1.2 15 1

Nested Loop Left Join (cost=14.11..318.72 rows=13 width=107) (actual time=11.370..18.257 rows=15 loops=1)

13. 2.601 17.727 ↓ 1.2 15 1

Hash Left Join (cost=13.82..286.95 rows=13 width=92) (actual time=10.951..17.727 rows=15 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
14. 0.045 9.996 ↓ 1.2 15 1

Nested Loop Left Join (cost=1.13..274.20 rows=13 width=84) (actual time=3.246..9.996 rows=15 loops=1)

15. 0.042 9.891 ↓ 1.2 15 1

Nested Loop Left Join (cost=0.84..166.10 rows=13 width=67) (actual time=3.230..9.891 rows=15 loops=1)

  • Join Filter: (dy.dyna_package_id = b.dyna_package_id)
  • Rows Removed by Join Filter: 60
16. 0.911 9.819 ↓ 1.2 15 1

Nested Loop Left Join (cost=0.84..164.27 rows=13 width=64) (actual time=3.207..9.819 rows=15 loops=1)

17. 4.438 4.438 ↓ 1.2 15 1

Index Scan using bill_finalized_date_idx on bill b (cost=0.42..54.49 rows=13 width=36) (actual time=0.915..4.438 rows=15 loops=1)

  • Index Cond: ((date(finalized_date) >= '2020-08-01'::date) AND (date(finalized_date) <= '2020-08-31'::date))
  • Filter: (status <> 'X'::bpchar)
18. 4.470 4.470 ↑ 1.0 1 15

Index Scan using idx_patient_registration_patientid_optype on patient_registration pr (cost=0.42..8.44 rows=1 width=37) (actual time=0.297..0.298 rows=1 loops=15)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
19. 0.026 0.030 ↑ 1.0 4 15

Materialize (cost=0.00..1.06 rows=4 width=11) (actual time=0.001..0.002 rows=4 loops=15)

20. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on dyna_packages dy (cost=0.00..1.04 rows=4 width=11) (actual time=0.003..0.004 rows=4 loops=1)

21. 0.060 0.060 ↓ 0.0 0 15

Index Scan using pharmacy_retail_customers_pkey on store_retail_customers prc (cost=0.29..8.30 rows=1 width=26) (actual time=0.004..0.004 rows=0 loops=15)

  • Index Cond: ((customer_id)::text = (b.visit_id)::text)
22. 2.684 5.130 ↓ 1.0 255 1

Hash (cost=9.53..9.53 rows=253 width=26) (actual time=5.130..5.130 rows=255 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
23. 2.446 2.446 ↓ 1.0 255 1

Seq Scan on incoming_sample_registration isr (cost=0.00..9.53 rows=253 width=26) (actual time=0.673..2.446 rows=255 loops=1)

24. 0.480 0.480 ↑ 1.0 1 15

Index Scan using patient_details_pkey on patient_details pd (cost=0.29..2.43 rows=1 width=33) (actual time=0.030..0.032 rows=1 loops=15)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 11.445 17.235 ↓ 1.7 5 15

Bitmap Heap Scan on bill_charge bc (cost=4.44..16.34 rows=3 width=54) (actual time=0.747..1.149 rows=5 loops=15)

  • Recheck Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 1
26. 5.790 5.790 ↓ 2.0 6 15

Bitmap Index Scan on bill_charge_bill_no_index (cost=0.00..4.44 rows=3 width=0) (actual time=0.386..0.386 rows=6 loops=15)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
27. 2.997 2.997 ↑ 1.0 1 81

Index Scan using chargehead_constants_pkey on chargehead_constants chc (cost=0.14..0.16 rows=1 width=23) (actual time=0.036..0.037 rows=1 loops=81)

  • Index Cond: ((chargehead_id)::text = (bc.charge_head)::text)
28. 0.162 0.162 ↑ 1.0 1 81

Index Scan using salutation_master_pkey on salutation_master smb (cost=0.14..0.16 rows=1 width=15) (actual time=0.001..0.002 rows=1 loops=81)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
29. 0.081 0.081 ↑ 1.0 1 81

Index Scan using visit_type_names_pkey on visit_type_names vn (cost=0.13..0.15 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=81)

  • Index Cond: (visit_type = pr.visit_type)
30. 0.081 0.081 ↑ 1.0 1 81

Index Scan using department_pkey on department admdep (cost=0.14..0.16 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=81)

  • Index Cond: ((pr.admitted_dept)::text = (dept_id)::text)
31. 1.863 1.863 ↑ 1.0 1 81

Index Scan using doctors_pkey on doctors dr (cost=0.27..2.09 rows=1 width=523) (actual time=0.023..0.023 rows=1 loops=81)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
32. 0.000 0.000 ↓ 0.0 0 81

Index Scan using doctors_pkey on doctors rdoc (cost=0.27..2.09 rows=1 width=523) (actual time=0.000..0.000 rows=0 loops=81)

  • Index Cond: ((pr.reference_docto_id)::text = (doctor_id)::text)
33. 0.000 0.000 ↓ 0.0 0 81

Index Scan using referral_pkey on referral ref (cost=0.27..1.96 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=81)

  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
Total runtime : 87.027 ms