explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ROY1

Settings
# exclusive inclusive rows x rows loops node
1. 10,684.623 255,454.051 ↓ 16.8 860,064 1

Hash Left Join (cost=4,369,664.88..10,096,979.69 rows=51,165 width=984) (actual time=96,100.097..255,454.051 rows=860,064 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
  • Rows Removed by Filter: 14
2. 637.861 134,588.185 ↓ 16.8 860,064 1

Hash Left Join (cost=4,364,475.02..6,391,246.38 rows=51,165 width=524) (actual time=95,986.371..134,588.185 rows=860,064 loops=1)

  • Hash Cond: ((doc.dept_id)::text = (dept.dept_id)::text)
3. 824.717 133,950.295 ↓ 16.8 860,064 1

Hash Left Join (cost=4,364,472.69..6,391,100.10 rows=51,165 width=519) (actual time=95,986.324..133,950.295 rows=860,064 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
4. 1,097.253 133,087.353 ↓ 16.8 860,064 1

Nested Loop Left Join (cost=4,364,287.18..6,390,780.00 rows=51,165 width=487) (actual time=95,948.075..133,087.353 rows=860,064 loops=1)

5. 1,573.994 118,252.692 ↓ 16.8 858,588 1

Hash Left Join (cost=4,364,286.62..6,049,190.99 rows=51,165 width=479) (actual time=95,946.252..118,252.692 rows=858,588 loops=1)

  • Hash Cond: (ipa.remittance_id = ir.remittance_id)
6. 20,698.517 116,486.882 ↓ 16.8 858,588 1

Hash Right Join (cost=4,346,135.25..6,022,800.31 rows=51,165 width=475) (actual time=92,005.720..116,486.882 rows=858,588 loops=1)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
7. 9,113.450 9,113.450 ↑ 1.0 34,819,464 1

Seq Scan on insurance_payment_allocation ipa (cost=0.00..1,065,655.96 rows=34,869,896 width=28) (actual time=0.017..9,113.450 rows=34,819,464 loops=1)

8. 746.351 86,674.915 ↓ 13.8 703,551 1

Hash (cost=4,342,397.69..4,342,397.69 rows=51,165 width=467) (actual time=86,674.915..86,674.915 rows=703,551 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 2) Memory Usage: 19969kB
9. 834.315 85,928.564 ↓ 13.8 703,551 1

Hash Right Join (cost=3,711,399.13..4,342,397.69 rows=51,165 width=467) (actual time=84,721.720..85,928.564 rows=703,551 loops=1)

  • Hash Cond: ((mrd_diagnosis_2.visit_id)::text = (pr.patient_id)::text)
10. 0.000 2,312.030 ↑ 1.0 272,686 1

Gather (cost=1,000.00..622,726.76 rows=275,097 width=56) (actual time=3.071..2,312.030 rows=272,686 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 2,323.716 2,323.716 ↑ 1.3 90,895 3 / 3

Parallel Seq Scan on mrd_diagnosis mrd_diagnosis_2 (cost=0.00..594,217.06 rows=114,624 width=56) (actual time=74.897..2,323.716 rows=90,895 loops=3)

  • Filter: (diag_type = 'R'::bpchar)
  • Rows Removed by Filter: 7381213
12. 745.560 82,782.219 ↓ 13.7 701,984 1

Hash (cost=3,706,910.57..3,706,910.57 rows=51,165 width=427) (actual time=82,782.219..82,782.219 rows=701,984 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 16 (originally 2) Memory Usage: 19969kB
13. 9,767.594 82,036.659 ↓ 13.7 701,984 1

Hash Right Join (cost=2,895,441.17..3,706,910.57 rows=51,165 width=427) (actual time=56,241.775..82,036.659 rows=701,984 loops=1)

  • Hash Cond: ((mrd_diagnosis_1.visit_id)::text = (pr.patient_id)::text)
14. 16,959.183 16,959.183 ↓ 1.0 14,332,750 1

Seq Scan on mrd_diagnosis mrd_diagnosis_1 (cost=0.00..757,742.75 rows=14,190,666 width=56) (actual time=0.467..16,959.183 rows=14,332,750 loops=1)

  • Filter: (diag_type = 'S'::bpchar)
  • Rows Removed by Filter: 8083576
15. 536.979 55,309.882 ↓ 9.6 284,435 1

Hash (cost=2,895,071.95..2,895,071.95 rows=29,538 width=387) (actual time=55,309.882..55,309.882 rows=284,435 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 8 (originally 1) Memory Usage: 19457kB
16. 54.013 54,772.903 ↓ 9.6 284,435 1

Gather (cost=1,016.25..2,895,071.95 rows=29,538 width=387) (actual time=10.982..54,772.903 rows=284,435 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 222.446 54,718.890 ↓ 7.7 94,812 3 / 3

Nested Loop (cost=16.25..2,891,118.15 rows=12,308 width=387) (actual time=40.405..54,718.890 rows=94,812 loops=3)

18. 100.156 41,211.524 ↓ 7.5 104,606 3 / 3

Nested Loop (cost=15.68..2,798,748.02 rows=13,940 width=357) (actual time=38.971..41,211.524 rows=104,606 loops=3)

19. 44.850 22,592.343 ↓ 8.5 24,725 3 / 3

Nested Loop (cost=15.12..684,757.41 rows=2,920 width=329) (actual time=37.384..22,592.343 rows=24,725 loops=3)

20. 42.837 16,539.993 ↓ 9.2 24,030 3 / 3

Nested Loop Left Join (cost=14.55..666,800.69 rows=2,625 width=315) (actual time=36.159..16,539.993 rows=24,030 loops=3)

21. 36.682 11,907.426 ↓ 9.2 24,030 3 / 3

Nested Loop Left Join (cost=14.12..649,637.90 rows=2,625 width=151) (actual time=34.648..11,907.426 rows=24,030 loops=3)

22. 100.676 5,647.406 ↓ 9.2 24,028 3 / 3

Hash Join (cost=13.56..432,623.96 rows=2,625 width=111) (actual time=33.260..5,647.406 rows=24,028 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
23. 5,546.234 5,546.234 ↑ 1.3 91,773 3 / 3

Parallel Seq Scan on patient_registration pr (cost=0.00..432,277.97 rows=115,512 width=88) (actual time=10.483..5,546.234 rows=91,773 loops=3)

  • Filter: ((reg_date >= '2019-01-01'::date) AND (reg_date <= '2019-01-31'::date))
  • Rows Removed by Filter: 2633581
24. 0.005 0.496 ↑ 1.0 1 3 / 3

Hash (cost=13.55..13.55 rows=1 width=31) (actual time=0.496..0.496 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.491 0.491 ↑ 1.0 1 3 / 3

Seq Scan on hospital_center_master hcm (cost=0.00..13.55 rows=1 width=31) (actual time=0.146..0.491 rows=1 loops=3)

  • Filter: ((center_name)::text = 'NMCAbuDhabi'::text)
  • Rows Removed by Filter: 43
26. 6,223.338 6,223.338 ↑ 9.0 1 72,085 / 3

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis (cost=0.56..82.58 rows=9 width=56) (actual time=0.202..0.259 rows=1 loops=72,085)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 2
27. 4,589.730 4,589.730 ↑ 1.0 1 72,090 / 3

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..6.54 rows=1 width=164) (actual time=0.191..0.191 rows=1 loops=72,090)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
28. 6,007.500 6,007.500 ↑ 1.0 1 72,090 / 3

Index Scan using bill_visit_id_idx on bill b (cost=0.56..6.83 rows=1 width=29) (actual time=0.242..0.250 rows=1 loops=72,090)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
29. 18,519.025 18,519.025 ↑ 58.0 4 74,175 / 3

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..721.65 rows=232 width=56) (actual time=0.254..0.749 rows=4 loops=74,175)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
30. 13,284.920 13,284.920 ↑ 1.0 1 313,817 / 3

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..6.62 rows=1 width=30) (actual time=0.126..0.127 rows=1 loops=313,817)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
31. 82.259 191.816 ↑ 1.0 370,283 1

Hash (cost=11,713.83..11,713.83 rows=370,283 width=12) (actual time=191.816..191.816 rows=370,283 loops=1)

  • Buckets: 524288 Batches: 2 Memory Usage: 10611kB
32. 109.557 109.557 ↑ 1.0 370,283 1

Seq Scan on insurance_remittance ir (cost=0.00..11,713.83 rows=370,283 width=12) (actual time=0.012..109.557 rows=370,283 loops=1)

33. 13,737.408 13,737.408 ↑ 1.0 1 858,588

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..6.67 rows=1 width=24) (actual time=0.016..0.016 rows=1 loops=858,588)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
34. 0.559 38.225 ↓ 1.0 2,024 1

Hash (cost=160.23..160.23 rows=2,023 width=48) (actual time=38.225..38.225 rows=2,024 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 178kB
35. 37.666 37.666 ↓ 1.0 2,024 1

Seq Scan on doctors doc (cost=0.00..160.23 rows=2,023 width=48) (actual time=0.332..37.666 rows=2,024 loops=1)

36. 0.013 0.029 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.028..0.029 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
37. 0.016 0.016 ↑ 1.0 59 1

Seq Scan on department dept (cost=0.00..1.59 rows=59 width=21) (actual time=0.008..0.016 rows=59 loops=1)

38. 37.395 93.051 ↑ 1.0 135,638 1

Hash (cost=3,494.38..3,494.38 rows=135,638 width=38) (actual time=93.051..93.051 rows=135,638 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11590kB
39. 55.656 55.656 ↑ 1.0 135,638 1

Seq Scan on referral ref (cost=0.00..3,494.38 rows=135,638 width=38) (actual time=0.374..55.656 rows=135,638 loops=1)

40.          

SubPlan (for Hash Left Join)

41. 1,720.128 110,088.192 ↑ 1.0 1 860,064

Aggregate (cost=72.21..72.22 rows=1 width=32) (actual time=0.128..0.128 rows=1 loops=860,064)

42. 108,368.064 108,368.064 ↓ 4.0 4 860,064

Index Scan using patient_registration_mr_no_index on patient_registration pr1 (cost=0.43..72.21 rows=1 width=15) (actual time=0.071..0.126 rows=4 loops=860,064)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
  • Filter: ((reg_date <= pr.reg_date) AND (reg_date >= (pr.reg_date - 31)))