explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EeOT

Settings
# exclusive inclusive rows x rows loops node
1. 6.976 184,482.437 ↑ 17.9 860 1
→

GroupAggregate (cost=6,571,120.91..6,573,242.39 rows=15,429 width=286) (actual time=184,473.548..184,482.437 rows=860 loops=1)

  • Group Key: foo.posted_date, foo.department, foo.center_name
2. 26.100 184,475.461 ↑ 3.5 44,700 1
→

Sort (cost=6,571,120.91..6,571,506.63 rows=154,290 width=262) (actual time=184,473.533..184,475.461 rows=44,700 loops=1)

  • Sort Key: foo.posted_date, foo.department, foo.center_name
  • Sort Method: quicksort Memory: 5,167kB
3. 3.877 184,449.361 ↑ 3.5 44,700 1
→

Subquery Scan on foo (cost=3,206,612.93..6,557,824.75 rows=154,290 width=262) (actual time=60,423.096..184,449.361 rows=44,700 loops=1)

4. 2.846 184,445.484 ↑ 3.5 44,700 1
→

Append (cost=3,206,612.93..6,556,281.85 rows=154,290 width=262) (actual time=60,423.095..184,445.484 rows=44,700 loops=1)

5. 4.175 60,502.632 ↑ 3.2 40,087 1
→

Subquery Scan on *SELECT* 1 (cost=3,206,612.93..3,211,350.50 rows=126,335 width=62) (actual time=60,423.094..60,502.632 rows=40,087 loops=1)

6. 64.188 60,498.457 ↑ 3.2 40,087 1
→

GroupAggregate (cost=3,206,612.93..3,210,087.15 rows=126,335 width=77) (actual time=60,423.093..60,498.457 rows=40,087 loops=1)

  • Group Key: ((bc.posted_date)::date), (COALESCE(rami.override_department, dit.dept_name, ramc.override_department, dch.dept_name, dep.dept_name, dep_1.dept_name, dac.dept_name, rami.allocation_department, ramc.allocation_department, '-NA-'::character varying)), b.visit_id, hcm.center_name
7. 243.971 60,434.269 ↑ 1.5 84,646 1
→

Sort (cost=3,206,612.93..3,206,928.77 rows=126,335 width=69) (actual time=60,423.070..60,434.269 rows=84,646 loops=1)

  • Sort Key: ((bc.posted_date)::date), (COALESCE(rami.override_department, dit.dept_name, ramc.override_department, dch.dept_name, dep.dept_name, dep_1.dept_name, dac.dept_name, rami.allocation_department, ramc.allocation_department, '-NA-'::character varying)), b.visit_id, hcm.center_name
  • Sort Method: quicksort Memory: 11,189kB
8. 52.327 60,190.298 ↑ 1.5 84,646 1
→

Hash Left Join (cost=1,428.43..3,195,908.00 rows=126,335 width=69) (actual time=5.615..60,190.298 rows=84,646 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = (dac.dept_id)::text)
  • Join Filter: ((COALESCE(rami.allocation_department, ramc.allocation_department))::text = 'CDEPT'::text)
  • Rows Removed by Join Filter: 163
9. 54.169 60,137.950 ↑ 1.5 84,646 1
→

Hash Left Join (cost=1,426.10..3,195,258.14 rows=126,335 width=130) (actual time=5.584..60,137.950 rows=84,646 loops=1)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (doc.doctor_id)::text)
  • Join Filter: ((COALESCE(rami.allocation_department, ramc.allocation_department))::text = 'PDEPT'::text)
  • Rows Removed by Join Filter: 14,799
10. 59.906 60,082.773 ↑ 1.5 84,646 1
→

Hash Left Join (cost=1,235.37..3,193,750.10 rows=126,335 width=124) (actual time=4.564..60,082.773 rows=84,646 loops=1)

  • Hash Cond: ((pr.doctor)::text = (doc_1.doctor_id)::text)
  • Join Filter: ((COALESCE(rami.allocation_department, ramc.allocation_department))::text = 'ADEPT'::text)
  • Rows Removed by Join Filter: 77,405
11. 445.984 60,021.621 ↑ 1.5 84,646 1
→

Nested Loop Left Join (cost=1,044.64..3,191,507.48 rows=126,335 width=119) (actual time=3.305..60,021.621 rows=84,646 loops=1)

  • Join Filter: ((bc.charge_head)::text = (ramc.charge_head)::text)
  • Rows Removed by Join Filter: 3,470,486
12. 16.407 59,406.345 ↓ 3.8 84,646 1
→

Nested Loop (cost=1,042.31..3,177,282.02 rows=22,542 width=97) (actual time=3.264..59,406.345 rows=84,646 loops=1)

13. 0.009 0.009 ↑ 1.0 1 1
→

Index Scan using center_pkey on hospital_center_master hcm (cost=0.14..6.16 rows=1 width=22) (actual time=0.007..0.009 rows=1 loops=1)

  • Index Cond: (center_id = 12)
14. 0.000 59,389.929 ↓ 3.8 84,646 1
→

Gather (cost=1,042.17..3,177,050.44 rows=22,542 width=83) (actual time=3.255..59,389.929 rows=84,646 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 26.894 59,904.952 ↓ 3.0 28,215 3 / 3
→

Hash Left Join (cost=42.17..3,173,796.24 rows=9,392 width=83) (actual time=32.623..59,904.952 rows=28,215 loops=3)

  • Hash Cond: ((bc.act_description_id)::text = (rami.item_id)::text)
16. 239.001 59,876.805 ↓ 3.0 28,202 3 / 3
→

Nested Loop (cost=0.87..3,173,685.99 rows=9,392 width=62) (actual time=31.078..59,876.805 rows=28,202 loops=3)

17. 197.318 52,210.887 ↓ 7.2 571,301 3 / 3
→

Nested Loop (cost=0.43..2,913,990.80 rows=79,638 width=50) (actual time=16.502..52,210.887 rows=571,301 loops=3)

18. 41,158.698 41,158.698 ↓ 6.9 571,309 3 / 3
→

Parallel Seq Scan on bill_charge bc (cost=0.00..2,550,275.38 rows=82,318 width=49) (actual time=16.365..41,158.698 rows=571,309 loops=3)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{ADJPDM,CHGPDM}'::text[])) AND ((posted_date)::date >= '2019-10-01'::date) AND ((posted_date)::date <= '2019-10-31'::date))
  • Rows Removed by Filter: 13,316,708
19. 10,854.871 10,854.871 ↑ 1.0 1 1,713,927 / 3
→

Index Scan using bill_pkey on bill b (cost=0.43..4.42 rows=1 width=29) (actual time=0.019..0.019 rows=1 loops=1,713,927)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
20. 7,426.917 7,426.917 ↓ 0.0 0 1,713,904 / 3
→

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..3.26 rows=1 width=28) (actual time=0.013..0.013 rows=0 loops=1,713,904)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
  • Filter: ((visit_type <> 'i'::bpchar) AND (center_id = 12))
  • Rows Removed by Filter: 1
21. 0.436 1.253 ↑ 1.0 1,103 3 / 3
→

Hash (cost=26.99..26.99 rows=1,145 width=36) (actual time=1.253..1.253 rows=1,103 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 68kB
22. 0.517 0.817 ↑ 1.0 1,145 3 / 3
→

Hash Left Join (cost=2.33..26.99 rows=1,145 width=36) (actual time=0.133..0.817 rows=1,145 loops=3)

  • Hash Cond: ((rami.allocation_department)::text = (dit.dept_id)::text)
23. 0.226 0.226 ↑ 1.0 1,145 3 / 3
→

Seq Scan on rev_allocation_map rami (cost=0.00..21.45 rows=1,145 width=23) (actual time=0.034..0.226 rows=1,145 loops=3)

24. 0.034 0.074 ↑ 1.0 59 3 / 3
→

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
25. 0.040 0.040 ↑ 1.0 59 3 / 3
→

Seq Scan on department dit (cost=0.00..1.59 rows=59 width=21) (actual time=0.024..0.040 rows=59 loops=3)

26. 169.184 169.292 ↑ 1.0 42 84,646
→

Materialize (cost=2.33..24.11 rows=42 width=34) (actual time=0.000..0.002 rows=42 loops=84,646)

27. 0.017 0.108 ↑ 1.0 42 1
→

Hash Left Join (cost=2.33..23.90 rows=42 width=34) (actual time=0.032..0.108 rows=42 loops=1)

  • Hash Cond: ((ramc.allocation_department)::text = (dch.dept_id)::text)
28. 0.073 0.073 ↑ 1.0 42 1
→

Seq Scan on rev_allocation_map ramc (cost=0.00..21.45 rows=42 width=21) (actual time=0.005..0.073 rows=42 loops=1)

  • Filter: (item_id IS NULL)
  • Rows Removed by Filter: 1,103
29. 0.011 0.018 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.018..0.018 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
30. 0.007 0.007 ↑ 1.0 59 1
→

Seq Scan on department dch (cost=0.00..1.59 rows=59 width=21) (actual time=0.002..0.007 rows=59 loops=1)

31. 0.341 1.246 ↑ 1.0 1,991 1
→

Hash (cost=165.84..165.84 rows=1,991 width=21) (actual time=1.246..1.246 rows=1,991 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 122kB
32. 0.618 0.905 ↑ 1.0 1,991 1
→

Hash Join (cost=2.33..165.84 rows=1,991 width=21) (actual time=0.032..0.905 rows=1,991 loops=1)

  • Hash Cond: ((doc_1.dept_id)::text = (dep_1.dept_id)::text)
33. 0.270 0.270 ↑ 1.0 1,991 1
→

Seq Scan on doctors doc_1 (cost=0.00..157.91 rows=1,991 width=16) (actual time=0.005..0.270 rows=1,991 loops=1)

34. 0.011 0.017 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.017..0.017 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
35. 0.006 0.006 ↑ 1.0 59 1
→

Seq Scan on department dep_1 (cost=0.00..1.59 rows=59 width=21) (actual time=0.001..0.006 rows=59 loops=1)

36. 0.335 1.008 ↑ 1.0 1,991 1
→

Hash (cost=165.84..165.84 rows=1,991 width=21) (actual time=1.008..1.008 rows=1,991 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 122kB
37. 0.490 0.673 ↑ 1.0 1,991 1
→

Hash Join (cost=2.33..165.84 rows=1,991 width=21) (actual time=0.031..0.673 rows=1,991 loops=1)

  • Hash Cond: ((doc.dept_id)::text = (dep.dept_id)::text)
38. 0.163 0.163 ↑ 1.0 1,991 1
→

Seq Scan on doctors doc (cost=0.00..157.91 rows=1,991 width=16) (actual time=0.002..0.163 rows=1,991 loops=1)

39. 0.013 0.020 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.020..0.020 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
40. 0.007 0.007 ↑ 1.0 59 1
→

Seq Scan on department dep (cost=0.00..1.59 rows=59 width=21) (actual time=0.002..0.007 rows=59 loops=1)

41. 0.012 0.021 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.021..0.021 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
42. 0.009 0.009 ↑ 1.0 59 1
→

Seq Scan on department dac (cost=0.00..1.59 rows=59 width=21) (actual time=0.004..0.009 rows=59 loops=1)

43. 0.149 71,052.158 ↑ 20.2 1,339 1
→

Subquery Scan on *SELECT* 2 (cost=527,952.99..528,897.67 rows=26,991 width=62) (actual time=71,023.531..71,052.158 rows=1,339 loops=1)

44. 23.613 71,052.009 ↑ 20.2 1,339 1
→

GroupAggregate (cost=527,952.99..528,627.76 rows=26,991 width=77) (actual time=71,023.529..71,052.009 rows=1,339 loops=1)

  • Group Key: pr_1.reg_date, 'Inpatient'::text, b_1.visit_id, hcm_1.center_name
45. 163.530 71,028.396 ↓ 3.0 79,891 1
→

Sort (cost=527,952.99..528,020.47 rows=26,991 width=69) (actual time=71,023.485..71,028.396 rows=79,891 loops=1)

  • Sort Key: pr_1.reg_date, b_1.visit_id, hcm_1.center_name
  • Sort Method: quicksort Memory: 9,314kB
46. 80.747 70,864.866 ↓ 3.0 79,891 1
→

Hash Left Join (cost=16,315.59..525,966.43 rows=26,991 width=69) (actual time=494.484..70,864.866 rows=79,891 loops=1)

  • Hash Cond: ((bc_1.prescribing_dr_id)::text = (doc_2.doctor_id)::text)
  • Join Filter: ((COALESCE(rami_1.allocation_department, ramc_1.allocation_department))::text = 'PDEPT'::text)
  • Rows Removed by Join Filter: 9,230
47. 91.484 70,781.903 ↓ 3.0 79,891 1
→

Hash Left Join (cost=16,124.86..525,494.26 rows=26,991 width=56) (actual time=492.249..70,781.903 rows=79,891 loops=1)

  • Hash Cond: ((pr_1.doctor)::text = (doc_3.doctor_id)::text)
  • Join Filter: ((COALESCE(rami_1.allocation_department, ramc_1.allocation_department))::text = 'ADEPT'::text)
  • Rows Removed by Join Filter: 46,512
48. 82.422 70,687.957 ↓ 3.0 79,891 1
→

Hash Left Join (cost=15,934.13..524,865.15 rows=26,991 width=64) (actual time=489.774..70,687.957 rows=79,891 loops=1)

  • Hash Cond: ((bc_1.charge_head)::text = (ramc_1.charge_head)::text)
49. 26.575 70,605.367 ↓ 16.6 79,891 1
→

Nested Loop (cost=15,912.15..524,308.38 rows=4,816 width=71) (actual time=489.596..70,605.367 rows=79,891 loops=1)

50. 0.024 0.024 ↑ 1.0 1 1
→

Index Scan using center_pkey on hospital_center_master hcm_1 (cost=0.14..6.16 rows=1 width=22) (actual time=0.020..0.024 rows=1 loops=1)

  • Index Cond: (center_id = 12)
51. 941.362 70,578.768 ↓ 16.6 79,891 1
→

Gather (cost=15,912.01..524,254.06 rows=4,816 width=57) (actual time=489.572..70,578.768 rows=79,891 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
52. 50.145 69,637.406 ↓ 13.3 26,630 3 / 3
→

Hash Left Join (cost=14,912.01..522,772.46 rows=2,007 width=57) (actual time=740.734..69,637.406 rows=26,630 loops=3)

  • Hash Cond: ((bc_1.act_description_id)::text = (rami_1.item_id)::text)
53. 21.847 69,586.582 ↓ 13.3 26,630 3 / 3
→

Nested Loop (cost=14,876.25..522,721.97 rows=2,007 width=58) (actual time=739.858..69,586.582 rows=26,630 loops=3)

54. 4.595 12,747.095 ↓ 1.6 695 3 / 3
→

Nested Loop (cost=14,875.68..388,886.49 rows=442 width=45) (actual time=720.121..12,747.095 rows=695 loops=3)

55. 12,622.634 12,700.867 ↓ 1.1 448 3 / 3
→

Parallel Bitmap Heap Scan on patient_registration pr_1 (cost=14,875.25..386,241.88 rows=411 width=32) (actual time=719.603..12,700.867 rows=448 loops=3)

  • Recheck Cond: (center_id = 12)
  • Filter: ((reg_date >= '2019-10-01'::date) AND (reg_date <= '2019-10-31'::date) AND (visit_type = 'i'::bpchar))
  • Rows Removed by Filter: 312,802
  • Heap Blocks: exact=110,834
56. 78.233 78.233 ↑ 1.0 939,748 1 / 3
→

Bitmap Index Scan on idx_patient_registration (cost=0.00..14,875.00 rows=945,676 width=0) (actual time=234.700..234.700 rows=939,748 loops=1)

  • Index Cond: (center_id = 12)
57. 41.633 41.633 ↓ 2.0 2 1,343 / 3
→

Index Scan using bill_visit_id_idx on bill b_1 (cost=0.43..6.42 rows=1 width=29) (actual time=0.050..0.093 rows=2 loops=1,343)

  • Index Cond: ((visit_id)::text = (pr_1.patient_id)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
58. 56,817.640 56,817.640 ↑ 4.7 38 2,085 / 3
→

Index Scan using bill_charge_bill_no_index on bill_charge bc_1 (cost=0.56..301.01 rows=179 width=41) (actual time=6.191..81.752 rows=38 loops=2,085)

  • Index Cond: ((bill_no)::text = (b_1.bill_no)::text)
  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text <> ALL ('{ADJPDM,CHGPDM}'::text[])))
  • Rows Removed by Filter: 2
59. 0.356 0.679 ↑ 1.0 1,103 3 / 3
→

Hash (cost=21.45..21.45 rows=1,145 width=14) (actual time=0.679..0.679 rows=1,103 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 67kB
60. 0.323 0.323 ↑ 1.0 1,145 3 / 3
→

Seq Scan on rev_allocation_map rami_1 (cost=0.00..21.45 rows=1,145 width=14) (actual time=0.032..0.323 rows=1,145 loops=3)

61. 0.015 0.168 ↑ 1.0 42 1
→

Hash (cost=21.45..21.45 rows=42 width=12) (actual time=0.168..0.168 rows=42 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
62. 0.153 0.153 ↑ 1.0 42 1
→

Seq Scan on rev_allocation_map ramc_1 (cost=0.00..21.45 rows=42 width=12) (actual time=0.012..0.153 rows=42 loops=1)

  • Filter: (item_id IS NULL)
  • Rows Removed by Filter: 1,103
63. 0.676 2.462 ↑ 1.0 1,991 1
→

Hash (cost=165.84..165.84 rows=1,991 width=8) (actual time=2.462..2.462 rows=1,991 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 94kB
64. 1.270 1.786 ↑ 1.0 1,991 1
→

Hash Join (cost=2.33..165.84 rows=1,991 width=8) (actual time=0.051..1.786 rows=1,991 loops=1)

  • Hash Cond: ((doc_3.dept_id)::text = (dep_3.dept_id)::text)
65. 0.477 0.477 ↑ 1.0 1,991 1
→

Seq Scan on doctors doc_3 (cost=0.00..157.91 rows=1,991 width=16) (actual time=0.005..0.477 rows=1,991 loops=1)

66. 0.020 0.039 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=8) (actual time=0.039..0.039 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
67. 0.019 0.019 ↑ 1.0 59 1
→

Seq Scan on department dep_3 (cost=0.00..1.59 rows=59 width=8) (actual time=0.006..0.019 rows=59 loops=1)

68. 0.646 2.216 ↑ 1.0 1,991 1
→

Hash (cost=165.84..165.84 rows=1,991 width=8) (actual time=2.216..2.216 rows=1,991 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 94kB
69. 1.147 1.570 ↑ 1.0 1,991 1
→

Hash Join (cost=2.33..165.84 rows=1,991 width=8) (actual time=0.050..1.570 rows=1,991 loops=1)

  • Hash Cond: ((doc_2.dept_id)::text = (dep_2.dept_id)::text)
70. 0.385 0.385 ↑ 1.0 1,991 1
→

Seq Scan on doctors doc_2 (cost=0.00..157.91 rows=1,991 width=16) (actual time=0.004..0.385 rows=1,991 loops=1)

71. 0.019 0.038 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=8) (actual time=0.038..0.038 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
72. 0.019 0.019 ↑ 1.0 59 1
→

Seq Scan on department dep_2 (cost=0.00..1.59 rows=59 width=8) (actual time=0.005..0.019 rows=59 loops=1)

73. 0.307 52,887.848 ↓ 3.4 3,274 1
→

Subquery Scan on *SELECT* 3 (cost=2,815,997.53..2,816,033.68 rows=964 width=62) (actual time=52,877.215..52,887.848 rows=3,274 loops=1)

74. 8.810 52,887.541 ↓ 3.4 3,274 1
→

GroupAggregate (cost=2,815,997.53..2,816,024.04 rows=964 width=77) (actual time=52,877.213..52,887.541 rows=3,274 loops=1)

  • Group Key: ((bc_2.posted_date)::date), (COALESCE(rami_2.override_department, dit_1.dept_name, ramc_2.override_department, dch_1.dept_name, dep_4.dept_name, dep_5.dept_name, dac_1.dept_name, rami_2.allocation_department, ramc_2.allocation_department, '-NA-'::character varying)), b_2.visit_id, hcm_2.center_name
75. 55.046 52,878.731 ↓ 20.4 19,654 1
→

Sort (cost=2,815,997.53..2,815,999.94 rows=964 width=69) (actual time=52,877.185..52,878.731 rows=19,654 loops=1)

  • Sort Key: ((bc_2.posted_date)::date), (COALESCE(rami_2.override_department, dit_1.dept_name, ramc_2.override_department, dch_1.dept_name, dep_4.dept_name, dep_5.dept_name, dac_1.dept_name, rami_2.allocation_department, ramc_2.allocation_department, '-NA-'::character varying)), b_2.visit_id, hcm_2.center_name
  • Sort Method: quicksort Memory: 2,304kB
76. 14.242 52,823.685 ↓ 20.4 19,654 1
→

Hash Left Join (cost=1,008.82..2,815,949.75 rows=964 width=69) (actual time=72.749..52,823.685 rows=19,654 loops=1)

  • Hash Cond: ((bc_2.act_department_id)::text = (dac_1.dept_id)::text)
  • Join Filter: ((COALESCE(rami_2.allocation_department, ramc_2.allocation_department))::text = 'CDEPT'::text)
77. 15.571 52,809.369 ↓ 20.4 19,654 1
→

Nested Loop Left Join (cost=1,006.50..2,815,942.48 rows=964 width=130) (actual time=72.657..52,809.369 rows=19,654 loops=1)

  • Join Filter: ((COALESCE(rami_2.allocation_department, ramc_2.allocation_department))::text = 'PDEPT'::text)
78. 10.488 52,656.220 ↓ 20.4 19,654 1
→

Nested Loop Left Join (cost=1,006.08..2,813,031.48 rows=964 width=124) (actual time=72.624..52,656.220 rows=19,654 loops=1)

  • Join Filter: ((COALESCE(rami_2.allocation_department, ramc_2.allocation_department))::text = 'ADEPT'::text)
  • Rows Removed by Join Filter: 19,654
79. 101.236 52,468.846 ↓ 20.4 19,654 1
→

Nested Loop Left Join (cost=1,005.66..2,810,117.70 rows=964 width=119) (actual time=72.567..52,468.846 rows=19,654 loops=1)

  • Join Filter: ((bc_2.charge_head)::text = (ramc_2.charge_head)::text)
  • Rows Removed by Join Filter: 805,814
80. 9.437 52,308.648 ↓ 114.3 19,654 1
→

Hash Left Join (cost=1,003.33..2,809,985.34 rows=172 width=97) (actual time=72.467..52,308.648 rows=19,654 loops=1)

  • Hash Cond: ((rami_2.allocation_department)::text = (dit_1.dept_id)::text)
81. 2,383.016 52,299.158 ↓ 114.3 19,654 1
→

Nested Loop Left Join (cost=1,001.01..2,809,982.53 rows=172 width=84) (actual time=72.394..52,299.158 rows=19,654 loops=1)

  • Join Filter: ((bc_2.act_description_id)::text = (rami_2.item_id)::text)
  • Rows Removed by Join Filter: 22,503,830
82. 4.912 48,658.286 ↓ 114.3 19,654 1
→

Nested Loop (cost=1,001.01..2,807,004.11 rows=172 width=76) (actual time=71.350..48,658.286 rows=19,654 loops=1)

83. 0.018 0.018 ↑ 1.0 1 1
→

Index Scan using center_pkey on hospital_center_master hcm_2 (cost=0.14..6.16 rows=1 width=22) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (center_id = 12)
84. 0.000 48,653.356 ↓ 114.3 19,654 1
→

Gather (cost=1,000.87..2,806,996.24 rows=172 width=62) (actual time=71.327..48,653.356 rows=19,654 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
85. 124.538 51,410.174 ↓ 91.0 6,551 3 / 3
→

Nested Loop (cost=0.87..2,805,979.04 rows=72 width=62) (actual time=75.575..51,410.174 rows=6,551 loops=3)

86. 166.973 48,867.436 ↓ 8.9 219,836 3 / 3
→

Nested Loop (cost=0.43..2,725,035.84 rows=24,822 width=50) (actual time=4.381..48,867.436 rows=219,836 loops=3)

87. 44,523.554 44,523.554 ↓ 8.6 219,837 3 / 3
→

Parallel Seq Scan on bill_charge bc_2 (cost=0.00..2,593,674.81 rows=25,658 width=49) (actual time=4.320..44,523.554 rows=219,837 loops=3)

  • Filter: ((status <> 'X'::bpchar) AND ((charge_head)::text = ANY ('{LTDIA,RTDIA}'::text[])) AND ((charge_head)::text <> ALL ('{ADJPDM,CHGPDM}'::text[])) AND ((posted_date)::date >= '2019-10-01'::date) AND ((posted_date)::date <= '2019-10-31'::date))
  • Rows Removed by Filter: 13,668,180
88. 4,176.909 4,176.909 ↑ 1.0 1 659,512 / 3
→

Index Scan using bill_pkey on bill b_2 (cost=0.43..5.12 rows=1 width=29) (actual time=0.019..0.019 rows=1 loops=659,512)

  • Index Cond: ((bill_no)::text = (bc_2.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
89. 2,418.200 2,418.200 ↓ 0.0 0 659,509 / 3
→

Index Scan using patient_registration_pkey on patient_registration pr_2 (cost=0.43..3.26 rows=1 width=28) (actual time=0.011..0.011 rows=0 loops=659,509)

  • Index Cond: ((patient_id)::text = (b_2.visit_id)::text)
  • Filter: ((center_id = 12) AND (visit_type = 'i'::bpchar))
  • Rows Removed by Filter: 1
90. 1,257.476 1,257.856 ↑ 1.0 1,145 19,654
→

Materialize (cost=0.00..27.18 rows=1,145 width=23) (actual time=0.000..0.064 rows=1,145 loops=19,654)

91. 0.380 0.380 ↑ 1.0 1,145 1
→

Seq Scan on rev_allocation_map rami_2 (cost=0.00..21.45 rows=1,145 width=23) (actual time=0.012..0.380 rows=1,145 loops=1)

92. 0.029 0.053 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.053..0.053 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
93. 0.024 0.024 ↑ 1.0 59 1
→

Seq Scan on department dit_1 (cost=0.00..1.59 rows=59 width=21) (actual time=0.007..0.024 rows=59 loops=1)

94. 58.621 58.962 ↑ 1.0 42 19,654
→

Materialize (cost=2.33..24.11 rows=42 width=34) (actual time=0.000..0.003 rows=42 loops=19,654)

95. 0.037 0.341 ↑ 1.0 42 1
→

Hash Left Join (cost=2.33..23.90 rows=42 width=34) (actual time=0.068..0.341 rows=42 loops=1)

  • Hash Cond: ((ramc_2.allocation_department)::text = (dch_1.dept_id)::text)
96. 0.261 0.261 ↑ 1.0 42 1
→

Seq Scan on rev_allocation_map ramc_2 (cost=0.00..21.45 rows=42 width=21) (actual time=0.013..0.261 rows=42 loops=1)

  • Filter: (item_id IS NULL)
  • Rows Removed by Filter: 1,103
97. 0.025 0.043 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.043..0.043 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
98. 0.018 0.018 ↑ 1.0 59 1
→

Seq Scan on department dch_1 (cost=0.00..1.59 rows=59 width=21) (actual time=0.004..0.018 rows=59 loops=1)

99. 39.308 176.886 ↑ 1.0 1 19,654
→

Nested Loop (cost=0.42..3.01 rows=1 width=21) (actual time=0.008..0.009 rows=1 loops=19,654)

100. 98.270 98.270 ↑ 1.0 1 19,654
→

Index Scan using doctors_pkey on doctors doc_5 (cost=0.28..2.85 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=19,654)

  • Index Cond: ((pr_2.doctor)::text = (doctor_id)::text)
101. 39.308 39.308 ↑ 1.0 1 19,654
→

Index Scan using department_pkey on department dep_5 (cost=0.14..0.16 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=19,654)

  • Index Cond: ((dept_id)::text = (doc_5.dept_id)::text)
102. 19.654 137.578 ↑ 1.0 1 19,654
→

Nested Loop (cost=0.42..3.01 rows=1 width=21) (actual time=0.007..0.007 rows=1 loops=19,654)

103. 78.616 78.616 ↑ 1.0 1 19,654
→

Index Scan using doctors_pkey on doctors doc_4 (cost=0.28..2.84 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=19,654)

  • Index Cond: ((bc_2.prescribing_dr_id)::text = (doctor_id)::text)
104. 39.308 39.308 ↑ 1.0 1 19,654
→

Index Scan using department_pkey on department dep_4 (cost=0.14..0.16 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=19,654)

  • Index Cond: ((dept_id)::text = (doc_4.dept_id)::text)
105. 0.041 0.074 ↑ 1.0 59 1
→

Hash (cost=1.59..1.59 rows=59 width=21) (actual time=0.074..0.074 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
106. 0.033 0.033 ↑ 1.0 59 1
→

Seq Scan on department dac_1 (cost=0.00..1.59 rows=59 width=21) (actual time=0.009..0.033 rows=59 loops=1)

Planning time : 20.696 ms
Execution time : 184,488.218 ms