explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7ePpq

Settings
# exclusive inclusive rows x rows loops node
1. 518.407 159,095.603 ↓ 2.2 95,193 1

Hash Left Join (cost=6,065,511.81..10,945,680.56 rows=43,986 width=856) (actual time=143,024.034..159,095.603 rows=95,193 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
2. 44.159 153,726.581 ↓ 2.2 95,193 1

Hash Left Join (cost=6,054,895.56..9,220,829.95 rows=43,986 width=396) (actual time=142,927.925..153,726.581 rows=95,193 loops=1)

  • Hash Cond: ((doc.dept_id)::text = (dept.dept_id)::text)
3. 53.476 153,682.381 ↓ 2.2 95,193 1

Hash Left Join (cost=6,054,890.81..9,220,686.93 rows=43,986 width=391) (actual time=142,927.845..153,682.381 rows=95,193 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
4. 52.341 153,627.674 ↓ 2.2 95,193 1

Nested Loop Left Join (cost=6,054,674.50..9,220,354.50 rows=43,986 width=359) (actual time=142,926.543..153,627.674 rows=95,193 loops=1)

5. 10.844 152,718.596 ↓ 2.2 95,193 1

Nested Loop Left Join (cost=6,054,673.94..9,128,001.22 rows=43,986 width=351) (actual time=142,926.034..152,718.596 rows=95,193 loops=1)

6. 2,956.542 145,463.112 ↓ 2.1 92,880 1

Merge Left Join (cost=6,054,673.38..8,836,617.15 rows=43,986 width=311) (actual time=142,923.584..145,463.112 rows=92,880 loops=1)

  • Merge Cond: ((bc.charge_id)::text = (ipa_1.charge_id)::text)
7. 330.038 68,859.349 ↓ 2.1 92,880 1

Sort (cost=2,629,576.84..2,629,686.81 rows=43,986 width=317) (actual time=68,807.086..68,859.349 rows=92,880 loops=1)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 50432kB
8. 9,956.233 68,529.311 ↓ 2.1 92,880 1

Hash Right Join (cost=558,560.16..2,626,184.47 rows=43,986 width=317) (actual time=56,282.467..68,529.311 rows=92,880 loops=1)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
9. 24,588.736 24,588.736 ↑ 1.0 37,968,661 1

Seq Scan on insurance_payment_allocation ipa (cost=0.00..1,923,928.56 rows=38,028,052 width=24) (actual time=1.397..24,588.736 rows=37,968,661 loops=1)

10. 234.039 33,984.342 ↓ 2.0 87,198 1

Hash (cost=557,130.61..557,130.61 rows=43,986 width=303) (actual time=33,984.342..33,984.342 rows=87,198 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 27819kB
11. 133.797 33,750.303 ↓ 2.0 87,198 1

Nested Loop (cost=5,842.43..557,130.61 rows=43,986 width=303) (actual time=1,062.250..33,750.303 rows=87,198 loops=1)

12. 85.558 20,351.474 ↓ 2.0 97,537 1

Nested Loop (cost=5,841.86..522,950.62 rows=49,855 width=297) (actual time=1,059.528..20,351.474 rows=97,537 loops=1)

13. 31.361 12,232.164 ↓ 2.5 25,832 1

Nested Loop Left Join (cost=5,841.30..363,957.84 rows=10,475 width=269) (actual time=1,058.610..12,232.164 rows=25,832 loops=1)

14. 16.168 9,441.555 ↓ 1.8 11,126 1

Nested Loop (cost=5,840.74..321,039.72 rows=6,041 width=229) (actual time=1,058.588..9,441.555 rows=11,126 loops=1)

15. 19.625 9,056.045 ↓ 2.0 10,863 1

Nested Loop Left Join (cost=5,840.18..305,744.09 rows=5,426 width=215) (actual time=1,058.285..9,056.045 rows=10,863 loops=1)

16. 15.736 3,496.290 ↓ 2.0 10,863 1

Nested Loop Left Join (cost=5,839.61..268,497.46 rows=5,426 width=175) (actual time=1,056.928..3,496.290 rows=10,863 loops=1)

17. 25.674 3,100.349 ↓ 2.0 10,863 1

Nested Loop (cost=5,839.18..260,986.90 rows=5,426 width=111) (actual time=1,056.865..3,100.349 rows=10,863 loops=1)

18. 0.050 0.050 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..5.50 rows=1 width=31) (actual time=0.022..0.050 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC MBZCity'::text)
  • Rows Removed by Filter: 45
19. 2,215.320 3,074.625 ↓ 1.6 10,863 1

Bitmap Heap Scan on patient_registration pr (cost=5,839.18..260,773.42 rows=6,933 width=88) (actual time=1,056.837..3,074.625 rows=10,863 loops=1)

  • Recheck Cond: (center_id = hcm.center_id)
  • Filter: ((reg_date >= '2019-06-01'::date) AND (reg_date <= '2019-06-30'::date))
  • Rows Removed by Filter: 522087
  • Heap Blocks: exact=269505
20. 859.305 859.305 ↓ 2.2 534,923 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..5,837.45 rows=241,202 width=0) (actual time=859.304..859.305 rows=534,923 loops=1)

  • Index Cond: (center_id = hcm.center_id)
21. 380.205 380.205 ↑ 1.0 1 10,863

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.38 rows=1 width=64) (actual time=0.035..0.035 rows=1 loops=10,863)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
22. 5,540.130 5,540.130 ↑ 9.0 1 10,863

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis (cost=0.56..6.59 rows=9 width=56) (actual time=0.508..0.510 rows=1 loops=10,863)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 2
23. 369.342 369.342 ↑ 1.0 1 10,863

Index Scan using bill_visit_id_idx on bill b (cost=0.56..2.79 rows=1 width=29) (actual time=0.033..0.034 rows=1 loops=10,863)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
24. 2,759.248 2,759.248 ↑ 8.5 2 11,126

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_1 (cost=0.56..6.59 rows=17 width=56) (actual time=0.104..0.248 rows=2 loops=11,126)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'S'::bpchar)
  • Rows Removed by Filter: 1
25. 8,033.752 8,033.752 ↑ 52.0 4 25,832

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..8.94 rows=208 width=56) (actual time=0.138..0.311 rows=4 loops=25,832)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
26. 13,265.032 13,265.032 ↑ 1.0 1 97,537

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..0.66 rows=1 width=16) (actual time=0.135..0.136 rows=1 loops=97,537)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
27. 5,544.138 73,647.221 ↓ 2.7 21,340,028 1

Finalize GroupAggregate (cost=3,425,096.54..5,946,994.95 rows=7,987,581 width=14) (actual time=43,865.632..73,647.221 rows=21,340,028 loops=1)

  • Group Key: ipa_1.charge_id
28. 14,752.275 68,103.083 ↓ 1.6 25,093,714 1

Gather Merge (cost=3,425,096.54..5,627,491.71 rows=15,975,162 width=14) (actual time=43,865.570..68,103.083 rows=25,093,714 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
29. 2,518.740 53,350.808 ↓ 1.0 8,365,485 3 / 3

Partial GroupAggregate (cost=3,424,096.51..3,782,561.61 rows=7,987,581 width=14) (actual time=43,548.464..53,350.808 rows=8,365,485 loops=3)

  • Group Key: ipa_1.charge_id
30. 45,406.691 50,832.068 ↑ 1.6 9,967,997 3 / 3

Sort (cost=3,424,096.51..3,463,709.07 rows=15,845,022 width=14) (actual time=43,548.446..50,832.068 rows=9,967,997 loops=3)

  • Sort Key: ipa_1.charge_id
  • Sort Method: external merge Disk: 323048kB
31. 2,386.964 5,425.377 ↑ 1.3 12,656,220 3 / 3

Hash Left Join (cost=35,773.38..1,335,805.36 rows=15,845,022 width=14) (actual time=404.371..5,425.377 rows=12,656,220 loops=3)

  • Hash Cond: (ipa_1.remittance_id = ir.remittance_id)
32. 2,636.655 2,636.655 ↑ 1.3 12,656,220 3 / 3

Parallel Seq Scan on insurance_payment_allocation ipa_1 (cost=0.00..1,258,437.65 rows=15,845,022 width=14) (actual time=0.027..2,636.655 rows=12,656,220 loops=3)

33. 126.868 401.758 ↓ 1.0 428,821 3 / 3

Hash (cost=21,837.70..21,837.70 rows=428,790 width=8) (actual time=401.758..401.758 rows=428,821 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 20847kB
34. 274.890 274.890 ↓ 1.0 428,821 3 / 3

Seq Scan on insurance_remittance ir (cost=0.00..21,837.70 rows=428,790 width=8) (actual time=0.021..274.890 rows=428,821 loops=3)

35. 7,244.640 7,244.640 ↓ 0.0 0 92,880

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_2 (cost=0.56..6.59 rows=1 width=56) (actual time=0.076..0.078 rows=0 loops=92,880)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'R'::bpchar)
  • Rows Removed by Filter: 6
36. 856.737 856.737 ↑ 1.0 1 95,193

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..2.07 rows=1 width=24) (actual time=0.008..0.009 rows=1 loops=95,193)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
37. 0.526 1.231 ↑ 1.0 2,101 1

Hash (cost=148.03..148.03 rows=2,101 width=48) (actual time=1.231..1.231 rows=2,101 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 200kB
38. 0.705 0.705 ↑ 1.0 2,101 1

Seq Scan on doctors doc (cost=0.00..148.03 rows=2,101 width=48) (actual time=0.012..0.705 rows=2,101 loops=1)

39. 0.018 0.041 ↑ 1.0 60 1

Hash (cost=2.80..2.80 rows=60 width=21) (actual time=0.041..0.041 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
40. 0.023 0.023 ↑ 1.0 60 1

Seq Scan on department dept (cost=0.00..2.80 rows=60 width=21) (actual time=0.012..0.023 rows=60 loops=1)

41. 36.130 90.965 ↑ 1.0 135,652 1

Hash (cost=6,207.56..6,207.56 rows=135,652 width=39) (actual time=90.965..90.965 rows=135,652 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11591kB
42. 54.835 54.835 ↑ 1.0 135,652 1

Seq Scan on referral ref (cost=0.00..6,207.56 rows=135,652 width=39) (actual time=0.523..54.835 rows=135,652 loops=1)

43.          

SubPlan (for Hash Left Join)

44. 95.193 4,759.650 ↑ 1.0 1 95,193

Aggregate (cost=38.84..38.87 rows=1 width=32) (actual time=0.050..0.050 rows=1 loops=95,193)

45. 4,664.457 4,664.457 ↓ 3.0 3 95,193

Index Scan using patient_registration_mr_no_index on patient_registration pr1 (cost=0.43..38.83 rows=1 width=15) (actual time=0.043..0.049 rows=3 loops=95,193)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
  • Filter: ((reg_date <= pr.reg_date) AND (reg_date >= (pr.reg_date - 31)))
  • Rows Removed by Filter: 24
Planning time : 42.744 ms
Execution time : 159,389.000 ms