explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KDtr

Settings
# exclusive inclusive rows x rows loops node
1. 119,502.151 641,596.929 ↓ 1.1 43,907,459 1

Sort (cost=64,681,997.45..64,785,755.22 rows=41,503,108 width=1,662) (actual time=583,965.351..641,596.929 rows=43,907,459 loops=1)

  • Sort Key: ((b.open_date)::date)
  • Sort Method: external merge Disk: 12195344kB
2. 3,285.019 522,094.778 ↓ 1.1 43,907,459 1

Append (cost=3,474,787.75..15,425,817.36 rows=41,503,108 width=1,662) (actual time=52,238.584..522,094.778 rows=43,907,459 loops=1)

3. 33,387.936 219,241.375 ↑ 1.0 41,002,715 1

Hash Left Join (cost=3,474,787.75..12,486,385.58 rows=41,433,435 width=345) (actual time=52,238.582..219,241.375 rows=41,002,715 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dc.doctor_id)::text)
4. 10,392.500 185,852.426 ↑ 1.0 41,002,715 1

Hash Left Join (cost=3,474,571.07..10,823,040.92 rows=41,433,435 width=249) (actual time=52,237.526..185,852.426 rows=41,002,715 loops=1)

  • Hash Cond: ((pr.dept_name)::text = (dep.dept_id)::text)
5. 11,114.813 175,459.890 ↑ 1.0 41,002,715 1

Hash Left Join (cost=3,474,566.32..10,692,801.89 rows=41,433,435 width=244) (actual time=52,237.476..175,459.890 rows=41,002,715 loops=1)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
6. 50,951.661 164,344.749 ↑ 1.0 41,002,715 1

Hash Join (cost=3,474,505.75..10,582,414.87 rows=41,433,435 width=227) (actual time=52,237.110..164,344.749 rows=41,002,715 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
7. 61,160.933 61,160.933 ↑ 1.0 42,655,645 1

Seq Scan on bill_charge bc (cost=0.00..3,968,757.10 rows=42,816,652 width=74) (actual time=0.038..61,160.933 rows=42,655,645 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHCMED,PHCRET}'::text[])))
  • Rows Removed by Filter: 3613714
8. 5,479.066 52,232.155 ↑ 1.0 8,961,258 1

Hash (cost=2,948,775.69..2,948,775.69 rows=9,398,525 width=167) (actual time=52,232.155..52,232.155 rows=8,961,258 loops=1)

  • Buckets: 524288 Batches: 32 Memory Usage: 57266kB
9. 2,184.528 46,753.089 ↑ 1.0 8,961,258 1

Hash Left Join (cost=1,356,317.94..2,948,775.69 rows=9,398,525 width=167) (actual time=10,749.736..46,753.089 rows=8,961,258 loops=1)

  • Hash Cond: ((hcm.state_id)::text = (stm.state_id)::text)
10. 1,879.680 44,567.541 ↑ 1.0 8,961,258 1

Hash Join (cost=1,356,291.62..2,923,258.86 rows=9,398,525 width=166) (actual time=10,748.682..44,567.541 rows=8,961,258 loops=1)

  • Hash Cond: (pr.center_id = hcm.center_id)
11. 2,028.309 42,687.805 ↑ 1.0 8,961,258 1

Hash Left Join (cost=1,356,284.75..2,892,221.52 rows=9,398,525 width=145) (actual time=10,748.584..42,687.805 rows=8,961,258 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
12. 9,597.292 40,659.459 ↑ 1.0 8,961,258 1

Hash Join (cost=1,356,282.44..2,853,618.11 rows=9,398,525 width=149) (actual time=10,748.473..40,659.459 rows=8,961,258 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
13. 9,946.558 28,795.338 ↑ 1.0 8,961,258 1

Hash Join (cost=1,046,631.69..2,159,425.14 rows=9,398,525 width=115) (actual time=8,471.974..28,795.338 rows=8,961,258 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
14. 10,383.206 10,383.206 ↓ 1.0 9,407,235 1

Seq Scan on bill b (cost=0.00..774,052.29 rows=9,398,525 width=67) (actual time=0.031..10,383.206 rows=9,407,235 loops=1)

  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 307881
15. 2,809.893 8,465.574 ↓ 1.0 8,731,975 1

Hash (cost=669,078.97..669,078.97 rows=8,731,099 width=63) (actual time=8,465.574..8,465.574 rows=8,731,975 loops=1)

  • Buckets: 1048576 Batches: 16 Memory Usage: 59151kB
16. 5,655.681 5,655.681 ↓ 1.0 8,731,975 1

Seq Scan on patient_registration pr (cost=0.00..669,078.97 rows=8,731,099 width=63) (actual time=0.019..5,655.681 rows=8,731,975 loops=1)

17. 867.792 2,266.829 ↓ 1.0 3,016,579 1

Hash (cost=182,180.48..182,180.48 rows=3,015,916 width=49) (actual time=2,266.829..2,266.829 rows=3,016,579 loops=1)

  • Buckets: 1048576 Batches: 8 Memory Usage: 37682kB
18. 1,399.037 1,399.037 ↓ 1.0 3,016,579 1

Seq Scan on patient_details pd (cost=0.00..182,180.48 rows=3,015,916 width=49) (actual time=0.021..1,399.037 rows=3,016,579 loops=1)

19. 0.018 0.037 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.037..0.037 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.019 0.019 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.63 rows=21 width=14) (actual time=0.014..0.019 rows=21 loops=1)

21. 0.009 0.056 ↑ 1.0 46 1

Hash (cost=5.38..5.38 rows=46 width=29) (actual time=0.055..0.056 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
22. 0.047 0.047 ↑ 1.0 46 1

Seq Scan on hospital_center_master hcm (cost=0.00..5.38 rows=46 width=29) (actual time=0.005..0.047 rows=46 loops=1)

23. 0.168 1.020 ↑ 1.0 357 1

Hash (cost=14.71..14.71 rows=357 width=15) (actual time=1.020..1.020 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
24. 0.852 0.852 ↑ 1.0 357 1

Seq Scan on state_master stm (cost=0.00..14.71 rows=357 width=15) (actual time=0.012..0.852 rows=357 loops=1)

25. 0.157 0.328 ↑ 1.0 761 1

Hash (cost=35.83..35.83 rows=761 width=33) (actual time=0.328..0.328 rows=761 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
26. 0.171 0.171 ↑ 1.0 761 1

Seq Scan on insurance_company_master icm (cost=0.00..35.83 rows=761 width=33) (actual time=0.024..0.171 rows=761 loops=1)

27. 0.021 0.036 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
28. 0.015 0.015 ↑ 1.0 60 1

Seq Scan on department dep (cost=0.00..2.80 rows=60 width=21) (actual time=0.010..0.015 rows=60 loops=1)

29. 0.444 1.013 ↑ 1.0 2,107 1

Hash (cost=148.21..148.21 rows=2,107 width=30) (actual time=1.013..1.013 rows=2,107 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 162kB
30. 0.569 0.569 ↑ 1.0 2,107 1

Seq Scan on doctors dc (cost=0.00..148.21 rows=2,107 width=30) (actual time=0.013..0.569 rows=2,107 loops=1)

31. 1,096.884 299,568.384 ↓ 41.7 2,904,744 1

Subquery Scan on *SELECT* 2 (cost=1,320.34..1,696,428.73 rows=69,673 width=389) (actual time=9.540..299,568.384 rows=2,904,744 loops=1)

32. 7,237.359 298,471.500 ↓ 41.7 2,904,744 1

Hash Left Join (cost=1,320.34..1,694,338.54 rows=69,673 width=373) (actual time=9.538..298,471.500 rows=2,904,744 loops=1)

  • Hash Cond: ((pr_1.doctor)::text = (dc_1.doctor_id)::text)
33. 2,196.993 291,233.131 ↓ 41.7 2,904,744 1

Hash Left Join (cost=1,103.65..1,691,325.20 rows=69,673 width=278) (actual time=8.489..291,233.131 rows=2,904,744 loops=1)

  • Hash Cond: ((pr_1.dept_name)::text = (dep_1.dept_id)::text)
34. 2,244.568 289,036.114 ↓ 41.7 2,904,744 1

Hash Left Join (cost=1,098.90..1,691,101.45 rows=69,673 width=273) (actual time=8.448..289,036.114 rows=2,904,744 loops=1)

  • Hash Cond: ((hcm_1.state_id)::text = (stm_1.state_id)::text)
35. 2,111.763 286,789.177 ↓ 41.7 2,904,744 1

Hash Join (cost=1,072.59..1,690,886.18 rows=69,673 width=272) (actual time=6.052..286,789.177 rows=2,904,744 loops=1)

  • Hash Cond: (pr_1.center_id = hcm_1.center_id)
36. 2,318.839 284,677.362 ↓ 41.7 2,904,744 1

Hash Left Join (cost=1,065.71..1,690,649.26 rows=69,673 width=251) (actual time=5.958..284,677.362 rows=2,904,744 loops=1)

  • Hash Cond: ((pd_1.salutation)::text = (sm_1.salutation_id)::text)
37. 1,563.632 282,358.490 ↓ 41.7 2,904,744 1

Nested Loop (cost=1,063.40..1,690,360.78 rows=69,673 width=255) (actual time=5.890..282,358.490 rows=2,904,744 loops=1)

38. 0.000 266,271.138 ↓ 41.7 2,904,744 1

Gather (cost=1,063.11..1,667,351.20 rows=69,673 width=200) (actual time=5.828..266,271.138 rows=2,904,744 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
39. 1,554.635 290,914.084 ↓ 33.4 968,248 3 / 3

Nested Loop (cost=63.11..1,659,383.90 rows=29,030 width=200) (actual time=3.769..290,914.084 rows=968,248 loops=3)

40. 592.961 258,223.505 ↓ 33.4 432,444 3 / 3

Hash Left Join (cost=62.68..1,643,162.56 rows=12,965 width=175) (actual time=2.961..258,223.505 rows=432,444 loops=3)

  • Hash Cond: ((pr_1.primary_insurance_co)::text = (icm_1.insurance_co_id)::text)
41. 379.863 257,629.913 ↓ 33.4 432,444 3 / 3

Nested Loop (cost=2.12..1,643,067.46 rows=12,965 width=158) (actual time=2.199..257,629.913 rows=432,444 loops=3)

42. 1,037.026 246,871.402 ↓ 33.4 432,444 3 / 3

Nested Loop (cost=1.69..1,636,665.86 rows=12,965 width=124) (actual time=1.987..246,871.402 rows=432,444 loops=3)

43. 807.383 236,320.615 ↓ 33.4 432,444 3 / 3

Nested Loop (cost=1.12..1,627,623.55 rows=12,965 width=76) (actual time=1.902..236,320.615 rows=432,444 loops=3)

44. 742.058 213,890.965 ↓ 32.3 432,445 3 / 3

Nested Loop (cost=0.56..1,606,968.69 rows=13,398 width=23) (actual time=1.501..213,890.965 rows=432,445 loops=3)

45. 385.803 385.803 ↑ 1.2 432,445 3 / 3

Parallel Seq Scan on store_sales_main ssm (cost=0.00..45,518.75 rows=540,325 width=19) (actual time=0.349..385.803 rows=432,445 loops=3)

46. 212,763.104 212,763.104 ↑ 1.0 1 1,297,336 / 3

Index Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..2.89 rows=1 width=24) (actual time=0.492..0.492 rows=1 loops=1,297,336)

  • Index Cond: ((charge_id)::text = (ssm.charge_id)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text = ANY ('{PHCMED,PHCRET}'::text[])))
47. 21,622.267 21,622.267 ↑ 1.0 1 1,297,336 / 3

Index Scan using bill_pkey on bill b_1 (cost=0.56..1.54 rows=1 width=67) (actual time=0.050..0.050 rows=1 loops=1,297,336)

  • Index Cond: ((bill_no)::text = (bc_1.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
48. 9,513.761 9,513.761 ↑ 1.0 1 1,297,331 / 3

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.56..0.70 rows=1 width=63) (actual time=0.022..0.022 rows=1 loops=1,297,331)

  • Index Cond: ((patient_id)::text = (b_1.visit_id)::text)
49. 10,378.648 10,378.648 ↑ 1.0 1 1,297,331 / 3

Index Scan using patient_details_pkey on patient_details pd_1 (cost=0.43..0.49 rows=1 width=49) (actual time=0.024..0.024 rows=1 loops=1,297,331)

  • Index Cond: ((mr_no)::text = (pr_1.mr_no)::text)
50. 0.270 0.631 ↑ 1.0 761 3 / 3

Hash (cost=35.83..35.83 rows=761 width=33) (actual time=0.631..0.631 rows=761 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
51. 0.361 0.361 ↑ 1.0 761 3 / 3

Seq Scan on insurance_company_master icm_1 (cost=0.00..35.83 rows=761 width=33) (actual time=0.035..0.361 rows=761 loops=3)

52. 31,135.944 31,135.944 ↑ 4.0 2 1,297,331 / 3

Index Scan using pharmacy_medicine_sales_sale_id_idx on store_sales_details ssd (cost=0.43..1.01 rows=8 width=43) (actual time=0.051..0.072 rows=2 loops=1,297,331)

  • Index Cond: ((sale_id)::text = (ssm.sale_id)::text)
53. 14,523.720 14,523.720 ↑ 1.0 1 2,904,744

Index Scan using pharmacy_medicine_details_pkey on store_item_details sid (cost=0.29..0.33 rows=1 width=63) (actual time=0.005..0.005 rows=1 loops=2,904,744)

  • Index Cond: (medicine_id = ssd.medicine_id)
54. 0.017 0.033 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.033..0.033 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.016 0.016 ↑ 1.0 21 1

Seq Scan on salutation_master sm_1 (cost=0.00..1.63 rows=21 width=14) (actual time=0.010..0.016 rows=21 loops=1)

56. 0.012 0.052 ↑ 1.0 46 1

Hash (cost=5.38..5.38 rows=46 width=29) (actual time=0.052..0.052 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
57. 0.040 0.040 ↑ 1.0 46 1

Seq Scan on hospital_center_master hcm_1 (cost=0.00..5.38 rows=46 width=29) (actual time=0.022..0.040 rows=46 loops=1)

58. 0.107 2.369 ↑ 1.0 357 1

Hash (cost=14.71..14.71 rows=357 width=15) (actual time=2.369..2.369 rows=357 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
59. 2.262 2.262 ↑ 1.0 357 1

Seq Scan on state_master stm_1 (cost=0.00..14.71 rows=357 width=15) (actual time=0.011..2.262 rows=357 loops=1)

60. 0.011 0.024 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
61. 0.013 0.013 ↑ 1.0 60 1

Seq Scan on department dep_1 (cost=0.00..2.80 rows=60 width=21) (actual time=0.007..0.013 rows=60 loops=1)

62. 0.436 1.010 ↑ 1.0 2,107 1

Hash (cost=148.21..148.21 rows=2,107 width=30) (actual time=1.010..1.010 rows=2,107 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 162kB
63. 0.574 0.574 ↑ 1.0 2,107 1

Seq Scan on doctors dc_1 (cost=0.00..148.21 rows=2,107 width=30) (actual time=0.008..0.574 rows=2,107 loops=1)

Planning time : 54.681 ms
Execution time : 644,881.755 ms