explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q6G

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 44,536.941 ↓ 0.0 0 1

Sort (cost=3,263,593.66..3,263,619.08 rows=10,166 width=737) (actual time=44,536.941..44,536.941 rows=0 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
2. 0.002 44,536.929 ↓ 0.0 0 1

Nested Loop Left Join (cost=39,114.05..3,262,917.04 rows=10,166 width=737) (actual time=44,536.929..44,536.929 rows=0 loops=1)

3. 0.003 44,536.927 ↓ 0.0 0 1

Hash Left Join (cost=39,113.76..3,164,937.82 rows=10,166 width=554) (actual time=44,536.927..44,536.927 rows=0 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
4. 0.002 44,536.924 ↓ 0.0 0 1

Nested Loop Left Join (cost=39,038.64..3,164,835.58 rows=10,166 width=540) (actual time=44,536.924..44,536.924 rows=0 loops=1)

5. 0.002 44,536.922 ↓ 0.0 0 1

Hash Left Join (cost=39,038.22..3,160,177.41 rows=10,166 width=525) (actual time=44,536.922..44,536.922 rows=0 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
6. 0.004 44,536.920 ↓ 0.0 0 1

Hash Left Join (cost=38,821.53..3,159,934.01 rows=10,166 width=503) (actual time=44,536.920..44,536.920 rows=0 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 0.003 44,536.916 ↓ 0.0 0 1

Hash Left Join (cost=38,604.84..3,159,690.48 rows=10,166 width=489) (actual time=44,536.916..44,536.916 rows=0 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 0.003 44,536.913 ↓ 0.0 0 1

Nested Loop Left Join (cost=38,603.53..3,159,599.21 rows=10,166 width=473) (actual time=44,536.912..44,536.913 rows=0 loops=1)

9. 0.003 44,536.910 ↓ 0.0 0 1

Hash Left Join (cost=38,602.14..3,142,763.27 rows=10,166 width=485) (actual time=44,536.910..44,536.910 rows=0 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 0.003 44,536.907 ↓ 0.0 0 1

Nested Loop Left Join (cost=38,599.83..3,142,719.21 rows=10,166 width=489) (actual time=44,536.907..44,536.907 rows=0 loops=1)

11. 63.743 44,536.904 ↓ 0.0 0 1

Hash Left Join (cost=38,599.40..3,137,699.64 rows=10,166 width=455) (actual time=44,536.904..44,536.904 rows=0 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
  • Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
  • Rows Removed by Filter: 30046
12. 19.314 44,219.203 ↑ 7.1 30,046 1

Nested Loop (cost=1,282.40..3,099,824.25 rows=212,720 width=441) (actual time=23.482..44,219.203 rows=30,046 loops=1)

13. 0.049 0.049 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..5.50 rows=1 width=22) (actual time=0.021..0.049 rows=1 loops=1)

  • Filter: ((center_name)::text = 'CosmeSurge Clinic Julphar'::text)
  • Rows Removed by Filter: 45
14. 24.735 44,199.840 ↑ 7.1 30,046 1

Hash Left Join (cost=1,282.40..3,093,437.15 rows=212,720 width=419) (actual time=23.460..44,199.840 rows=30,046 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 31.580 44,175.102 ↑ 7.1 30,046 1

Hash Left Join (cost=1,254.90..3,092,851.26 rows=212,720 width=297) (actual time=23.447..44,175.102 rows=30,046 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
16. 22.211 44,143.419 ↑ 7.1 30,046 1

Gather (cost=1,237.44..3,087,756.18 rows=212,720 width=272) (actual time=23.328..44,143.419 rows=30,046 loops=1)

  • Workers Planned: 2
  • Workers Launched: 0
17. 31.580 44,121.208 ↑ 2.9 30,046 1

Hash Left Join (cost=237.44..3,065,484.18 rows=88,633 width=272) (actual time=21.028..44,121.208 rows=30,046 loops=1)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
18. 28.329 44,088.512 ↑ 2.9 30,046 1

Hash Left Join (cost=20.75..3,065,034.15 rows=88,633 width=257) (actual time=19.887..44,088.512 rows=30,046 loops=1)

  • Hash Cond: (ssg.service_group_id = sg.service_group_id)
19. 32.930 44,060.168 ↑ 2.9 30,046 1

Hash Left Join (cost=17.81..3,064,709.57 rows=88,633 width=251) (actual time=19.861..44,060.168 rows=30,046 loops=1)

  • Hash Cond: (bc.service_sub_group_id = ssg.service_sub_group_id)
20. 38.483 44,027.181 ↑ 2.9 30,046 1

Hash Join (cost=5.88..3,064,447.63 rows=88,633 width=235) (actual time=19.762..44,027.181 rows=30,046 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
21. 29.408 43,988.667 ↑ 2.9 30,046 1

Nested Loop (cost=1.12..3,064,164.25 rows=88,633 width=225) (actual time=19.691..43,988.667 rows=30,046 loops=1)

22. 34.701 31,089.589 ↑ 3.0 6,490 1

Nested Loop Left Join (cost=0.56..707,575.72 rows=19,580 width=122) (actual time=18.851..31,089.589 rows=6,490 loops=1)

23. 30,866.678 30,866.678 ↑ 3.0 6,490 1

Parallel Seq Scan on bill b (cost=0.00..630,391.40 rows=19,580 width=56) (actual time=18.777..30,866.678 rows=6,490 loops=1)

  • Filter: ((status <> 'X'::bpchar) AND (date(open_date) >= '2020-01-31'::date) AND (date(open_date) <= '2020-01-31'::date))
  • Rows Removed by Filter: 9709248
24. 188.210 188.210 ↑ 1.0 1 6,490

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..3.94 rows=1 width=66) (actual time=0.029..0.029 rows=1 loops=6,490)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
25. 12,869.670 12,869.670 ↑ 40.6 5 6,490

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..114.27 rows=203 width=117) (actual time=0.489..1.983 rows=5 loops=6,490)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
26. 0.015 0.031 ↑ 1.0 60 1

Hash (cost=2.80..2.80 rows=60 width=22) (actual time=0.030..0.031 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
27. 0.016 0.016 ↑ 1.0 60 1

Seq Scan on chargehead_constants chc (cost=0.00..2.80 rows=60 width=22) (actual time=0.009..0.016 rows=60 loops=1)

28. 0.028 0.057 ↑ 1.0 159 1

Hash (cost=6.77..6.77 rows=159 width=24) (actual time=0.057..0.057 rows=159 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
29. 0.029 0.029 ↑ 1.0 159 1

Seq Scan on service_sub_groups ssg (cost=0.00..6.77 rows=159 width=24) (actual time=0.008..0.029 rows=159 loops=1)

30. 0.007 0.015 ↑ 1.0 31 1

Hash (cost=1.93..1.93 rows=31 width=14) (actual time=0.015..0.015 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
31. 0.008 0.008 ↑ 1.0 31 1

Seq Scan on service_groups sg (cost=0.00..1.93 rows=31 width=14) (actual time=0.005..0.008 rows=31 loops=1)

32. 0.446 1.116 ↑ 1.0 2,107 1

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

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

Seq Scan on doctors predoc (cost=0.00..148.21 rows=2,107 width=30) (actual time=0.017..0.670 rows=2,107 loops=1)

34. 0.023 0.103 ↑ 1.0 116 1

Hash (cost=13.69..13.69 rows=116 width=64) (actual time=0.103..0.103 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
35. 0.010 0.080 ↑ 1.0 116 1

Subquery Scan on tdep (cost=0.00..13.69 rows=116 width=64) (actual time=0.024..0.080 rows=116 loops=1)

36. 0.007 0.070 ↑ 1.0 116 1

Append (cost=0.00..10.21 rows=116 width=96) (actual time=0.023..0.070 rows=116 loops=1)

37. 0.029 0.029 ↑ 1.0 44 1

Seq Scan on services_departments (cost=0.00..2.54 rows=44 width=77) (actual time=0.022..0.029 rows=44 loops=1)

38. 0.002 0.015 ↑ 1.0 12 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.75 rows=12 width=77) (actual time=0.011..0.015 rows=12 loops=1)

39. 0.013 0.013 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.39 rows=12 width=55) (actual time=0.010..0.013 rows=12 loops=1)

40. 0.005 0.019 ↑ 1.0 60 1

Subquery Scan on *SELECT* 3 (cost=0.00..4.60 rows=60 width=77) (actual time=0.008..0.019 rows=60 loops=1)

41. 0.014 0.014 ↑ 1.0 60 1

Seq Scan on department (cost=0.00..2.80 rows=60 width=53) (actual time=0.008..0.014 rows=60 loops=1)

42. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=18.40..18.40 rows=280 width=170) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
43. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on store_retail_customers prc (cost=0.00..18.40 rows=280 width=170) (actual time=0.002..0.002 rows=0 loops=1)

44. 131.414 253.958 ↓ 1.0 446,019 1

Hash (cost=22,835.00..22,835.00 rows=445,600 width=39) (actual time=253.958..253.958 rows=446,019 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 35850kB
45. 122.544 122.544 ↓ 1.0 446,019 1

Seq Scan on incoming_sample_registration isr (cost=0.00..22,835.00 rows=445,600 width=39) (actual time=0.008..122.544 rows=446,019 loops=1)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.49 rows=1 width=49) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
47. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.63..1.63 rows=21 width=14) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on salutation_master smb (cost=0.00..1.63 rows=21 width=14) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.39..1.63 rows=1 width=20) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.83..0.95 rows=1 width=20) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.56..0.64 rows=1 width=38) (never executed)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.31 rows=1 width=10) (never executed)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
53. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.56..0.64 rows=1 width=38) (never executed)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
54. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.15..1.15 rows=5 width=20) (never executed)

55. 0.000 0.000 ↓ 0.0 0

Seq Scan on op_type_names otn (cost=0.00..1.15 rows=5 width=20) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Hash (cost=148.21..148.21 rows=2,107 width=30) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors dr (cost=0.00..148.21 rows=2,107 width=30) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Hash (cost=148.21..148.21 rows=2,107 width=30) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors rdoc (cost=0.00..148.21 rows=2,107 width=30) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Index Scan using referral_pkey on referral ref (cost=0.42..0.46 rows=1 width=28) (never executed)

  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
61. 0.000 0.000 ↓ 0.0 0

Hash (cost=53.22..53.22 rows=674 width=34) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Seq Scan on tpa_master ptpa (cost=0.00..53.22 rows=674 width=34) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_plan_main_pkey on insurance_plan_main pipm (cost=0.29..0.33 rows=1 width=48) (never executed)

  • Index Cond: (pri_plan.plan_id = plan_id)
64.          

SubPlan (for Nested Loop Left Join)

65. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.61..4.64 rows=1 width=32) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_claim_charge_id on bill_charge_claim (cost=0.56..4.60 rows=1 width=4) (never executed)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
67. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.61..4.64 rows=1 width=32) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bill_charge_claim_1 (cost=0.56..4.60 rows=1 width=4) (never executed)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
Planning time : 75.001 ms
Execution time : 44,541.329 ms