explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3HaO : Optimization for: plan #LwSX

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7,439.353 118,741.937 ↓ 1.2 3,134,038 1

Hash Left Join (cost=8,415,931.55..9,289,580.13 rows=2,533,267 width=565) (actual time=99,098.795..118,741.937 rows=3,134,038 loops=1)

  • Hash Cond: (svda.addressid = sva.addressid)
2. 5,914.800 110,714.824 ↓ 1.2 3,134,038 1

Hash Right Join (cost=8,339,682.36..8,854,154.60 rows=2,533,267 width=500) (actual time=98,482.281..110,714.824 rows=3,134,038 loops=1)

  • Hash Cond: (bbp.billid = b.billid)
3. 1,270.594 13,807.212 ↓ 201.6 10,865,071 1

Subquery Scan on bbp (cost=2,076,100.32..2,426,413.53 rows=53,894 width=8) (actual time=7,445.610..13,807.212 rows=10,865,071 loops=1)

  • Filter: (bbp.rnk = 1)
  • Rows Removed by Filter: 682
4. 4,077.927 12,536.618 ↓ 1.0 10,865,753 1

WindowAgg (cost=2,076,100.32..2,291,677.68 rows=10,778,868 width=20) (actual time=7,445.608..12,536.618 rows=10,865,753 loops=1)

5. 6,026.808 8,458.691 ↓ 1.0 10,865,753 1

Sort (cost=2,076,100.32..2,103,047.49 rows=10,778,868 width=12) (actual time=7,445.591..8,458.691 rows=10,865,753 loops=1)

  • Sort Key: billprovider.billid, billprovider.billproviderid DESC
  • Sort Method: external merge Disk: 233976kB
6. 2,431.883 2,431.883 ↓ 1.0 10,865,753 1

Seq Scan on billprovider (cost=0.00..448,612.79 rows=10,778,868 width=12) (actual time=0.013..2,431.883 rows=10,865,753 loops=1)

  • Filter: (providerroleid = 1)
  • Rows Removed by Filter: 10659863
7. 2,901.038 90,992.812 ↓ 1.2 3,134,038 1

Hash (cost=6,068,639.21..6,068,639.21 rows=2,533,267 width=500) (actual time=90,992.812..90,992.812 rows=3,134,038 loops=1)

  • Buckets: 8192 Batches: 512 Memory Usage: 2508kB
8. 5,717.382 88,091.774 ↓ 1.2 3,134,038 1

Hash Right Join (cost=5,702,184.01..6,068,639.21 rows=2,533,267 width=500) (actual time=78,595.691..88,091.774 rows=3,134,038 loops=1)

  • Hash Cond: (rnbp.billid = b.billid)
9. 780.318 8,637.691 ↓ 198.9 6,200,147 1

Subquery Scan on rnbp (cost=1,365,549.45..1,568,217.50 rows=31,180 width=8) (actual time=4,844.712..8,637.691 rows=6,200,147 loops=1)

  • Filter: (rnbp.rnk = 1)
  • Rows Removed by Filter: 76
10. 2,412.850 7,857.373 ↑ 1.0 6,200,223 1

WindowAgg (cost=1,365,549.45..1,490,268.25 rows=6,235,940 width=20) (actual time=4,844.711..7,857.373 rows=6,200,223 loops=1)

11. 3,269.739 5,444.523 ↑ 1.0 6,200,223 1

Sort (cost=1,365,549.45..1,381,139.30 rows=6,235,940 width=12) (actual time=4,844.696..5,444.523 rows=6,200,223 loops=1)

  • Sort Key: billprovider_1.billid, billprovider_1.billproviderid DESC
  • Sort Method: external merge Disk: 133536kB
12. 2,174.784 2,174.784 ↑ 1.0 6,200,223 1

Seq Scan on billprovider billprovider_1 (cost=0.00..448,612.79 rows=6,235,940 width=12) (actual time=0.014..2,174.784 rows=6,200,223 loops=1)

  • Filter: (providerroleid = 2)
  • Rows Removed by Filter: 15325393
13. 4,065.274 73,736.701 ↓ 1.2 3,134,038 1

Hash (cost=4,141,691.72..4,141,691.72 rows=2,533,267 width=500) (actual time=73,736.701..73,736.701 rows=3,134,038 loops=1)

  • Buckets: 8192 Batches: 512 Memory Usage: 2508kB
14. 1,044.587 69,671.427 ↓ 1.2 3,134,038 1

Hash Join (cost=3,459,729.64..4,141,691.72 rows=2,533,267 width=500) (actual time=32,855.820..69,671.427 rows=3,134,038 loops=1)

  • Hash Cond: (c.insurerid = i.insurerid)
15. 1,056.011 68,626.672 ↓ 1.2 3,134,040 1

Hash Join (cost=3,459,708.66..4,134,972.08 rows=2,533,267 width=480) (actual time=32,855.643..68,626.672 rows=3,134,040 loops=1)

  • Hash Cond: (c.clientaccountid = ca.clientaccountid)
16. 974.063 67,567.670 ↓ 1.2 3,134,040 1

Hash Join (cost=3,459,368.77..4,127,979.19 rows=2,533,267 width=450) (actual time=32,852.574..67,567.670 rows=3,134,040 loops=1)

  • Hash Cond: (c.claimsystemid = cs.claimsystemid)
17. 5,966.643 66,593.529 ↓ 1.2 3,135,948 1

Hash Join (cost=3,459,358.96..4,121,057.44 rows=2,581,751 width=433) (actual time=32,852.480..66,593.529 rows=3,135,948 loops=1)

  • Hash Cond: (cp.patientid = p.patientid)
18. 4,153.296 60,111.919 ↓ 1.2 3,135,948 1

Hash Right Join (cost=3,395,064.45..3,758,512.81 rows=2,581,751 width=424) (actual time=31,986.612..60,111.919 rows=3,135,948 loops=1)

  • Hash Cond: (svbp.billid = b.billid)
19. 1,292.590 27,689.638 ↓ 197.5 4,459,558 1

Nested Loop Left Join (cost=1,034,066.29..1,255,952.95 rows=22,578 width=8) (actual time=3,714.162..27,689.638 rows=4,459,558 loops=1)

20. 1,673.851 17,477.932 ↓ 197.5 4,459,558 1

Nested Loop Left Join (cost=1,034,065.86..1,245,543.47 rows=22,578 width=8) (actual time=3,714.151..17,477.932 rows=4,459,558 loops=1)

21. 493.678 6,884.965 ↓ 197.5 4,459,558 1

Subquery Scan on svbp (cost=1,034,065.44..1,180,824.26 rows=22,578 width=8) (actual time=3,714.120..6,884.965 rows=4,459,558 loops=1)

  • Filter: (svbp.rnk = 1)
  • Rows Removed by Filter: 82
22. 2,106.700 6,391.287 ↑ 1.0 4,459,640 1

WindowAgg (cost=1,034,065.44..1,124,378.56 rows=4,515,656 width=20) (actual time=3,714.117..6,391.287 rows=4,459,640 loops=1)

23. 2,377.556 4,284.587 ↑ 1.0 4,459,640 1

Sort (cost=1,034,065.44..1,045,354.58 rows=4,515,656 width=12) (actual time=3,714.103..4,284.587 rows=4,459,640 loops=1)

  • Sort Key: billprovider_2.billid, billprovider_2.billproviderid DESC
  • Sort Method: external merge Disk: 96072kB
24. 1,693.395 1,907.031 ↑ 1.0 4,459,640 1

Bitmap Heap Scan on billprovider billprovider_2 (cost=144,660.90..380,588.60 rows=4,515,656 width=12) (actual time=224.377..1,907.031 rows=4,459,640 loops=1)

  • Recheck Cond: (providerroleid = 6)
  • Rows Removed by Index Recheck: 8942290
  • Heap Blocks: exact=57928 lossy=99339
25. 213.636 213.636 ↑ 1.0 4,460,080 1

Bitmap Index Scan on _dta_index_billprovider_9_707533604__k6_k3_k5_k2_k7 (cost=0.00..143,531.98 rows=4,515,656 width=0) (actual time=213.636..213.636 rows=4,460,080 loops=1)

  • Index Cond: (providerroleid = 6)
26. 8,919.116 8,919.116 ↑ 1.0 1 4,459,558

Index Scan using dbaprovideraddress_pkey on dbaprovideraddress svpa (cost=0.43..2.87 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,459,558)

  • Index Cond: (dbaprovideraddressid = svbp.dbaprovideraddressid)
27. 8,919.116 8,919.116 ↑ 1.0 1 4,459,558

Index Scan using dbaaddress_pkey on dbaaddress svda (cost=0.42..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=4,459,558)

  • Index Cond: (dbaaddressid = svpa.dbaaddressid)
28. 2,895.772 28,268.985 ↓ 1.2 3,135,948 1

Hash (cost=2,187,536.27..2,187,536.27 rows=2,581,751 width=420) (actual time=28,268.985..28,268.985 rows=3,135,948 loops=1)

  • Buckets: 16384 Batches: 512 Memory Usage: 2060kB
29. 1,148.613 25,373.213 ↓ 1.2 3,135,948 1

Hash Left Join (cost=202,737.53..2,187,536.27 rows=2,581,751 width=420) (actual time=2,459.503..25,373.213 rows=3,135,948 loops=1)

  • Hash Cond: (c.adjusterid = a.adjusterid)
30. 939.194 24,222.459 ↓ 1.2 3,135,948 1

Hash Left Join (cost=202,497.81..2,180,515.57 rows=2,581,751 width=406) (actual time=2,457.325..24,222.459 rows=3,135,948 loops=1)

  • Hash Cond: ((bd.paymentauthorizationcode)::text = (pac.paymentauthorizationcodeid)::text)
31. 1,055.061 23,283.256 ↓ 1.2 3,135,948 1

Hash Join (cost=202,476.79..2,173,659.41 rows=2,581,751 width=290) (actual time=2,457.302..23,283.256 rows=3,135,948 loops=1)

  • Hash Cond: ((b.billstatus)::text = (bs.billstatusid)::text)
32. 5,182.453 22,228.174 ↓ 1.2 3,135,948 1

Hash Join (cost=202,474.44..2,166,401.27 rows=2,581,751 width=272) (actual time=2,457.230..22,228.174 rows=3,135,948 loops=1)

  • Hash Cond: (b.claimpatientid = cp.claimpatientid)
33. 1,691.609 14,589.104 ↓ 1.2 3,138,145 1

Merge Left Join (cost=43.82..1,743,839.64 rows=2,583,301 width=240) (actual time=0.107..14,589.104 rows=3,138,145 loops=1)

  • Merge Cond: (b.billid = bd.billid)
34. 2,860.547 11,161.320 ↓ 1.2 3,138,145 1

Merge Left Join (cost=21.62..1,366,358.99 rows=2,583,301 width=238) (actual time=0.077..11,161.320 rows=3,138,145 loops=1)

  • Merge Cond: (b.billid = brbd.billid)
  • Filter: (brbd.pwtkheaderid IS NULL)
  • Rows Removed by Filter: 6333366
35. 6,510.759 6,510.759 ↑ 1.0 9,471,511 1

Index Scan using bill_pkey on bill b (cost=0.43..919,763.25 rows=9,476,525 width=225) (actual time=0.040..6,510.759 rows=9,471,511 loops=1)

  • Filter: ((usernumber <> 99991) AND (((billstatus)::text <> ALL ('{z,rc,rd,pa}'::text[])) OR (((billstatus)::text = ANY ('{z,rc,rd,pa}'::text[])) AND (createdate > (now() - '180 days'::interval)))))
  • Rows Removed by Filter: 1609976
36. 1,790.014 1,790.014 ↑ 1.0 9,552,643 1

Index Scan using ix_billreviewbilldetail_billid_includes on billreviewbilldetail brbd (cost=0.43..317,188.59 rows=9,566,609 width=17) (actual time=0.033..1,790.014 rows=9,552,643 loops=1)

37. 1,736.175 1,736.175 ↓ 1.0 9,315,909 1

Index Scan using billdetails_pkey on billdetails bd (cost=0.43..326,880.40 rows=9,298,253 width=6) (actual time=0.026..1,736.175 rows=9,315,909 loops=1)

38. 400.755 2,456.617 ↑ 1.0 1,904,992 1

Hash (cost=163,704.06..163,704.06 rows=1,906,524 width=36) (actual time=2,456.617..2,456.617 rows=1,904,992 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2570kB
39. 996.007 2,055.862 ↑ 1.0 1,904,992 1

Hash Join (cost=93,780.19..163,704.06 rows=1,906,524 width=36) (actual time=823.809..2,055.862 rows=1,904,992 loops=1)

  • Hash Cond: (cp.claimid = c.claimid)
40. 236.706 236.706 ↑ 1.0 1,904,992 1

Seq Scan on claimpatient cp (cost=0.00..33,401.24 rows=1,906,524 width=12) (actual time=0.005..236.706 rows=1,904,992 loops=1)

41. 378.260 823.149 ↓ 1.0 1,888,413 1

Hash (cost=57,298.75..57,298.75 rows=1,886,675 width=32) (actual time=823.149..823.149 rows=1,888,413 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2421kB
42. 444.889 444.889 ↓ 1.0 1,888,413 1

Seq Scan on claim c (cost=0.00..57,298.75 rows=1,886,675 width=32) (actual time=0.005..444.889 rows=1,888,413 loops=1)

43. 0.011 0.021 ↑ 1.0 60 1

Hash (cost=1.60..1.60 rows=60 width=20) (actual time=0.021..0.021 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
44. 0.010 0.010 ↑ 1.0 60 1

Seq Scan on billstatus bs (cost=0.00..1.60 rows=60 width=20) (actual time=0.004..0.010 rows=60 loops=1)

45. 0.003 0.009 ↑ 98.0 5 1

Hash (cost=14.90..14.90 rows=490 width=138) (actual time=0.008..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.006 0.006 ↑ 98.0 5 1

Seq Scan on paymentauthorizationcode pac (cost=0.00..14.90 rows=490 width=138) (actual time=0.005..0.006 rows=5 loops=1)

47. 1.114 2.141 ↑ 1.0 6,921 1

Hash (cost=153.21..153.21 rows=6,921 width=18) (actual time=2.141..2.141 rows=6,921 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 391kB
48. 1.027 1.027 ↑ 1.0 6,921 1

Seq Scan on adjuster a (cost=0.00..153.21 rows=6,921 width=18) (actual time=0.005..1.027 rows=6,921 loops=1)

49. 281.127 514.967 ↑ 1.0 1,551,701 1

Hash (cost=35,804.56..35,804.56 rows=1,551,756 width=17) (actual time=514.967..514.967 rows=1,551,701 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2996kB
50. 233.840 233.840 ↑ 1.0 1,551,701 1

Seq Scan on patient p (cost=0.00..35,804.56 rows=1,551,756 width=17) (actual time=0.003..233.840 rows=1,551,701 loops=1)

51. 0.037 0.078 ↓ 1.0 210 1

Hash (cost=7.20..7.20 rows=209 width=25) (actual time=0.078..0.078 rows=210 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
52. 0.041 0.041 ↓ 1.0 210 1

Seq Scan on claimsystem cs (cost=0.00..7.20 rows=209 width=25) (actual time=0.006..0.041 rows=210 loops=1)

  • Filter: (claimsystemid <> ALL ('{1007,1009,1010,999}'::integer[]))
  • Rows Removed by Filter: 4
53. 1.528 2.991 ↑ 1.0 8,884 1

Hash (cost=228.84..228.84 rows=8,884 width=34) (actual time=2.991..2.991 rows=8,884 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 735kB
54. 1.463 1.463 ↑ 1.0 8,884 1

Seq Scan on clientaccount ca (cost=0.00..228.84 rows=8,884 width=34) (actual time=0.002..1.463 rows=8,884 loops=1)

55. 0.092 0.168 ↑ 1.0 577 1

Hash (cost=13.77..13.77 rows=577 width=24) (actual time=0.167..0.168 rows=577 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
56. 0.076 0.076 ↑ 1.0 577 1

Seq Scan on insurer i (cost=0.00..13.77 rows=577 width=24) (actual time=0.004..0.076 rows=577 loops=1)

57. 254.039 587.760 ↑ 1.0 1,784,831 1

Hash (cost=46,962.75..46,962.75 rows=1,785,075 width=6) (actual time=587.760..587.760 rows=1,784,831 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3221kB
58. 333.721 333.721 ↑ 1.0 1,784,831 1

Seq Scan on address sva (cost=0.00..46,962.75 rows=1,785,075 width=6) (actual time=0.015..333.721 rows=1,784,831 loops=1)

Planning time : 8.963 ms
Execution time : 118,939.355 ms