explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VKKF

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 44,659.312 ↓ 4.9 149,450 1

Sort (cost=523,647.18..523,722.73 rows=30,222 width=670) (actual time=44,586.895..44,659.312 rows=149,450 loops=1)

  • Sort Key: prmp.preauth_presc_id
  • Sort Method: external merge Disk: 41720kB
2. 4,577.684 43,941.653 ↓ 4.9 149,450 1

Hash Left Join (cost=407,747.85..503,119.94 rows=30,222 width=616) (actual time=31,040.648..43,941.653 rows=149,450 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
3. 184.668 39,363.969 ↓ 4.9 149,450 1

Hash Left Join (cost=407,746.56..486,232.10 rows=30,222 width=621) (actual time=31,040.416..39,363.969 rows=149,450 loops=1)

  • Hash Cond: (pips.plan_type_id = cats.category_id)
4. 186.813 39,179.301 ↓ 4.9 149,450 1

Hash Left Join (cost=407,483.73..485,289.29 rows=30,222 width=600) (actual time=31,037.745..39,179.301 rows=149,450 loops=1)

  • Hash Cond: (pip.plan_type_id = cat.category_id)
5. 114.803 38,992.488 ↓ 4.9 149,450 1

Hash Left Join (cost=407,220.91..484,346.47 rows=30,222 width=579) (actual time=31,034.900..38,992.488 rows=149,450 loops=1)

  • Hash Cond: ((pip.insurance_co)::text = (icm.insurance_co_id)::text)
6. 117.907 38,877.685 ↓ 4.9 149,450 1

Hash Left Join (cost=407,219.46..483,929.47 rows=30,222 width=570) (actual time=31,034.869..38,877.685 rows=149,450 loops=1)

  • Hash Cond: ((pip.sponsor_id)::text = (tp.tpa_id)::text)
7. 233.546 38,759.778 ↓ 4.9 149,450 1

Hash Left Join (cost=407,054.99..483,349.44 rows=30,222 width=558) (actual time=31,034.652..38,759.778 rows=149,450 loops=1)

  • Hash Cond: (pip.plan_id = pm.plan_id)
8. 491.398 38,526.232 ↓ 4.9 149,450 1

Nested Loop Left Join (cost=406,123.65..481,738.10 rows=30,222 width=555) (actual time=31,028.077..38,526.232 rows=149,450 loops=1)

  • Join Filter: ((prmp.preauth_payer_id)::text = (pips.insurance_co)::text)
  • Rows Removed by Join Filter: 1119
9. 61.058 32,206.284 ↓ 4.9 149,450 1

Hash Left Join (cost=406,081.61..442,483.02 rows=30,222 width=513) (actual time=31,027.915..32,206.284 rows=149,450 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (referral.referal_no)::text)
10. 125.067 32,145.126 ↓ 4.9 149,450 1

Hash Left Join (cost=406,058.67..442,345.62 rows=30,222 width=303) (actual time=31,027.803..32,145.126 rows=149,450 loops=1)

  • Hash Cond: ((pr.doctor)::text = (d.doctor_id)::text)
11. 441.316 32,020.012 ↓ 4.9 149,450 1

Hash Left Join (cost=406,051.47..441,938.20 rows=30,222 width=293) (actual time=31,027.727..32,020.012 rows=149,450 loops=1)

  • Hash Cond: (grp.consultation_id = dc.consultation_id)
12. 392.124 31,221.210 ↓ 4.9 149,450 1

Hash Right Join (cost=365,441.87..394,973.50 rows=30,222 width=285) (actual time=30,667.291..31,221.210 rows=149,450 loops=1)

  • Hash Cond: (((pip.insurance_co)::text = (prmp.preauth_payer_id)::text) AND ((pip.patient_id)::text = (pr.patient_id)::text))
13. 162.485 162.485 ↓ 1.0 361,547 1

Seq Scan on patient_insurance_plans pip (cost=0.00..20,098.16 rows=361,516 width=39) (actual time=0.032..162.485 rows=361,547 loops=1)

14. 104.157 30,666.601 ↓ 4.9 149,450 1

Hash (cost=363,955.54..363,955.54 rows=30,222 width=255) (actual time=30,666.601..30,666.601 rows=149,450 loops=1)

  • Buckets: 1024 Batches: 32 (originally 16) Memory Usage: 1025kB
15. 215.694 30,562.444 ↓ 4.9 149,450 1

Hash Join (cost=338,128.54..363,955.54 rows=30,222 width=255) (actual time=29,817.915..30,562.444 rows=149,450 loops=1)

  • Hash Cond: (grp.preauth_presc_id = prmp.preauth_presc_id)
16. 357.489 30,273.969 ↓ 4.9 149,450 1

Hash Join (cost=330,121.81..351,164.14 rows=30,222 width=127) (actual time=29,532.180..30,273.969 rows=149,450 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
17. 223.354 1,641.485 ↓ 1.6 149,450 1

Hash Join (cost=116,990.05..131,778.16 rows=90,667 width=60) (actual time=1,254.134..1,641.485 rows=149,450 loops=1)

  • Hash Cond: ((grp.visit_id)::text = (pr.patient_id)::text)
18. 18.835 655.748 ↓ 1.6 149,450 1

Subquery Scan on grp (cost=68,622.68..73,275.45 rows=90,667 width=17) (actual time=491.083..655.748 rows=149,450 loops=1)

19. 70.338 636.913 ↓ 1.6 149,450 1

Group (cost=68,622.68..72,368.78 rows=90,667 width=17) (actual time=491.081..636.913 rows=149,450 loops=1)

20. 416.098 566.575 ↓ 1.0 374,629 1

Sort (cost=68,622.68..69,559.20 rows=374,610 width=17) (actual time=491.077..566.575 rows=374,629 loops=1)

  • Sort Key: preauth_prescription_activities.preauth_presc_id, preauth_prescription_activities.consultation_id, preauth_prescription_activities.visit_id
  • Sort Method: external merge Disk: 9872kB
21. 150.477 150.477 ↓ 1.0 374,629 1

Seq Scan on preauth_prescription_activities (cost=0.00..18,578.10 rows=374,610 width=17) (actual time=0.020..150.477 rows=374,629 loops=1)

22. 280.866 762.383 ↓ 1.0 743,253 1

Hash (cost=31,819.72..31,819.72 rows=743,172 width=52) (actual time=762.383..762.383 rows=743,253 loops=1)

  • Buckets: 2048 Batches: 64 Memory Usage: 1015kB
23. 481.517 481.517 ↓ 1.0 743,253 1

Seq Scan on patient_registration pr (cost=0.00..31,819.72 rows=743,172 width=52) (actual time=0.012..481.517 rows=743,253 loops=1)

24. 812.602 28,274.995 ↓ 3.0 705,018 1

Hash (cost=207,210.26..207,210.26 rows=235,000 width=73) (actual time=28,274.995..28,274.995 rows=705,018 loops=1)

  • Buckets: 1024 Batches: 128 (originally 32) Memory Usage: 1025kB
25. 27,462.393 27,462.393 ↓ 3.0 705,018 1

Seq Scan on patient_details pd (cost=0.00..207,210.26 rows=235,000 width=73) (actual time=0.246..27,462.393 rows=705,018 loops=1)

  • Filter: patient_confidentiality_check(patient_group, mr_no)
26. 37.990 72.781 ↓ 1.0 158,513 1

Hash (cost=2,929.77..2,929.77 rows=158,477 width=132) (actual time=72.781..72.781 rows=158,513 loops=1)

  • Buckets: 1024 Batches: 32 Memory Usage: 229kB
27. 34.791 34.791 ↓ 1.0 158,513 1

Seq Scan on preauth_prescription prmp (cost=0.00..2,929.77 rows=158,477 width=132) (actual time=0.013..34.791 rows=158,513 loops=1)

28. 155.700 357.486 ↓ 1.0 729,044 1

Hash (cost=27,937.60..27,937.60 rows=728,960 width=12) (actual time=357.486..357.486 rows=729,044 loops=1)

  • Buckets: 4096 Batches: 64 Memory Usage: 500kB
29. 201.786 201.786 ↓ 1.0 729,044 1

Seq Scan on doctor_consultation dc (cost=0.00..27,937.60 rows=728,960 width=12) (actual time=0.010..201.786 rows=729,044 loops=1)

30. 0.023 0.047 ↑ 1.0 98 1

Hash (cost=5.98..5.98 rows=98 width=25) (actual time=0.047..0.047 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
31. 0.024 0.024 ↑ 1.0 98 1

Seq Scan on doctors d (cost=0.00..5.98 rows=98 width=25) (actual time=0.003..0.024 rows=98 loops=1)

32. 0.017 0.100 ↑ 1.4 98 1

Hash (cost=21.21..21.21 rows=138 width=250) (actual time=0.100..0.100 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
33. 0.062 0.083 ↑ 1.4 98 1

HashAggregate (cost=18.45..19.83 rows=138 width=92) (actual time=0.068..0.083 rows=98 loops=1)

34. 0.007 0.021 ↑ 1.4 98 1

Append (cost=0.00..17.76 rows=138 width=92) (actual time=0.003..0.021 rows=98 loops=1)

35. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on referral (cost=0.00..10.40 rows=40 width=256) (actual time=0.001..0.001 rows=0 loops=1)

36. 0.013 0.013 ↑ 1.0 98 1

Seq Scan on doctors (cost=0.00..5.98 rows=98 width=25) (actual time=0.001..0.013 rows=98 loops=1)

37. 1,644.212 5,828.550 ↑ 1.0 1 149,450

Hash Right Join (cost=42.04..43.33 rows=1 width=67) (actual time=0.033..0.039 rows=1 loops=149,450)

  • Hash Cond: ((icms.insurance_co_id)::text = (pips.insurance_co)::text)
38. 298.638 298.638 ↑ 1.0 20 149,319

Seq Scan on insurance_company_master icms (cost=0.00..1.20 rows=20 width=17) (actual time=0.001..0.002 rows=20 loops=149,319)

39. 3,885.700 3,885.700 ↑ 1.0 1 149,450

Hash (cost=42.03..42.03 rows=1 width=50) (actual time=0.026..0.026 rows=1 loops=149,450)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 287.318 3,736.250 ↑ 1.0 1 149,450

Nested Loop Left Join (cost=24.42..42.03 rows=1 width=50) (actual time=0.024..0.025 rows=1 loops=149,450)

41. 299.293 2,690.100 ↑ 1.0 1 149,450

Nested Loop Left Join (cost=0.71..14.30 rows=1 width=38) (actual time=0.017..0.018 rows=1 loops=149,450)

42. 1,942.850 1,942.850 ↑ 1.0 1 149,450

Index Scan using policy_patient_idx on patient_insurance_plans pips (cost=0.42..7.25 rows=1 width=35) (actual time=0.013..0.013 rows=1 loops=149,450)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
43. 447.957 447.957 ↑ 1.0 1 149,319

Index Scan using insurance_plan_main_pkey on insurance_plan_main pms (cost=0.29..7.04 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=149,319)

  • Index Cond: (plan_id = pips.plan_id)
44. 149.319 746.595 ↑ 1.0 1 149,319

Bitmap Heap Scan on tpa_master tps (cost=23.71..27.72 rows=1 width=22) (actual time=0.005..0.005 rows=1 loops=149,319)

  • Recheck Cond: ((tpa_id)::text = (pips.sponsor_id)::text)
45. 597.276 597.276 ↑ 1.0 1 149,319

Bitmap Index Scan on tpa_master_pkey (cost=0.00..23.71 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=149,319)

  • Index Cond: ((tpa_id)::text = (pips.sponsor_id)::text)
46. 3.068 6.549 ↓ 1.0 17,394 1

Hash (cost=713.93..713.93 rows=17,393 width=7) (actual time=6.549..6.549 rows=17,394 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 679kB
47. 3.481 3.481 ↓ 1.0 17,394 1

Seq Scan on insurance_plan_main pm (cost=0.00..713.93 rows=17,393 width=7) (actual time=0.003..3.481 rows=17,394 loops=1)

48. 0.009 0.192 ↑ 1.0 21 1

Hash (cost=164.21..164.21 rows=21 width=22) (actual time=0.192..0.192 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
49. 0.183 0.183 ↑ 1.0 21 1

Seq Scan on tpa_master tp (cost=0.00..164.21 rows=21 width=22) (actual time=0.017..0.183 rows=21 loops=1)

50. 0.007 0.011 ↑ 1.0 20 1

Hash (cost=1.20..1.20 rows=20 width=17) (actual time=0.011..0.011 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.004 0.004 ↑ 1.0 20 1

Seq Scan on insurance_company_master icm (cost=0.00..1.20 rows=20 width=17) (actual time=0.001..0.004 rows=20 loops=1)

52. 1.579 2.826 ↓ 1.0 8,482 1

Hash (cost=156.81..156.81 rows=8,481 width=25) (actual time=2.826..2.826 rows=8,482 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 486kB
53. 1.247 1.247 ↓ 1.0 8,482 1

Seq Scan on insurance_category_master cat (cost=0.00..156.81 rows=8,481 width=25) (actual time=0.007..1.247 rows=8,482 loops=1)

54. 1.547 2.650 ↓ 1.0 8,482 1

Hash (cost=156.81..156.81 rows=8,481 width=25) (actual time=2.650..2.650 rows=8,482 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 486kB
55. 1.103 1.103 ↓ 1.0 8,482 1

Seq Scan on insurance_category_master cats (cost=0.00..156.81 rows=8,481 width=25) (actual time=0.003..1.103 rows=8,482 loops=1)

56. 0.003 0.009 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=13) (actual time=0.009..0.009 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
57. 0.006 0.006 ↑ 1.0 13 1

Seq Scan on salutation_master sm (cost=0.00..1.13 rows=13 width=13) (actual time=0.003..0.006 rows=13 loops=1)