explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fdPi

Settings
# exclusive inclusive rows x rows loops node
1. 423.605 156,155.769 ↓ 6.9 195,157 1

Sort (cost=8,546,141.13..8,546,211.95 rows=28,330 width=1,662) (actual time=156,125.040..156,155.769 rows=195,157 loops=1)

  • Sort Key: ((b.open_date)::date)
  • Sort Method: external merge Disk: 54376kB
2. 12.889 155,732.164 ↓ 6.9 195,157 1

Append (cost=3,908,623.35..8,543,196.22 rows=28,330 width=1,662) (actual time=73,794.254..155,732.164 rows=195,157 loops=1)

3. 137.425 75,891.115 ↓ 6.5 184,787 1

Hash Left Join (cost=3,908,623.35..4,115,763.95 rows=28,282 width=345) (actual time=73,794.254..75,891.115 rows=184,787 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dc.doctor_id)::text)
4. 48.409 75,752.909 ↓ 6.5 184,787 1

Hash Left Join (cost=3,908,406.67..4,114,412.04 rows=28,282 width=249) (actual time=73,793.429..75,752.909 rows=184,787 loops=1)

  • Hash Cond: ((pr.dept_name)::text = (dep.dept_id)::text)
5. 49.742 75,704.477 ↓ 6.5 184,787 1

Hash Left Join (cost=3,908,401.92..4,114,318.39 rows=28,282 width=244) (actual time=73,793.397..75,704.477 rows=184,787 loops=1)

  • Hash Cond: ((hcm.state_id)::text = (stm.state_id)::text)
6. 48.013 75,653.399 ↓ 6.5 184,787 1

Hash Left Join (cost=3,908,375.60..4,114,215.38 rows=28,282 width=243) (actual time=73,792.052..75,653.399 rows=184,787 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
7. 45.795 75,605.373 ↓ 6.5 184,787 1

Hash Left Join (cost=3,908,373.29..4,114,096.90 rows=28,282 width=247) (actual time=73,792.030..75,605.373 rows=184,787 loops=1)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
8. 742.670 75,559.327 ↓ 6.5 184,787 1

Hash Join (cost=3,908,312.73..4,113,961.04 rows=28,282 width=230) (actual time=73,791.743..75,559.327 rows=184,787 loops=1)

  • Hash Cond: ((pd.mr_no)::text = (pr.mr_no)::text)
9. 1,025.127 1,025.127 ↓ 1.0 3,016,518 1

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

10. 151.462 73,791.530 ↓ 6.5 184,787 1

Hash (cost=3,907,393.56..3,907,393.56 rows=28,282 width=196) (actual time=73,791.530..73,791.530 rows=184,787 loops=1)

  • Buckets: 262144 (originally 32768) Batches: 1 (originally 1) Memory Usage: 42806kB
11. 1,379.938 73,640.068 ↓ 6.5 184,787 1

Hash Join (cost=700,740.81..3,907,393.56 rows=28,282 width=196) (actual time=4,447.996..73,640.068 rows=184,787 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
12. 228.574 69,741.709 ↓ 1.1 1,395,678 1

Gather (cost=146,290.06..3,347,215.76 rows=1,300,958 width=127) (actual time=1,922.757..69,741.709 rows=1,395,678 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
13. 8,867.606 69,513.135 ↓ 2.6 1,395,678 1

Hash Join (cost=145,290.06..3,216,119.96 rows=542,066 width=127) (actual time=1,921.527..69,513.135 rows=1,395,678 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
14. 58,726.295 58,726.295 ↓ 2.4 42,652,762 1

Parallel Seq Scan on bill_charge bc (cost=0.00..3,023,999.12 rows=17,840,272 width=74) (actual time=0.536..58,726.295 rows=42,652,762 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{PHCMED,PHCRET}'::text[])))
  • Rows Removed by Filter: 3613532
15. 137.994 1,919.234 ↓ 1.0 308,778 1

Hash (cost=135,699.24..135,699.24 rows=295,102 width=67) (actual time=1,919.234..1,919.234 rows=308,778 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 32866kB
16. 1,781.240 1,781.240 ↓ 1.0 308,778 1

Index Scan using bill_finalized_date_idx on bill b (cost=0.56..135,699.24 rows=295,102 width=67) (actual time=0.042..1,781.240 rows=308,778 loops=1)

  • Index Cond: (((finalized_date)::date >= '2020-01-01'::date) AND ((finalized_date)::date <= '2020-01-31'::date))
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 6367
17. 450.877 2,518.421 ↓ 5.6 1,070,911 1

Hash (cost=548,282.06..548,282.06 rows=189,806 width=84) (actual time=2,518.421..2,518.421 rows=1,070,911 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 2 (originally 1) Memory Usage: 65007kB
18. 75.071 2,067.544 ↓ 5.6 1,070,911 1

Gather (cost=1,005.53..548,282.06 rows=189,806 width=84) (actual time=6.363..2,067.544 rows=1,070,911 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
19. 1,078.204 1,992.473 ↓ 4.5 356,970 3 / 3

Hash Join (cost=5.53..528,301.46 rows=79,086 width=84) (actual time=1.302..1,992.473 rows=356,970 loops=3)

  • Hash Cond: (pr.center_id = hcm.center_id)
20. 914.207 914.207 ↑ 1.2 2,910,498 3 / 3

Parallel Seq Scan on patient_registration pr (cost=0.00..516,284.74 rows=3,637,958 width=63) (actual time=0.016..914.207 rows=2,910,498 loops=3)

21. 0.004 0.062 ↑ 1.0 1 3 / 3

Hash (cost=5.50..5.50 rows=1 width=29) (actual time=0.062..0.062 rows=1 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.058 0.058 ↑ 1.0 1 3 / 3

Seq Scan on hospital_center_master hcm (cost=0.00..5.50 rows=1 width=29) (actual time=0.056..0.058 rows=1 loops=3)

  • Filter: ((center_name)::text = 'NMC AlAin'::text)
  • Rows Removed by Filter: 45
23. 0.112 0.251 ↑ 1.0 761 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
24. 0.139 0.139 ↑ 1.0 761 1

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

25. 0.004 0.013 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.009 0.009 ↑ 1.0 21 1

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

27. 0.042 1.336 ↑ 1.0 357 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
28. 1.294 1.294 ↑ 1.0 357 1

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

29. 0.010 0.023 ↑ 1.0 60 1

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

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

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

31. 0.394 0.781 ↑ 1.0 2,107 1

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

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

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

33. 2.750 79,828.160 ↓ 216.0 10,370 1

Subquery Scan on *SELECT* 2 (cost=4,163,438.15..4,426,583.81 rows=48 width=389) (actual time=73,506.730..79,828.160 rows=10,370 loops=1)

34. 19.050 79,825.410 ↓ 216.0 10,370 1

Hash Left Join (cost=4,163,438.15..4,426,582.37 rows=48 width=373) (actual time=73,506.725..79,825.410 rows=10,370 loops=1)

  • Hash Cond: ((pr_1.doctor)::text = (dc_1.doctor_id)::text)
35. 5.565 79,804.905 ↓ 216.0 10,370 1

Hash Left Join (cost=4,163,221.46..4,426,363.76 rows=48 width=278) (actual time=73,505.190..79,804.905 rows=10,370 loops=1)

  • Hash Cond: ((pr_1.dept_name)::text = (dep_1.dept_id)::text)
36. 5.701 79,799.290 ↓ 216.0 10,370 1

Hash Left Join (cost=4,163,216.71..4,426,358.85 rows=48 width=273) (actual time=73,505.115..79,799.290 rows=10,370 loops=1)

  • Hash Cond: ((pd_1.salutation)::text = (sm_1.salutation_id)::text)
37. 14.763 79,793.557 ↓ 216.0 10,370 1

Hash Join (cost=4,163,214.40..4,426,356.35 rows=48 width=277) (actual time=73,505.023..79,793.557 rows=10,370 loops=1)

  • Hash Cond: (pr_1.center_id = hcm_1.center_id)
38. 57.400 79,778.692 ↓ 43.8 95,914 1

Hash Join (cost=4,163,192.76..4,426,325.07 rows=2,188 width=255) (actual time=73,284.236..79,778.692 rows=95,914 loops=1)

  • Hash Cond: (ssd.medicine_id = sid.medicine_id)
39. 867.745 79,688.537 ↓ 43.8 95,914 1

Hash Join (cost=4,155,709.64..4,418,836.20 rows=2,188 width=200) (actual time=73,251.257..79,688.537 rows=95,914 loops=1)

  • Hash Cond: ((ssd.sale_id)::text = (ssm.sale_id)::text)
40. 5,632.970 5,632.970 ↓ 1.0 2,904,569 1

Seq Scan on store_sales_details ssd (cost=0.00..252,171.93 rows=2,903,731 width=43) (actual time=0.010..5,632.970 rows=2,904,569 loops=1)

41. 27.507 73,187.822 ↓ 43.4 42,366 1

Hash (cost=4,155,677.88..4,155,677.88 rows=977 width=175) (actual time=73,187.822..73,187.822 rows=42,366 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9862kB
42. 10.479 73,160.315 ↓ 43.4 42,366 1

Hash Left Join (cost=3,962,155.81..4,155,677.88 rows=977 width=175) (actual time=71,602.867..73,160.315 rows=42,366 loops=1)

  • Hash Cond: ((pr_1.primary_insurance_co)::text = (icm_1.insurance_co_id)::text)
43. 463.201 73,149.470 ↓ 43.4 42,366 1

Hash Join (cost=3,962,095.25..4,155,614.72 rows=977 width=158) (actual time=71,602.455..73,149.470 rows=42,366 loops=1)

  • Hash Cond: ((pd_1.mr_no)::text = (pr_1.mr_no)::text)
44. 1,084.007 1,084.007 ↓ 1.0 3,016,518 1

Seq Scan on patient_details pd_1 (cost=0.00..182,180.48 rows=3,015,916 width=49) (actual time=0.009..1,084.007 rows=3,016,518 loops=1)

45. 21.039 71,602.262 ↓ 43.4 42,366 1

Hash (cost=3,962,063.50..3,962,063.50 rows=977 width=124) (actual time=71,602.262..71,602.262 rows=42,366 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 7460kB
46. 2,499.468 71,581.223 ↓ 43.4 42,366 1

Hash Join (cost=3,249,299.72..3,962,063.50 rows=977 width=124) (actual time=62,278.301..71,581.223 rows=42,366 loops=1)

  • Hash Cond: ((pr_1.patient_id)::text = (b_1.visit_id)::text)
47. 7,187.015 7,187.015 ↓ 1.0 8,731,495 1

Seq Scan on patient_registration pr_1 (cost=0.00..669,078.97 rows=8,731,099 width=63) (actual time=0.009..7,187.015 rows=8,731,495 loops=1)

48. 16.486 61,894.740 ↓ 43.4 42,366 1

Hash (cost=3,249,267.97..3,249,267.97 rows=977 width=76) (actual time=61,894.740..61,894.740 rows=42,366 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 5119kB
49. 431.391 61,878.254 ↓ 43.4 42,366 1

Hash Join (cost=3,176,163.33..3,249,267.97 rows=977 width=76) (actual time=61,215.960..61,878.254 rows=42,366 loops=1)

  • Hash Cond: ((ssm.charge_id)::text = (bc_1.charge_id)::text)
50. 813.941 813.941 ↓ 1.0 1,297,261 1

Seq Scan on store_sales_main ssm (cost=0.00..68,212.40 rows=1,296,780 width=19) (actual time=0.007..813.941 rows=1,297,261 loops=1)

51. 56.255 60,632.922 ↓ 1.2 42,366 1

Hash (cost=3,175,030.32..3,175,030.32 rows=34,862 width=77) (actual time=60,632.922..60,632.922 rows=42,366 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 5119kB
52. 19.540 60,576.667 ↓ 1.2 42,366 1

Gather (cost=146,290.06..3,175,030.32 rows=34,862 width=77) (actual time=4,904.845..60,576.667 rows=42,366 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
53. 910.072 60,557.127 ↓ 2.9 42,366 1

Hash Join (cost=145,290.06..3,170,544.12 rows=14,526 width=77) (actual time=4,903.122..60,557.127 rows=42,366 loops=1)

  • Hash Cond: ((bc_1.bill_no)::text = (b_1.bill_no)::text)
54. 54,746.821 54,746.821 ↓ 2.7 1,297,261 1

Parallel Seq Scan on bill_charge bc_1 (cost=0.00..3,023,999.12 rows=478,071 width=24) (actual time=0.035..54,746.821 rows=1,297,261 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text = ANY ('{PHCMED,PHCRET}'::text[])))
  • Rows Removed by Filter: 44969033
55. 183.430 4,900.234 ↓ 1.0 308,778 1

Hash (cost=135,699.24..135,699.24 rows=295,102 width=67) (actual time=4,900.234..4,900.234 rows=308,778 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 32866kB
56. 4,716.804 4,716.804 ↓ 1.0 308,778 1

Index Scan using bill_finalized_date_idx on bill b_1 (cost=0.56..135,699.24 rows=295,102 width=67) (actual time=0.054..4,716.804 rows=308,778 loops=1)

  • Index Cond: (((finalized_date)::date >= '2020-01-01'::date) AND ((finalized_date)::date <= '2020-01-31'::date))
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 6367
57. 0.191 0.366 ↑ 1.0 761 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 57kB
58. 0.175 0.175 ↑ 1.0 761 1

Seq Scan on insurance_company_master icm_1 (cost=0.00..35.83 rows=761 width=33) (actual time=0.014..0.175 rows=761 loops=1)

59. 12.487 32.755 ↓ 1.0 65,503 1

Hash (cost=5,354.70..5,354.70 rows=65,490 width=63) (actual time=32.754..32.755 rows=65,503 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 6732kB
60. 20.268 20.268 ↓ 1.0 65,503 1

Seq Scan on store_item_details sid (cost=0.00..5,354.70 rows=65,490 width=63) (actual time=0.009..20.268 rows=65,503 loops=1)

61. 0.002 0.102 ↑ 1.0 1 1

Hash (cost=21.61..21.61 rows=1 width=30) (actual time=0.102..0.102 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.040 0.100 ↑ 1.0 1 1

Hash Right Join (cost=5.53..21.61 rows=1 width=30) (actual time=0.058..0.100 rows=1 loops=1)

  • Hash Cond: ((stm_1.state_id)::text = (hcm_1.state_id)::text)
63. 0.033 0.033 ↑ 1.0 357 1

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

64. 0.003 0.027 ↑ 1.0 1 1

Hash (cost=5.50..5.50 rows=1 width=29) (actual time=0.027..0.027 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm_1 (cost=0.00..5.50 rows=1 width=29) (actual time=0.023..0.024 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC AlAin'::text)
  • Rows Removed by Filter: 45
66. 0.015 0.032 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
67. 0.017 0.017 ↑ 1.0 21 1

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

68. 0.027 0.050 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
69. 0.023 0.023 ↑ 1.0 60 1

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

70. 0.793 1.455 ↑ 1.0 2,107 1

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

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

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