explain.depesz.com

PostgreSQL's explain analyze made readable

Result: accf

Settings
# exclusive inclusive rows x rows loops node
1. 18.059 196,500.277 ↓ 3,963.0 3,963 1

Sort (cost=77,181.03..77,181.03 rows=1 width=213) (actual time=196,499.266..196,500.277 rows=3,963 loops=1)

  • Sort Key: (date(b.finalized_date))
  • Sort Method: quicksort Memory: 1149kB
2. 96.348 196,482.218 ↓ 3,963.0 3,963 1

Nested Loop Left Join (cost=687.38..77,181.02 rows=1 width=213) (actual time=118.321..196,482.218 rows=3,963 loops=1)

  • Join Filter: ((hcmo.center_id)::text = (dom.outsource_dest)::text)
  • Rows Removed by Join Filter: 3985
  • Filter: (CASE WHEN ((dom.outsource_dest_type)::text = ANY ('{O,IO}'::text[])) THEN om.oh_name ELSE hcmo.center_name END IS NULL)
  • Rows Removed by Filter: 22
3. 21.745 196,381.885 ↓ 3,985.0 3,985 1

Nested Loop Left Join (cost=687.38..77,179.94 rows=1 width=209) (actual time=118.256..196,381.885 rows=3,985 loops=1)

  • Join Filter: ((om.oh_id)::text = (dom.outsource_dest)::text)
  • Rows Removed by Join Filter: 31858
4. 40.861 196,352.170 ↓ 3,985.0 3,985 1

Nested Loop Left Join (cost=687.38..77,178.76 rows=1 width=199) (actual time=118.243..196,352.170 rows=3,985 loops=1)

  • Join Filter: ((dom.outsource_dest_id = tp.outsource_dest_id) OR (dom.outsource_dest_id = tpr.outsource_dest_id))
  • Rows Removed by Join Filter: 31858
5. 26,528.439 196,299.354 ↓ 3,985.0 3,985 1

Nested Loop Left Join (cost=687.38..77,177.56 rows=1 width=194) (actual time=118.222..196,299.354 rows=3,985 loops=1)

  • Join Filter: ((tpr.pat_id)::text = (isr.incoming_visit_id)::text)
  • Rows Removed by Join Filter: 193666124
6. 12.900 12,789.810 ↓ 3,985.0 3,985 1

Nested Loop Left Join (cost=686.95..77,100.20 rows=1 width=206) (actual time=59.365..12,789.810 rows=3,985 loops=1)

7. 430.024 11,646.352 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=686.52..77,099.49 rows=1 width=206) (actual time=59.327..11,646.352 rows=3,953 loops=1)

  • Join Filter: ((pm.package_id)::text = (bc.act_description_id)::text)
  • Rows Removed by Join Filter: 1446798
  • Filter: (((bc.charge_group)::text = 'DIA'::text) OR (((bc.charge_group)::text = 'PKG'::text) AND (pm.package_type = 'd'::bpchar)))
8. 11.518 11,034.490 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=686.52..77,079.68 rows=1 width=210) (actual time=59.163..11,034.490 rows=3,953 loops=1)

9. 12.482 10,991.348 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=686.38..77,079.51 rows=1 width=207) (actual time=59.146..10,991.348 rows=3,953 loops=1)

10. 13.891 10,955.148 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=686.25..77,079.35 rows=1 width=212) (actual time=59.128..10,955.148 rows=3,953 loops=1)

11. 9.295 10,846.385 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=685.83..77,078.83 rows=1 width=196) (actual time=59.097..10,846.385 rows=3,953 loops=1)

12. 11.446 10,813.372 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=685.55..77,078.52 rows=1 width=182) (actual time=59.064..10,813.372 rows=3,953 loops=1)

13. 15.529 10,770.302 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=685.26..77,077.78 rows=1 width=172) (actual time=59.043..10,770.302 rows=3,953 loops=1)

14. 8.359 10,422.721 ↓ 3,953.0 3,953 1

Nested Loop Left Join (cost=684.84..77,071.23 rows=1 width=155) (actual time=59.007..10,422.721 rows=3,953 loops=1)

15. 27.876 10,394.597 ↓ 3,953.0 3,953 1

Nested Loop (cost=684.56..77,070.90 rows=1 width=125) (actual time=58.993..10,394.597 rows=3,953 loops=1)

16. 93.574 2,971.081 ↓ 471.7 105,652 1

Hash Join (cost=684.14..76,947.61 rows=224 width=92) (actual time=56.898..2,971.081 rows=105,652 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
17. 150.641 2,877.491 ↓ 14.6 200,091 1

Nested Loop (cost=682.36..76,892.35 rows=13,666 width=82) (actual time=56.867..2,877.491 rows=200,091 loops=1)

18. 0.029 0.029 ↑ 1.0 1 1

Index Scan using idx_doctor_name_d on doctors predoc (cost=0.14..8.16 rows=1 width=23) (actual time=0.013..0.029 rows=1 loops=1)

  • Index Cond: ((doctor_name)::text = 'Gupta Ashok'::text)
19. 2,681.780 2,726.821 ↓ 8.7 200,091 1

Bitmap Heap Scan on bill_charge bc (cost=682.22..76,653.58 rows=23,061 width=74) (actual time=56.844..2,726.821 rows=200,091 loops=1)

  • Recheck Cond: ((prescribing_dr_id)::text = (predoc.doctor_id)::text)
  • Rows Removed by Index Recheck: 656308
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 24644
20. 45.041 45.041 ↓ 9.0 224,797 1

Bitmap Index Scan on bc_pres_doc_index (cost=0.00..676.45 rows=25,069 width=0) (actual time=45.041..45.041 rows=224,797 loops=1)

  • Index Cond: ((prescribing_dr_id)::text = (predoc.doctor_id)::text)
21. 0.002 0.016 ↑ 1.0 1 1

Hash (cost=1.76..1.76 rows=1 width=22) (actual time=0.016..0.016 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
22. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on chargehead_constants chc (cost=0.00..1.76 rows=1 width=22) (actual time=0.011..0.014 rows=1 loops=1)

  • Filter: ((chargehead_name)::text = 'Laboratory Test'::text)
  • Rows Removed by Filter: 60
23. 7,395.640 7,395.640 ↓ 0.0 0 105,652

Index Scan using bill_pkey on bill b (cost=0.42..0.54 rows=1 width=42) (actual time=0.069..0.070 rows=0 loops=105,652)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: ((status <> 'X'::bpchar) AND (date(closed_date) >= '2018-11-01'::date) AND (date(closed_date) <= '2018-11-30'::date))
  • Rows Removed by Filter: 1
24. 19.765 19.765 ↓ 0.0 0 3,953

Index Scan using dyna_packages_pkey on dyna_packages dy (cost=0.28..0.32 rows=1 width=38) (actual time=0.005..0.005 rows=0 loops=3,953)

  • Index Cond: (dyna_package_id = b.dyna_package_id)
25. 332.052 332.052 ↑ 1.0 1 3,953

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..6.54 rows=1 width=26) (actual time=0.081..0.084 rows=1 loops=3,953)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
26. 31.624 31.624 ↓ 0.0 0 3,953

Index Scan using pharmacy_retail_customers_pkey on store_retail_customers prc (cost=0.29..0.73 rows=1 width=19) (actual time=0.008..0.008 rows=0 loops=3,953)

  • Index Cond: ((customer_id)::text = (b.visit_id)::text)
27. 23.718 23.718 ↓ 0.0 0 3,953

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.27..0.30 rows=1 width=23) (actual time=0.006..0.006 rows=0 loops=3,953)

  • Index Cond: ((incoming_visit_id)::text = (b.visit_id)::text)
28. 94.872 94.872 ↑ 1.0 1 3,953

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..0.50 rows=1 width=34) (actual time=0.022..0.024 rows=1 loops=3,953)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
29. 23.718 23.718 ↑ 1.0 1 3,953

Index Scan using salutation_master_pkey on salutation_master smb (cost=0.13..0.15 rows=1 width=13) (actual time=0.005..0.006 rows=1 loops=3,953)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
30. 31.624 31.624 ↑ 1.0 1 3,953

Index Scan using organization_details_pkey on organization_details od (cost=0.14..0.16 rows=1 width=19) (actual time=0.006..0.008 rows=1 loops=3,953)

  • Index Cond: ((pr.org_id)::text = (org_id)::text)
31. 181.838 181.838 ↑ 1.0 366 3,953

Seq Scan on pack_master pm (cost=0.00..10.66 rows=366 width=6) (actual time=0.003..0.046 rows=366 loops=3,953)

32. 1,130.558 1,130.558 ↑ 1.0 1 3,953

Index Scan using idx_test_presc_common_order_id on tests_prescribed tp (cost=0.43..0.70 rows=1 width=16) (actual time=0.263..0.286 rows=1 loops=3,953)

  • Index Cond: (common_order_id = bc.order_number)
  • Filter: ((test_id)::text = (bc.act_description_id)::text)
  • Rows Removed by Filter: 11
33. 156,981.105 156,981.105 ↓ 19.0 48,599 3,985

Index Scan using tests_prescribed_testid_idx on tests_prescribed tpr (cost=0.43..45.33 rows=2,563 width=21) (actual time=0.037..39.393 rows=48,599 loops=3,985)

  • Index Cond: ((test_id)::text = (bc.act_description_id)::text)
34. 11.955 11.955 ↑ 1.0 8 3,985

Seq Scan on diag_outsource_master dom (cost=0.00..1.08 rows=8 width=17) (actual time=0.002..0.003 rows=8 loops=3,985)

35. 7.970 7.970 ↑ 1.0 8 3,985

Seq Scan on outhouse_master om (cost=0.00..1.08 rows=8 width=21) (actual time=0.001..0.002 rows=8 loops=3,985)

36. 3.985 3.985 ↑ 1.0 1 3,985

Seq Scan on hospital_center_master hcmo (cost=0.00..1.01 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=3,985)