explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w7Ab

Settings
# exclusive inclusive rows x rows loops node
1. 4,321.666 667,533.615 ↓ 28.9 1,033,575 1

Sort (cost=3,564,172.52..3,564,261.78 rows=35,704 width=1,662) (actual time=666,881.322..667,533.615 rows=1,033,575 loops=1)

  • Sort Key: ((b.open_date)::date)
  • Sort Method: external merge Disk: 295888kB
2. 216.596 663,211.949 ↓ 28.9 1,033,575 1

Append (cost=1,237.90..3,561,115.58 rows=35,704 width=1,662) (actual time=45.720..663,211.949 rows=1,033,575 loops=1)

3. 0.000 326,276.290 ↓ 26.6 949,494 1

Gather (cost=1,237.90..1,406,840.15 rows=35,634 width=342) (actual time=45.719..326,276.290 rows=949,494 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 1,226.950 327,280.691 ↓ 21.3 316,498 3

Hash Left Join (cost=237.90..1,401,497.27 rows=14,848 width=342) (actual time=65.769..327,280.691 rows=316,498 loops=3)

  • Hash Cond: ((pr.doctor)::text = (dc.doctor_id)::text)
5. 439.260 326,051.185 ↓ 21.3 316,498 3

Hash Left Join (cost=54.76..1,400,718.26 rows=14,848 width=245) (actual time=63.157..326,051.185 rows=316,498 loops=3)

  • Hash Cond: ((pr.dept_name)::text = (dep.dept_id)::text)
6. 294,829.188 325,611.808 ↓ 21.3 316,498 3

Nested Loop (cost=52.43..1,400,674.17 rows=14,848 width=240) (actual time=63.003..325,611.808 rows=316,498 loops=3)

7. 72.782 30,777.392 ↓ 16.8 56,333 3

Hash Left Join (cost=51.87..465,756.83 rows=3,352 width=179) (actual time=43.292..30,777.392 rows=56,333 loops=3)

  • Hash Cond: ((hcm.state_id)::text = (stm.state_id)::text)
8. 86.070 30,704.298 ↓ 16.8 56,333 3

Hash Left Join (cost=39.84..465,735.89 rows=3,352 width=178) (actual time=42.958..30,704.298 rows=56,333 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
9. 95.058 30,618.192 ↓ 16.8 56,333 3

Hash Left Join (cost=38.36..465,723.84 rows=3,352 width=182) (actual time=42.901..30,618.192 rows=56,333 loops=3)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
10. 2,710.377 30,522.600 ↓ 16.8 56,333 3

Nested Loop (cost=10.27..465,686.91 rows=3,352 width=166) (actual time=42.260..30,522.600 rows=56,333 loops=3)

11. 23,317.951 27,812.177 ↓ 16.8 56,333 3

Nested Loop (cost=9.84..455,393.02 rows=3,352 width=133) (actual time=41.150..27,812.177 rows=56,333 loops=3)

12. 1,676.808 4,494.186 ↓ 19.7 567,319 3

Hash Join (cost=9.29..358,079.55 rows=28,778 width=81) (actual time=0.921..4,494.186 rows=567,319 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
13. 2,817.300 2,817.300 ↑ 1.2 2,351,289 3

Parallel Seq Scan on patient_registration pr (cost=0.00..350,044.97 rows=2,935,297 width=63) (actual time=0.660..2,817.300 rows=2,351,289 loops=3)

14. 0.006 0.078 ↑ 1.0 1 3

Hash (cost=9.28..9.28 rows=1 width=26) (actual time=0.078..0.078 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.072 0.072 ↑ 1.0 1 3

Seq Scan on hospital_center_master hcm (cost=0.00..9.28 rows=1 width=26) (actual time=0.052..0.072 rows=1 loops=3)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 34
16. 0.040 0.040 ↓ 0.0 0 1,701,958

Index Scan using bill_visit_id_idx on bill b (cost=0.56..3.37 rows=1 width=67) (actual time=0.040..0.040 rows=0 loops=1,701,958)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((finalized_date)::date >= '2019-05-01'::date) AND ((finalized_date)::date <= '2019-07-31'::date))
  • Rows Removed by Filter: 1
17. 0.046 0.046 ↑ 1.0 1 169,000

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..3.07 rows=1 width=48) (actual time=0.046..0.046 rows=1 loops=169,000)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
18. 0.269 0.534 ↑ 1.0 715 3

Hash (cost=19.15..19.15 rows=715 width=32) (actual time=0.534..0.534 rows=715 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
19. 0.265 0.265 ↑ 1.0 715 3

Seq Scan on insurance_company_master icm (cost=0.00..19.15 rows=715 width=32) (actual time=0.032..0.265 rows=715 loops=3)

20. 0.014 0.036 ↑ 1.0 21 3

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.036..0.036 rows=21 loops=3)

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

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.018..0.022 rows=21 loops=3)

22. 0.115 0.312 ↑ 1.0 357 3

Hash (cost=7.57..7.57 rows=357 width=15) (actual time=0.312..0.312 rows=357 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
23. 0.197 0.197 ↑ 1.0 357 3

Seq Scan on state_master stm (cost=0.00..7.57 rows=357 width=15) (actual time=0.019..0.197 rows=357 loops=3)

24. 5.228 5.228 ↑ 28.3 6 169,000

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..277.21 rows=170 width=75) (actual time=1.539..5.228 rows=6 loops=169,000)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHCMED,PHCRET}'::text[])))
  • Rows Removed by Filter: 1
25. 0.067 0.117 ↑ 1.0 59 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
26. 0.050 0.050 ↑ 1.0 59 3

Seq Scan on department dep (cost=0.00..1.59 rows=59 width=21) (actual time=0.033..0.050 rows=59 loops=3)

27. 0.999 2.556 ↑ 1.0 1,873 3

Hash (cost=159.73..159.73 rows=1,873 width=30) (actual time=2.556..2.556 rows=1,873 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 131kB
28. 1.557 1.557 ↑ 1.0 1,873 3

Seq Scan on doctors dc (cost=0.00..159.73 rows=1,873 width=30) (actual time=0.037..1.557 rows=1,873 loops=3)

29. 34.286 336,719.063 ↓ 1,201.2 84,081 1

Subquery Scan on *SELECT* 2 (cost=5,710.95..2,153,919.09 rows=70 width=384) (actual time=235,552.319..336,719.063 rows=84,081 loops=1)

30. 100.937 336,684.777 ↓ 1,201.2 84,081 1

Gather (cost=5,710.95..2,153,918.39 rows=70 width=368) (actual time=235,552.316..336,684.777 rows=84,081 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
31. 391.163 336,583.840 ↓ 966.4 28,027 3

Nested Loop Left Join (cost=4,710.95..2,152,909.86 rows=29 width=368) (actual time=222,422.598..336,583.840 rows=28,027 loops=3)

32. 31.220 336,192.668 ↓ 966.4 28,027 3

Hash Left Join (cost=4,710.67..2,152,820.56 rows=29 width=272) (actual time=222,422.418..336,192.668 rows=28,027 loops=3)

  • Hash Cond: ((pr_1.dept_name)::text = (dep_1.dept_id)::text)
33. 36.219 336,161.382 ↓ 966.4 28,027 3

Hash Left Join (cost=4,708.34..2,152,818.16 rows=29 width=267) (actual time=222,422.325..336,161.382 rows=28,027 loops=3)

  • Hash Cond: ((pd_1.salutation)::text = (sm_1.salutation_id)::text)
34. 52.133 336,125.106 ↓ 966.4 28,027 3

Hash Join (cost=4,706.87..2,152,816.60 rows=29 width=271) (actual time=222,422.219..336,125.106 rows=28,027 loops=3)

  • Hash Cond: (pr_1.center_id = hcm_1.center_id)
35. 135.866 336,072.705 ↓ 30.6 90,705 3

Hash Join (cost=4,691.26..2,152,789.59 rows=2,962 width=252) (actual time=149,371.645..336,072.705 rows=90,705 loops=3)

  • Hash Cond: (ssd.medicine_id = sid.medicine_id)
36. 30,684.226 335,868.872 ↓ 30.6 90,705 3

Nested Loop (cost=30.50..2,148,121.06 rows=2,962 width=198) (actual time=149,303.225..335,868.872 rows=90,705 loops=3)

37. 69.757 305,183.890 ↓ 30.8 40,345 3

Hash Left Join (cost=30.07..2,137,571.61 rows=1,308 width=173) (actual time=149,298.453..305,183.890 rows=40,345 loops=3)

  • Hash Cond: ((pr_1.primary_insurance_co)::text = (icm_1.insurance_co_id)::text)
38. 3,547.349 305,113.276 ↓ 30.8 40,345 3

Nested Loop (cost=1.99..2,137,540.06 rows=1,308 width=157) (actual time=149,297.439..305,113.276 rows=40,345 loops=3)

39. 1,555.582 301,565.841 ↓ 30.8 40,345 3

Nested Loop (cost=1.56..2,133,278.61 rows=1,308 width=124) (actual time=149,297.243..301,565.841 rows=40,345 loops=3)

40. 45,379.040 300,010.224 ↓ 30.8 40,345 3

Nested Loop (cost=1.12..2,128,675.50 rows=1,308 width=76) (actual time=149,296.468..300,010.224 rows=40,345 loops=3)

41. 254,310.001 254,631.058 ↓ 28.5 356,612 3

Nested Loop (cost=0.56..2,081,797.84 rows=12,507 width=23) (actual time=0.867..254,631.058 rows=356,612 loops=3)

42. 320.346 320.346 ↑ 1.2 356,612 3

Parallel Seq Scan on store_sales_main ssm (cost=0.00..28,378.48 rows=445,448 width=19) (actual time=0.232..320.346 rows=356,612 loops=3)

43. 0.711 0.711 ↑ 1.0 1 1,069,836

Index Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..4.61 rows=1 width=24) (actual time=0.711..0.711 rows=1 loops=1,069,836)

  • Index Cond: ((charge_id)::text = (ssm.charge_id)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text = ANY ('{PHCMED,PHCRET}'::text[])))
44. 0.126 0.126 ↓ 0.0 0 1,069,836

Index Scan using bill_pkey on bill b_1 (cost=0.56..3.75 rows=1 width=67) (actual time=0.126..0.126 rows=0 loops=1,069,836)

  • Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((finalized_date)::date >= '2019-05-01'::date) AND ((finalized_date)::date <= '2019-07-31'::date))
  • Rows Removed by Filter: 1
45. 0.035 0.035 ↑ 1.0 1 121,035

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.43..3.52 rows=1 width=63) (actual time=0.035..0.035 rows=1 loops=121,035)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
46. 0.086 0.086 ↑ 1.0 1 121,035

Index Scan using patient_details_pkey on patient_details pd_1 (cost=0.43..3.26 rows=1 width=48) (actual time=0.086..0.086 rows=1 loops=121,035)

  • Index Cond: ((mr_no)::text = (pr_1.mr_no)::text)
47. 0.479 0.857 ↑ 1.0 715 3

Hash (cost=19.15..19.15 rows=715 width=32) (actual time=0.857..0.857 rows=715 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
48. 0.378 0.378 ↑ 1.0 715 3

Seq Scan on insurance_company_master icm_1 (cost=0.00..19.15 rows=715 width=32) (actual time=0.028..0.378 rows=715 loops=3)

49. 0.756 0.756 ↑ 3.5 2 121,035

Index Scan using pharmacy_medicine_sales_sale_id_idx on store_sales_details ssd (cost=0.43..8.00 rows=7 width=43) (actual time=0.445..0.756 rows=2 loops=121,035)

  • Index Cond: ((sale_id)::text = (ssm.sale_id)::text)
50. 17.381 67.967 ↑ 1.0 56,478 3

Hash (cost=3,954.78..3,954.78 rows=56,478 width=62) (actual time=67.967..67.967 rows=56,478 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 5819kB
51. 50.586 50.586 ↑ 1.0 56,478 3

Seq Scan on store_item_details sid (cost=0.00..3,954.78 rows=56,478 width=62) (actual time=0.153..50.586 rows=56,478 loops=3)

52. 0.011 0.268 ↑ 1.0 1 3

Hash (cost=15.60..15.60 rows=1 width=27) (actual time=0.268..0.268 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.008 0.257 ↑ 1.0 1 3

Nested Loop Left Join (cost=0.27..15.60 rows=1 width=27) (actual time=0.239..0.257 rows=1 loops=3)

54. 0.073 0.073 ↑ 1.0 1 3

Seq Scan on hospital_center_master hcm_1 (cost=0.00..9.28 rows=1 width=26) (actual time=0.057..0.073 rows=1 loops=3)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 34
55. 0.176 0.176 ↑ 1.0 1 3

Index Scan using state_master_pkey on state_master stm_1 (cost=0.27..6.29 rows=1 width=15) (actual time=0.176..0.176 rows=1 loops=3)

  • Index Cond: ((state_id)::text = (hcm_1.state_id)::text)
56. 0.019 0.057 ↑ 1.0 21 3

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.057..0.057 rows=21 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.038 0.038 ↑ 1.0 21 3

Seq Scan on salutation_master sm_1 (cost=0.00..1.21 rows=21 width=14) (actual time=0.030..0.038 rows=21 loops=3)

58. 0.035 0.066 ↑ 1.0 59 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
59. 0.031 0.031 ↑ 1.0 59 3

Seq Scan on department dep_1 (cost=0.00..1.59 rows=59 width=21) (actual time=0.013..0.031 rows=59 loops=3)

60. 0.009 0.009 ↑ 1.0 1 84,081

Index Scan using doctors_pkey on doctors dc_1 (cost=0.28..3.04 rows=1 width=30) (actual time=0.009..0.009 rows=1 loops=84,081)

  • Index Cond: ((doctor_id)::text = (pr_1.doctor)::text)
Planning time : 13.191 ms
Execution time : 667,593.322 ms