explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rMXr

Settings
# exclusive inclusive rows x rows loops node
1. 2,705.818 108,035.883 ↓ 18.8 245,765 1

Hash Left Join (cost=1,881,588.88..4,021,600.24 rows=13,045 width=956) (actual time=72,720.169..108,035.883 rows=245,765 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
2. 24,762.525 98,627.145 ↓ 18.8 245,765 1

Hash Right Join (cost=1,876,399.02..3,072,921.88 rows=13,045 width=496) (actual time=72,652.329..98,627.145 rows=245,765 loops=1)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
3. 24,212.060 24,212.060 ↑ 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=24) (actual time=0.608..24,212.060 rows=34,819,464 loops=1)

4. 360.228 49,652.560 ↓ 15.5 202,395 1

Hash (cost=1,876,235.96..1,876,235.96 rows=13,045 width=492) (actual time=49,652.560..49,652.560 rows=202,395 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 19969kB
5. 686.092 49,292.332 ↓ 15.5 202,395 1

Gather (cost=17,548.70..1,876,235.96 rows=13,045 width=492) (actual time=435.290..49,292.332 rows=202,395 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 50.979 48,606.240 ↓ 12.4 67,465 3 / 3

Hash Left Join (cost=16,548.70..1,873,931.46 rows=5,435 width=492) (actual time=348.066..48,606.240 rows=67,465 loops=3)

  • Hash Cond: ((doc.dept_id)::text = (dept.dept_id)::text)
7. 57.505 48,555.205 ↓ 12.4 67,465 3 / 3

Hash Left Join (cost=16,546.37..1,873,913.84 rows=5,435 width=487) (actual time=347.977..48,555.205 rows=67,465 loops=3)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
8. 67.290 48,495.676 ↓ 12.4 67,465 3 / 3

Hash Left Join (cost=16,360.85..1,873,714.03 rows=5,435 width=455) (actual time=345.830..48,495.676 rows=67,465 loops=3)

  • Hash Cond: (cr.latest_remittance_id = ir.remittance_id)
9. 55.601 48,213.156 ↓ 12.4 67,465 3 / 3

Nested Loop Left Join (cost=18.49..1,857,357.39 rows=5,435 width=455) (actual time=128.632..48,213.156 rows=67,465 loops=3)

10. 62.946 22,655.785 ↓ 12.4 67,465 3 / 3

Nested Loop Left Join (cost=17.93..1,821,408.32 rows=5,435 width=465) (actual time=104.731..22,655.785 rows=67,465 loops=3)

11. 92.266 21,783.443 ↓ 12.4 67,450 3 / 3

Nested Loop Left Join (cost=17.37..1,783,582.27 rows=5,435 width=457) (actual time=104.693..21,783.443 rows=67,450 loops=3)

12. 52.491 20,815.701 ↓ 12.4 67,344 3 / 3

Nested Loop Left Join (cost=16.81..1,327,676.49 rows=5,435 width=417) (actual time=104.673..20,815.701 rows=67,344 loops=3)

13. 42.324 20,365.657 ↓ 9.0 28,397 3 / 3

Nested Loop (cost=16.25..1,063,948.60 rows=3,138 width=377) (actual time=104.645..20,365.657 rows=28,397 loops=3)

14. 20.118 19,878.100 ↓ 8.9 31,802 3 / 3

Nested Loop (cost=15.68..1,040,275.55 rows=3,554 width=357) (actual time=104.574..19,878.100 rows=31,802 loops=3)

15. 11.491 19,632.749 ↓ 10.4 7,767 3 / 3

Nested Loop (cost=15.12..498,090.57 rows=745 width=329) (actual time=104.521..19,632.749 rows=7,767 loops=3)

16. 14.520 19,478.593 ↓ 11.2 7,509 3 / 3

Nested Loop Left Join (cost=14.55..493,128.31 rows=669 width=315) (actual time=104.453..19,478.593 rows=7,509 loops=3)

17. 13.650 19,321.408 ↓ 11.2 7,509 3 / 3

Nested Loop Left Join (cost=14.12..488,547.74 rows=669 width=151) (actual time=104.418..19,321.408 rows=7,509 loops=3)

18. 33.664 19,142.575 ↓ 11.2 7,508 3 / 3

Hash Join (cost=13.56..432,376.29 rows=669 width=111) (actual time=104.351..19,142.575 rows=7,508 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
19. 19,108.820 19,108.820 ↑ 1.0 28,234 3 / 3

Parallel Seq Scan on patient_registration pr (cost=0.00..432,277.97 rows=29,451 width=88) (actual time=42.657..19,108.820 rows=28,234 loops=3)

  • Filter: ((reg_date >= '2019-01-01'::date) AND (reg_date <= '2019-01-10'::date))
  • Rows Removed by Filter: 2697120
20. 0.006 0.091 ↑ 1.0 1 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.085 0.085 ↑ 1.0 1 3 / 3

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

  • Filter: ((center_name)::text = 'NMCAbuDhabi'::text)
  • Rows Removed by Filter: 43
22. 165.183 165.183 ↑ 9.0 1 22,525 / 3

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis (cost=0.56..83.87 rows=9 width=56) (actual time=0.020..0.022 rows=1 loops=22,525)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 2
23. 142.665 142.665 ↑ 1.0 1 22,526 / 3

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..6.85 rows=1 width=164) (actual time=0.019..0.019 rows=1 loops=22,526)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
24. 142.665 142.665 ↑ 1.0 1 22,526 / 3

Index Scan using bill_visit_id_idx on bill b (cost=0.56..7.41 rows=1 width=29) (actual time=0.018..0.019 rows=1 loops=22,526)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
25. 225.233 225.233 ↑ 58.0 4 23,300 / 3

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..725.45 rows=232 width=56) (actual time=0.019..0.029 rows=4 loops=23,300)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
26. 445.233 445.233 ↑ 1.0 1 95,407 / 3

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..6.65 rows=1 width=30) (actual time=0.014..0.014 rows=1 loops=95,407)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
27. 397.553 397.553 ↑ 8.5 2 85,190 / 3

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_1 (cost=0.56..83.87 rows=17 width=56) (actual time=0.013..0.014 rows=2 loops=85,190)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'S'::bpchar)
  • Rows Removed by Filter: 1
28. 875.476 875.476 ↓ 0.0 0 202,033 / 3

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_2 (cost=0.56..83.87 rows=1 width=56) (actual time=0.013..0.013 rows=0 loops=202,033)

  • Index Cond: ((pr.patient_id)::text = (visit_id)::text)
  • Filter: (diag_type = 'R'::bpchar)
  • Rows Removed by Filter: 5
29. 809.396 809.396 ↑ 1.0 1 202,349 / 3

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..6.95 rows=1 width=24) (actual time=0.011..0.012 rows=1 loops=202,349)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
30. 25,501.770 25,501.770 ↑ 1.0 1 202,395 / 3

Index Scan using claim_reconciliation_pkey on claim_reconciliation cr (cost=0.56..6.61 rows=1 width=18) (actual time=0.378..0.378 rows=1 loops=202,395)

  • Index Cond: ((claim_id)::text = (bcc.claim_id)::text)
31. 117.101 215.230 ↑ 1.0 370,283 3 / 3

Hash (cost=11,713.83..11,713.83 rows=370,283 width=8) (actual time=215.230..215.230 rows=370,283 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 18561kB
32. 98.129 98.129 ↑ 1.0 370,283 3 / 3

Seq Scan on insurance_remittance ir (cost=0.00..11,713.83 rows=370,283 width=8) (actual time=0.039..98.129 rows=370,283 loops=3)

33. 0.770 2.024 ↓ 1.0 2,024 3 / 3

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

  • Buckets: 2048 Batches: 1 Memory Usage: 178kB
34. 1.254 1.254 ↓ 1.0 2,024 3 / 3

Seq Scan on doctors doc (cost=0.00..160.23 rows=2,023 width=48) (actual time=0.051..1.254 rows=2,024 loops=3)

35. 0.024 0.056 ↑ 1.0 59 3 / 3

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.056..0.056 rows=59 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
36. 0.032 0.032 ↑ 1.0 59 3 / 3

Seq Scan on department dept (cost=0.00..1.59 rows=59 width=21) (actual time=0.021..0.032 rows=59 loops=3)

37. 36.993 67.265 ↑ 1.0 135,638 1

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

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

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

39.          

SubPlan (for Hash Left Join)

40. 491.530 6,635.655 ↑ 1.0 1 245,765

Aggregate (cost=72.21..72.22 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=245,765)

41. 6,144.125 6,144.125 ↓ 4.0 4 245,765

Index Scan using patient_registration_mr_no_index on patient_registration pr1 (cost=0.43..72.21 rows=1 width=15) (actual time=0.017..0.025 rows=4 loops=245,765)

  • 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: 14