explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 22mZ

Settings
# exclusive inclusive rows x rows loops node
1. 9,543.237 1,065,255.349 ↓ 18.8 245,765 1

Merge Left Join (cost=5,913,558.29..8,873,969.37 rows=13,045 width=956) (actual time=608,923.306..1,065,255.349 rows=245,765 loops=1)

  • Merge Cond: ((bc.charge_id)::text = (ipa_1.charge_id)::text)
2. 7,229.171 479,898.553 ↓ 18.8 245,765 1

Sort (cost=3,019,111.03..3,019,143.64 rows=13,045 width=527) (actual time=474,022.273..479,898.553 rows=245,765 loops=1)

  • Sort Key: bc.charge_id
  • Sort Method: external merge Disk: 82824kB
3. 27,807.123 472,669.382 ↓ 18.8 245,765 1

Hash Right Join (cost=1,821,696.47..3,018,219.33 rows=13,045 width=527) (actual time=400,977.929..472,669.382 rows=245,765 loops=1)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
4. 65,011.753 65,011.753 ↑ 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=35.601..65,011.753 rows=34,819,464 loops=1)

5. 476.927 379,850.506 ↓ 15.5 202,395 1

Hash (cost=1,821,533.40..1,821,533.40 rows=13,045 width=513) (actual time=379,850.506..379,850.506 rows=202,395 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 4 (originally 1) Memory Usage: 19969kB
6. 48.593 379,373.579 ↓ 15.5 202,395 1

Gather (cost=6,395.63..1,821,533.40 rows=13,045 width=513) (actual time=956.855..379,373.579 rows=202,395 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 58.846 379,324.986 ↓ 12.4 67,465 3 / 3

Hash Left Join (cost=5,395.63..1,819,228.90 rows=5,435 width=513) (actual time=890.802..379,324.986 rows=67,465 loops=3)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
8. 63.011 378,778.641 ↓ 12.4 67,465 3 / 3

Hash Left Join (cost=205.77..1,814,024.78 rows=5,435 width=488) (actual time=401.907..378,778.641 rows=67,465 loops=3)

  • Hash Cond: ((doc.dept_id)::text = (dept.dept_id)::text)
9. 98.004 378,715.590 ↓ 12.4 67,465 3 / 3

Hash Left Join (cost=203.44..1,814,007.16 rows=5,435 width=483) (actual time=401.843..378,715.590 rows=67,465 loops=3)

  • Hash Cond: ((pr.doctor)::text = (doc.doctor_id)::text)
10. 87.757 378,556.927 ↓ 12.4 67,465 3 / 3

Nested Loop Left Join (cost=17.93..1,813,807.35 rows=5,435 width=451) (actual time=341.064..378,556.927 rows=67,465 loops=3)

11. 113.650 370,779.908 ↓ 12.4 67,450 3 / 3

Nested Loop Left Join (cost=17.37..1,776,112.32 rows=5,435 width=443) (actual time=305.886..370,779.908 rows=67,450 loops=3)

12. 109.720 365,076.678 ↓ 12.4 67,344 3 / 3

Nested Loop Left Join (cost=16.81..1,322,726.29 rows=5,435 width=403) (actual time=305.858..365,076.678 rows=67,344 loops=3)

13. 117.256 361,786.531 ↓ 9.0 28,397 3 / 3

Nested Loop (cost=16.25..1,060,453.22 rows=3,138 width=363) (actual time=305.801..361,786.531 rows=28,397 loops=3)

14. 52.123 251,855.818 ↓ 8.9 31,802 3 / 3

Nested Loop (cost=15.68..1,036,912.29 rows=3,554 width=357) (actual time=293.245..251,855.818 rows=31,802 loops=3)

15. 36.231 124,981.795 ↓ 10.4 7,767 3 / 3

Nested Loop (cost=15.12..497,755.58 rows=745 width=329) (actual time=253.744..124,981.795 rows=7,767 loops=3)

16. 37.268 88,002.924 ↓ 11.2 7,509 3 / 3

Nested Loop Left Join (cost=14.55..492,802.82 rows=669 width=315) (actual time=226.170..88,002.924 rows=7,509 loops=3)

17. 30.440 38,108.109 ↓ 11.2 7,509 3 / 3

Nested Loop Left Join (cost=14.12..488,237.58 rows=669 width=151) (actual time=152.614..38,108.109 rows=7,509 loops=3)

18. 50.671 10,769.861 ↓ 11.2 7,508 3 / 3

Hash Join (cost=13.56..432,376.29 rows=669 width=111) (actual time=128.655..10,769.861 rows=7,508 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
19. 10,710.073 10,710.073 ↑ 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=64.714..10,710.073 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.009 9.117 ↑ 1.0 1 3 / 3

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

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

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

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

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis (cost=0.56..83.41 rows=9 width=56) (actual time=3.428..3.637 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. 49,857.547 49,857.547 ↑ 1.0 1 22,526 / 3

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

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

Index Scan using bill_visit_id_idx on bill b (cost=0.56..7.39 rows=1 width=29) (actual time=4.705..4.920 rows=1 loops=22,526)

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

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

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

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bcc (cost=0.56..6.61 rows=1 width=16) (actual time=3.452..3.453 rows=1 loops=95,407)

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

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_1 (cost=0.56..83.41 rows=17 width=56) (actual time=0.111..0.112 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. 5,589.580 5,589.580 ↓ 0.0 0 202,033 / 3

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis mrd_diagnosis_2 (cost=0.56..83.41 rows=1 width=56) (actual time=0.083..0.083 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. 7,689.262 7,689.262 ↑ 1.0 1 202,349 / 3

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pip (cost=0.56..6.93 rows=1 width=24) (actual time=0.113..0.114 rows=1 loops=202,349)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
30. 0.712 60.659 ↓ 1.0 2,024 3 / 3

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

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

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

32. 0.017 0.040 ↑ 1.0 59 3 / 3

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

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

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

34. 52.574 487.499 ↑ 1.0 135,638 3 / 3

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

  • Buckets: 262144 Batches: 1 Memory Usage: 11590kB
35. 434.925 434.925 ↑ 1.0 135,638 3 / 3

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

36. 10,459.713 158,504.589 ↓ 3.4 24,056,192 1

Finalize GroupAggregate (cost=2,894,447.25..4,824,071.46 rows=6,979,714 width=14) (actual time=90,126.637..158,504.589 rows=24,056,192 loops=1)

  • Group Key: ipa_1.charge_id
37. 33,763.827 148,044.876 ↓ 2.0 27,555,479 1

Gather Merge (cost=2,894,447.25..4,684,477.18 rows=13,959,428 width=14) (actual time=90,126.582..148,044.876 rows=27,555,479 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
38. 5,337.004 114,281.049 ↓ 1.3 9,186,071 3 / 3

Partial GroupAggregate (cost=2,893,447.23..3,072,212.79 rows=6,979,714 width=14) (actual time=89,949.549..114,281.049 rows=9,186,071 loops=3)

  • Group Key: ipa_1.charge_id
39. 71,867.935 108,944.045 ↑ 1.4 10,665,954 3 / 3

Sort (cost=2,893,447.23..2,929,770.04 rows=14,529,123 width=14) (actual time=89,949.535..108,944.045 rows=10,665,954 loops=3)

  • Sort Key: ipa_1.charge_id
  • Sort Method: external merge Disk: 302336kB
40. 3,240.343 37,076.110 ↑ 1.3 11,606,488 3 / 3

Hash Left Join (cost=16,342.37..916,729.98 rows=14,529,123 width=14) (actual time=708.083..37,076.110 rows=11,606,488 loops=3)

  • Hash Cond: (ipa_1.remittance_id = ir.remittance_id)
41. 33,139.505 33,139.505 ↑ 1.3 11,606,488 3 / 3

Parallel Seq Scan on insurance_payment_allocation ipa_1 (cost=0.00..862,248.23 rows=14,529,123 width=14) (actual time=9.416..33,139.505 rows=11,606,488 loops=3)

42. 111.923 696.262 ↑ 1.0 370,283 3 / 3

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

  • Buckets: 524288 Batches: 1 Memory Usage: 18561kB
43. 584.339 584.339 ↑ 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.036..584.339 rows=370,283 loops=3)

44.          

SubPlan (for Merge Left Join)

45. 737.295 417,308.970 ↑ 1.0 1 245,765

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

46. 416,571.675 416,571.675 ↓ 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=1.216..1.695 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