explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FKhu

Settings
# exclusive inclusive rows x rows loops node
1. 8,277.536 202,355.798 ↓ 1.2 3,131,718 1

Hash Left Join (cost=8,631,182.87..9,983,499.83 rows=2,532,406 width=661) (actual time=149,703.000..202,355.798 rows=3,131,718 loops=1)

  • Hash Cond: (bpa.dbaaddressid = bda.dbaaddressid)
2. 6,570.154 192,655.073 ↓ 1.2 3,131,718 1

Hash Left Join (cost=8,519,239.28..9,457,239.54 rows=2,532,406 width=504) (actual time=147,805.166..192,655.073 rows=3,131,718 loops=1)

  • Hash Cond: (svda.addressid = sva.addressid)
3. 6,297.100 185,482.896 ↓ 1.2 3,131,718 1

Hash Right Join (cost=8,443,010.00..9,036,000.69 rows=2,532,406 width=506) (actual time=147,091.707..185,482.896 rows=3,131,718 loops=1)

  • Hash Cond: (bbp.billid = b.billid)
4. 2,947.957 39,614.427 ↓ 201.6 10,863,627 1

Nested Loop Left Join (cost=2,075,702.45..2,504,589.19 rows=53,884 width=8) (actual time=7,373.879..39,614.427 rows=10,863,627 loops=1)

5. 1,050.903 14,939.216 ↓ 201.6 10,863,627 1

Subquery Scan on bbp (cost=2,075,702.02..2,425,950.81 rows=53,884 width=8) (actual time=7,373.841..14,939.216 rows=10,863,627 loops=1)

  • Filter: (bbp.rnk = 1)
  • Rows Removed by Filter: 682
6. 5,133.688 13,888.313 ↓ 1.0 10,864,309 1

WindowAgg (cost=2,075,702.02..2,291,239.74 rows=10,776,886 width=20) (actual time=7,373.839..13,888.313 rows=10,864,309 loops=1)

7. 6,315.221 8,754.625 ↓ 1.0 10,864,309 1

Sort (cost=2,075,702.02..2,102,644.23 rows=10,776,886 width=12) (actual time=7,373.820..8,754.625 rows=10,864,309 loops=1)

  • Sort Key: billprovider.billid, billprovider.billproviderid DESC
  • Sort Method: external merge Disk: 233944kB
8. 2,439.404 2,439.404 ↓ 1.0 10,864,309 1

Seq Scan on billprovider (cost=0.00..448,530.30 rows=10,776,886 width=12) (actual time=0.017..2,439.404 rows=10,864,309 loops=1)

  • Filter: (providerroleid = 1)
  • Rows Removed by Filter: 10657748
9. 21,727.254 21,727.254 ↑ 1.0 1 10,863,627

Index Scan using dbaprovideraddress_pkey on dbaprovideraddress bpa (cost=0.43..1.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10,863,627)

  • Index Cond: (dbaprovideraddressid = bbp.dbaprovideraddressid)
10. 3,634.319 139,571.369 ↓ 1.2 3,131,718 1

Hash (cost=6,172,430.48..6,172,430.48 rows=2,532,406 width=502) (actual time=139,571.369..139,571.369 rows=3,131,718 loops=1)

  • Buckets: 8192 Batches: 512 Memory Usage: 2519kB
11. 5,497.068 135,937.050 ↓ 1.2 3,131,718 1

Hash Right Join (cost=5,702,392.67..6,172,430.48 rows=2,532,406 width=502) (actual time=77,834.788..135,937.050 rows=3,131,718 loops=1)

  • Hash Cond: (rnbp.billid = b.billid)
12. 3,850.899 57,548.457 ↓ 198.9 6,199,027 1

Nested Loop Left Join (cost=1,365,295.52..1,671,601.24 rows=31,174 width=6) (actual time=4,848.237..57,548.457 rows=6,199,027 loops=1)

13. 2,921.528 41,299.504 ↓ 198.9 6,199,027 1

Nested Loop Left Join (cost=1,365,295.09..1,650,830.05 rows=31,174 width=8) (actual time=4,848.220..41,299.504 rows=6,199,027 loops=1)

14. 3,898.267 25,979.922 ↓ 198.9 6,199,027 1

Nested Loop Left Join (cost=1,365,294.67..1,636,457.44 rows=31,174 width=8) (actual time=4,848.202..25,979.922 rows=6,199,027 loops=1)

15. 779.506 9,683.601 ↓ 198.9 6,199,027 1

Subquery Scan on rnbp (cost=1,365,294.24..1,567,925.01 rows=31,174 width=8) (actual time=4,848.166..9,683.601 rows=6,199,027 loops=1)

  • Filter: (rnbp.rnk = 1)
  • Rows Removed by Filter: 76
16. 3,011.536 8,904.095 ↑ 1.0 6,199,103 1

WindowAgg (cost=1,365,294.24..1,489,990.10 rows=6,234,793 width=20) (actual time=4,848.164..8,904.095 rows=6,199,103 loops=1)

17. 3,733.300 5,892.559 ↑ 1.0 6,199,103 1

Sort (cost=1,365,294.24..1,380,881.22 rows=6,234,793 width=12) (actual time=4,848.150..5,892.559 rows=6,199,103 loops=1)

  • Sort Key: billprovider_1.billid, billprovider_1.billproviderid DESC
  • Sort Method: external merge Disk: 133512kB
18. 2,159.259 2,159.259 ↑ 1.0 6,199,103 1

Seq Scan on billprovider billprovider_1 (cost=0.00..448,530.30 rows=6,234,793 width=12) (actual time=0.015..2,159.259 rows=6,199,103 loops=1)

  • Filter: (providerroleid = 2)
  • Rows Removed by Filter: 15322954
19. 12,398.054 12,398.054 ↑ 1.0 1 6,199,027

Index Scan using dbaprovideraddress_pkey on dbaprovideraddress rnpa (cost=0.43..2.20 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=6,199,027)

  • Index Cond: (dbaprovideraddressid = rnbp.dbaprovideraddressid)
20. 12,398.054 12,398.054 ↑ 1.0 1 6,199,027

Index Scan using dbaaddress_pkey on dbaaddress rnda (cost=0.42..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=6,199,027)

  • Index Cond: (dbaaddressid = rnpa.dbaaddressid)
21. 12,398.054 12,398.054 ↑ 1.0 1 6,199,027

Index Scan using _dta_index_address_9_309576141__k1_12 on address rna (cost=0.43..0.67 rows=1 width=6) (actual time=0.002..0.002 rows=1 loops=6,199,027)

  • Index Cond: (rnda.addressid = addressid)
22. 3,460.448 72,891.525 ↓ 1.2 3,131,718 1

Hash (cost=4,142,220.07..4,142,220.07 rows=2,532,406 width=500) (actual time=72,891.525..72,891.525 rows=3,131,718 loops=1)

  • Buckets: 8192 Batches: 512 Memory Usage: 2506kB
23. 1,026.438 69,431.077 ↓ 1.2 3,131,718 1

Hash Join (cost=3,460,451.81..4,142,220.07 rows=2,532,406 width=500) (actual time=31,762.506..69,431.077 rows=3,131,718 loops=1)

  • Hash Cond: (c.insurerid = i.insurerid)
24. 1,026.678 68,404.468 ↓ 1.2 3,131,720 1

Hash Join (cost=3,460,430.83..4,135,502.70 rows=2,532,406 width=480) (actual time=31,762.328..68,404.468 rows=3,131,720 loops=1)

  • Hash Cond: (c.clientaccountid = ca.clientaccountid)
25. 934.348 67,374.866 ↓ 1.2 3,131,720 1

Hash Join (cost=3,460,090.94..4,128,512.07 rows=2,532,406 width=450) (actual time=31,759.337..67,374.866 rows=3,131,720 loops=1)

  • Hash Cond: (c.claimsystemid = cs.claimsystemid)
26. 6,200.671 66,440.440 ↓ 1.2 3,133,628 1

Hash Join (cost=3,460,081.13..4,121,592.67 rows=2,580,873 width=433) (actual time=31,759.251..66,440.440 rows=3,133,628 loops=1)

  • Hash Cond: (cp.patientid = p.patientid)
27. 4,472.334 59,739.175 ↓ 1.2 3,133,628 1

Hash Right Join (cost=3,395,784.62..3,759,142.35 rows=2,580,873 width=424) (actual time=30,904.367..59,739.175 rows=3,133,628 loops=1)

  • Hash Cond: (svbp.billid = b.billid)
28. 1,389.770 28,073.869 ↓ 197.5 4,458,563 1

Nested Loop Left Join (cost=1,033,858.75..1,255,702.80 rows=22,574 width=8) (actual time=3,707.854..28,073.869 rows=4,458,563 loops=1)

29. 1,840.375 17,766.973 ↓ 197.5 4,458,563 1

Nested Loop Left Join (cost=1,033,858.33..1,245,295.18 rows=22,574 width=8) (actual time=3,707.840..17,766.973 rows=4,458,563 loops=1)

30. 500.188 7,009.472 ↓ 197.5 4,458,563 1

Subquery Scan on svbp (cost=1,033,857.90..1,180,589.75 rows=22,574 width=8) (actual time=3,707.809..7,009.472 rows=4,458,563 loops=1)

  • Filter: (svbp.rnk = 1)
  • Rows Removed by Filter: 82
31. 2,147.164 6,509.284 ↑ 1.0 4,458,645 1

WindowAgg (cost=1,033,857.90..1,124,154.42 rows=4,514,826 width=20) (actual time=3,707.807..6,509.284 rows=4,458,645 loops=1)

32. 2,436.465 4,362.120 ↑ 1.0 4,458,645 1

Sort (cost=1,033,857.90..1,045,144.97 rows=4,514,826 width=12) (actual time=3,707.789..4,362.120 rows=4,458,645 loops=1)

  • Sort Key: billprovider_2.billid, billprovider_2.billproviderid DESC
  • Sort Method: external merge Disk: 96048kB
33. 1,701.849 1,925.655 ↑ 1.0 4,458,645 1

Bitmap Heap Scan on billprovider billprovider_2 (cost=144,622.46..380,506.79 rows=4,514,826 width=12) (actual time=234.285..1,925.655 rows=4,458,645 loops=1)

  • Recheck Cond: (providerroleid = 6)
  • Rows Removed by Index Recheck: 8942607
  • Heap Blocks: exact=57897 lossy=99337
34. 223.806 223.806 ↑ 1.0 4,459,044 1

Bitmap Index Scan on _dta_index_billprovider_9_707533604__k6_k3_k5_k2_k7 (cost=0.00..143,493.76 rows=4,514,826 width=0) (actual time=223.806..223.806 rows=4,459,044 loops=1)

  • Index Cond: (providerroleid = 6)
35. 8,917.126 8,917.126 ↑ 1.0 1 4,458,563

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,458,563)

  • Index Cond: (dbaprovideraddressid = svbp.dbaprovideraddressid)
36. 8,917.126 8,917.126 ↑ 1.0 1 4,458,563

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,458,563)

  • Index Cond: (dbaaddressid = svpa.dbaaddressid)
37. 2,780.601 27,192.972 ↓ 1.2 3,133,628 1

Hash (cost=2,188,522.96..2,188,522.96 rows=2,580,873 width=420) (actual time=27,192.972..27,192.972 rows=3,133,628 loops=1)

  • Buckets: 16384 Batches: 512 Memory Usage: 2059kB
38. 1,147.538 24,412.371 ↓ 1.2 3,133,628 1

Hash Left Join (cost=202,675.79..2,188,522.96 rows=2,580,873 width=420) (actual time=2,382.976..24,412.371 rows=3,133,628 loops=1)

  • Hash Cond: (c.adjusterid = a.adjusterid)
39. 955.792 23,262.461 ↓ 1.2 3,133,628 1

Hash Left Join (cost=202,436.07..2,181,504.56 rows=2,580,873 width=406) (actual time=2,380.568..23,262.461 rows=3,133,628 loops=1)

  • Hash Cond: ((bd.paymentauthorizationcode)::text = (pac.paymentauthorizationcodeid)::text)
40. 1,034.958 22,306.658 ↓ 1.2 3,133,628 1

Hash Join (cost=202,415.04..2,174,650.74 rows=2,580,873 width=290) (actual time=2,380.540..22,306.658 rows=3,133,628 loops=1)

  • Hash Cond: ((b.billstatus)::text = (bs.billstatusid)::text)
41. 4,791.425 21,271.676 ↓ 1.2 3,133,628 1

Hash Join (cost=202,412.69..2,167,395.05 rows=2,580,873 width=272) (actual time=2,380.501..21,271.676 rows=3,133,628 loops=1)

  • Hash Cond: (b.claimpatientid = cp.claimpatientid)
42. 1,652.285 14,100.414 ↓ 1.2 3,135,825 1

Merge Left Join (cost=42.70..1,744,967.22 rows=2,582,422 width=240) (actual time=0.112..14,100.414 rows=3,135,825 loops=1)

  • Merge Cond: (b.billid = bd.billid)
43. 2,729.507 10,773.812 ↓ 1.2 3,135,825 1

Merge Left Join (cost=21.62..1,365,905.02 rows=2,582,422 width=238) (actual time=0.081..10,773.812 rows=3,135,825 loops=1)

  • Merge Cond: (b.billid = brbd.billid)
  • Filter: (brbd.pwtkheaderid IS NULL)
  • Rows Removed by Filter: 6333366
44. 6,279.766 6,279.766 ↑ 1.0 9,469,191 1

Index Scan using bill_pkey on bill b (cost=0.43..919,423.92 rows=9,473,300 width=225) (actual time=0.050..6,279.766 rows=9,469,191 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
45. 1,764.539 1,764.539 ↑ 1.0 9,551,260 1

Index Scan using ix_billreviewbilldetail_billid_includes on billreviewbilldetail brbd (cost=0.43..317,108.13 rows=9,564,245 width=17) (actual time=0.027..1,764.539 rows=9,551,260 loops=1)

46. 1,674.317 1,674.317 ↑ 1.0 9,314,500 1

Index Scan using billdetails_pkey on billdetails bd (cost=0.43..327,372.97 rows=9,363,366 width=6) (actual time=0.027..1,674.317 rows=9,314,500 loops=1)

47. 404.747 2,379.837 ↑ 1.0 1,904,713 1

Hash (cost=163,654.10..163,654.10 rows=1,905,992 width=36) (actual time=2,379.837..2,379.837 rows=1,904,713 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2570kB
48. 959.960 1,975.090 ↑ 1.0 1,904,713 1

Hash Join (cost=93,750.93..163,654.10 rows=1,905,992 width=36) (actual time=795.174..1,975.090 rows=1,904,713 loops=1)

  • Hash Cond: (cp.claimid = c.claimid)
49. 220.597 220.597 ↑ 1.0 1,904,713 1

Seq Scan on claimpatient cp (cost=0.00..33,391.92 rows=1,905,992 width=12) (actual time=0.005..220.597 rows=1,904,713 loops=1)

50. 373.022 794.533 ↓ 1.0 1,888,136 1

Hash (cost=57,280.86..57,280.86 rows=1,886,086 width=32) (actual time=794.533..794.533 rows=1,888,136 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2421kB
51. 421.511 421.511 ↓ 1.0 1,888,136 1

Seq Scan on claim c (cost=0.00..57,280.86 rows=1,886,086 width=32) (actual time=0.004..421.511 rows=1,888,136 loops=1)

52. 0.014 0.024 ↑ 1.0 60 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
53. 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.005..0.010 rows=60 loops=1)

54. 0.004 0.011 ↑ 98.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.007 0.007 ↑ 98.0 5 1

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

56. 1.143 2.372 ↑ 1.0 6,921 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 391kB
57. 1.229 1.229 ↑ 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.229 rows=6,921 loops=1)

58. 282.105 500.594 ↑ 1.0 1,551,266 1

Hash (cost=35,800.67..35,800.67 rows=1,552,067 width=17) (actual time=500.594..500.594 rows=1,551,266 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2994kB
59. 218.489 218.489 ↑ 1.0 1,551,266 1

Seq Scan on patient p (cost=0.00..35,800.67 rows=1,552,067 width=17) (actual time=0.003..218.489 rows=1,551,266 loops=1)

60. 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
61. 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.005..0.041 rows=210 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 735kB
63. 1.385 1.385 ↑ 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.385 rows=8,884 loops=1)

64. 0.093 0.171 ↑ 1.0 577 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
65. 0.078 0.078 ↑ 1.0 577 1

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

66. 261.743 602.023 ↑ 1.0 1,784,534 1

Hash (cost=46,948.79..46,948.79 rows=1,784,679 width=6) (actual time=602.023..602.023 rows=1,784,534 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3220kB
67. 340.280 340.280 ↑ 1.0 1,784,534 1

Seq Scan on address sva (cost=0.00..46,948.79 rows=1,784,679 width=6) (actual time=0.008..340.280 rows=1,784,534 loops=1)

68. 88.986 1,423.189 ↑ 1.0 615,208 1

Hash (cost=101,848.39..101,848.39 rows=615,296 width=6) (actual time=1,423.189..1,423.189 rows=615,208 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2507kB
69. 659.200 1,334.203 ↑ 1.0 615,208 1

Hash Left Join (cost=76,229.28..101,848.39 rows=615,296 width=6) (actual time=593.376..1,334.203 rows=615,208 loops=1)

  • Hash Cond: (bda.addressid = ba.addressid)
70. 82.473 82.473 ↑ 1.0 615,208 1

Seq Scan on dbaaddress bda (cost=0.00..12,223.96 rows=615,296 width=8) (actual time=0.006..82.473 rows=615,208 loops=1)

71. 261.294 592.530 ↑ 1.0 1,784,534 1

Hash (cost=46,948.79..46,948.79 rows=1,784,679 width=6) (actual time=592.530..592.530 rows=1,784,534 loops=1)

  • Buckets: 131072 Batches: 32 Memory Usage: 3220kB
72. 331.236 331.236 ↑ 1.0 1,784,534 1

Seq Scan on address ba (cost=0.00..46,948.79 rows=1,784,679 width=6) (actual time=0.005..331.236 rows=1,784,534 loops=1)

Planning time : 8.159 ms
Execution time : 202,594.919 ms