explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I7JL : Optimization for: plan #8ya3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.521 8,189.724 ↑ 564.9 1,110 1

Subquery Scan on FlatProductItem3 (cost=439,119,039.76..540,768,014.16 rows=627,073 width=1,125) (actual time=8,048.973..8,189.724 rows=1,110 loops=1)

  • CASE WHEN (max(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."costForAdditional" ELSE COALESCE(resourcerate."MSRPcostForAdditional", resourcerate."costForAdditional") END ELSE parentrate."costForAdditional" END) > '0'::numeric) THEN 1 ELSE 0 END) <= 1))))
2. 53.768 8,189.203 ↑ 564.9 1,110 1

GroupAggregate (cost=439,119,039.76..540,761,743.43 rows=627,073 width=1,370) (actual time=8,048.972..8,189.203 rows=1,110 loops=1)

  • Group Key: owner."AccountID", resource."resourceID", (COALESCE(planresource."MPNumber", resource."MPNumber")), "*SELECT* 1"."BillingPeriod", "*SELECT* 1"."BillingPeriodType
  • Filter: ((NOT (min(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPsetupFee", resourcerate."setupFee") ELSE resourcerate."setupFee" END) IS DISTINCT FROM max(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPsetupFee", resourcerate."setupFee") ELSE resourcerate."setupFee" END))) AND (NOT (min(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPrecurringFee", resourcerate."recurringFee") ELSE resourcerate."recurringFee" END) IS DISTINCT FROM max(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPrecurringFee", resourcerate."recurringFee") ELSE resourcerate."recurringFee" END))) AND (NOT (min(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPcostForAdditional", resourcerate."costForAdditional") ELSE resourcerate."costForAdditional" END) IS DISTINCT FROM max(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPcostForAdditional", resourcerate."costForAdditional") ELSE resourcerate."costForAdditional" END))) AND (NOT (min(resourcerate."MSRPsetupFee") IS DISTINCT FROM max(resourcerate."MSRPsetupFee"))) AND (NOT (min(resourcerate."MSRPrecurringFee") IS DISTINCT FROM max(resourcerate."MSRPrecurringFee"))) AND (NOT (min(resourcerate."MSRPcostForAdditional") IS DISTINCT FROM max(resourcerate."MSRPcostForAdditional"))) AND (((CASE WHEN (max(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPsetupFee", resourcerate."setupFee") ELSE resourcerate."setupFee" END) > '0'::numeric) THEN 1 ELSE 0 END + CASE WHEN (max(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPrecurringFee", resourcerate."recurringFee") ELSE resourcerate."recurringFee" END) > '0'::numeric) THEN 1 ELSE 0 END) + CASE WHEN (max(CASE WHEN "*SELECT* 1"."isDelegated" THEN COALESCE(resourcerate."MSRPcostForAdditional", resourcerate."costForAdditional") ELSE resourcerate."costForAdditional" END) > '0'::numeric) THEN 1 ELSE 0 END) <= 1) AND (((CASE WHEN (max(resourcerate."MSRPsetupFee") > '0'::numeric) THEN 1 ELSE 0 END + CASE WHEN (max(resourcerate."MSRPrecurringFee") > '0'::numeric) THEN 1 ELSE 0 END) + CASE WHEN (max(resourcerate."MSRPcostForAdditional") > '0'::numeric) THEN 1 ELSE 0 END) <= 1) AND ((owner."IsProvider" <> 0) OR ((NOT (min(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."setupFee" ELSE COALESCE(resourcerate."MSRPsetupFee", resourcerate."setupFee") END ELSE parentrate."setupFee" END) IS DISTINCT FROM max(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."setupFee" ELSE COALESCE(resourcerate."MSRPsetupFee", resourcerate."setupFee") END ELSE parentrate."setupFee" END))) AND (NOT (min(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."recurringFee" ELSE COALESCE(resourcerate."MSRPrecurringFee", resourcerate."recurringFee") END ELSE parentrate."recurringFee" END) IS DISTINCT FROM max(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."recurringFee" ELSE COALESCE(resourcerate."MSRPrecurringFee", resourcerate."recurringFee") END ELSE parentrate."recurringFee" END))) AND (NOT (min(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."costForAdditional" ELSE COALESCE(resourcerate."MSRPcostForAdditional", resourcerate."costForAdditional") END ELSE parentrate."costForAdditional" END) IS DISTINCT FROM max(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."costForAdditional" ELSE COALESCE(resourcerate."MSRPcostForAdditional", resourcerate."costForAdditional") END ELSE parentrate."costForAdditional" END))) AND (((CASE WHEN (max(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."setupFee" ELSE COALESCE(resourcerate."MSRPsetupFee", resourcerate."setupFee") END ELSE parentrate."setupFee" END) > '0'::numeric) THEN 1 ELSE 0 END + CASE WHEN (max(CASE WHEN "*SELECT* 1"."isDelegated" THEN CASE WHEN ("*SELECT* 1"."PlanAccountID" = owner."VendorAccountID") THEN resourcerate."recurringFee" ELSE COALESCE(resourcerate."MSRPrecurringFee", resourcerate."recurringFee") END ELSE parentrate."recurringFee" END) > '0'::numeric) THEN 1 ELSE 0 END)
  • Rows Removed by Filter: 8
3.          

CTE RealDelegatedPlan

4. 66.320 1,001.635 ↑ 93.4 241,229 1

Recursive Union (cost=0.00..2,052,110.83 rows=22,524,058 width=12) (actual time=0.010..1,001.635 rows=241,229 loops=1)

5. 240.005 240.005 ↑ 1.0 231,259 1

Seq Scan on "Plan" p1 (cost=0.00..50,573.87 rows=231,348 width=12) (actual time=0.009..240.005 rows=231,259 loops=1)

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
6. 108.390 695.310 ↑ 447.2 4,985 2

Hash Join (cost=54,369.72..155,105.58 rows=2,229,271 width=12) (actual time=286.663..347.655 rows=4,985 loops=2)

  • Hash Cond: (pp."ParentPlanID" = p1_1."PlanID")
7. 40.668 40.668 ↑ 19.2 120,614 2

WorkTable Scan on "RealDelegatedPlan" pp (cost=0.00..46,269.60 rows=2,313,480 width=12) (actual time=0.008..20.334 rows=120,614 loops=2)

8. 100.102 546.252 ↑ 1.0 231,259 2

Hash (cost=50,573.87..50,573.87 rows=231,348 width=8) (actual time=273.126..273.126 rows=231,259 loops=2)

  • Buckets: 65536 Batches: 8 Memory Usage: 1645kB
9. 446.150 446.150 ↑ 1.0 231,259 2

Seq Scan on "Plan" p1_1 (cost=0.00..50,573.87 rows=231,348 width=8) (actual time=0.009..223.075 rows=231,259 loops=2)

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
10. 4.454 8,049.694 ↑ 314.3 1,995 1

Sort (cost=437,066,928.93..437,068,496.61 rows=627,073 width=1,033) (actual time=8,048.747..8,049.694 rows=1,995 loops=1)

  • Sort Key: resource."resourceID", (COALESCE(planresource."MPNumber", resource."MPNumber")), "*SELECT* 1"."BillingPeriod", "*SELECT* 1"."BillingPeriodType
  • Sort Method: quicksort Memory: 1810kB
11. 104.125 8,045.240 ↑ 314.3 1,995 1

Hash Right Join (cost=416,557,688.12..436,151,353.71 rows=627,073 width=1,033) (actual time=7,990.184..8,045.240 rows=1,995 loops=1)

  • Hash Cond: (("SubscriptionCounters"."PlanID" = "*SELECT* 1"."PlanID") AND ("SubscriptionCounters"."resourceID" = resource."resourceID"))
  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
  • Rows Removed by Join Filter: 447
12. 22.903 4,902.365 ↑ 1.6 147,830 1

Subquery Scan on SubscriptionCounters (cost=415,305,082.60..434,774,036.77 rows=238,600 width=48) (actual time=4,071.506..4,902.365 rows=147,830 loops=1)

13. 537.665 4,879.462 ↑ 1.6 147,830 1

GroupAggregate (cost=415,305,082.60..434,771,650.77 rows=238,600 width=48) (actual time=4,071.504..4,879.462 rows=147,830 loops=1)

  • Group Key: "*SELECT* 1_2"."PlanID", sp."resourceID
14. 1,088.762 4,341.797 ↑ 1,282.0 1,012,143 1

Sort (cost=415,305,082.60..418,549,013.55 rows=1,297,572,378 width=14) (actual time=4,071.481..4,341.797 rows=1,012,143 loops=1)

  • Sort Key: "*SELECT* 1_2"."PlanID", sp."resourceID
  • Sort Method: external merge Disk: 25920kB
15. 285.230 3,253.035 ↑ 1,282.0 1,012,143 1

Merge Join (cost=65,724,401.58..85,845,087.50 rows=1,297,572,378 width=14) (actual time=2,543.328..3,253.035 rows=1,012,143 loops=1)

  • Merge Cond: (sp."subscriptionID" = "*SELECT* 1_2"."subscriptionID")
16. 512.993 1,566.188 ↑ 1.3 505,517 1

Sort (cost=198,646.67..200,250.23 rows=641,426 width=14) (actual time=1,413.459..1,566.188 rows=505,517 loops=1)

  • Sort Key: sp."subscriptionID
  • Sort Method: external merge Disk: 13032kB
17. 1,053.195 1,053.195 ↑ 1.3 505,517 1

Seq Scan on "SubscrParam" sp (cost=0.00..114,854.17 rows=641,426 width=14) (actual time=0.060..1,053.195 rows=505,517 loops=1)

  • Filter: (("Amount" + "IncludedValue") > '0'::numeric)
  • Rows Removed by Filter: 1418761
18. 116.873 1,401.617 ↑ 242.2 1,079,967 1

Materialize (cost=65,525,754.91..66,833,541.16 rows=261,557,250 width=8) (actual time=1,129.859..1,401.617 rows=1,079,967 loops=1)

19. 502.902 1,284.744 ↑ 458.7 570,248 1

Sort (cost=65,525,754.91..66,179,648.03 rows=261,557,250 width=8) (actual time=1,129.855..1,284.744 rows=570,248 loops=1)

  • Sort Key: "*SELECT* 1_2"."subscriptionID
  • Sort Method: external merge Disk: 10024kB
20. 51.205 781.842 ↑ 458.7 570,249 1

Append (cost=4,433,018.05..11,076,807.02 rows=261,557,250 width=8) (actual time=165.311..781.842 rows=570,249 loops=1)

21. 32.360 632.612 ↑ 969.5 269,487 1

Subquery Scan on *SELECT* 1_2 (cost=4,433,018.05..11,064,890.40 rows=261,256,488 width=8) (actual time=165.309..632.612 rows=269,487 loops=1)

22. 156.611 600.252 ↑ 969.5 269,487 1

Merge Join (cost=4,433,018.05..8,452,325.52 rows=261,256,488 width=8) (actual time=165.308..600.252 rows=269,487 loops=1)

  • Merge Cond: (subscr_1."PlanID" = rdp."PlanID")
23. 185.709 185.709 ↑ 1.0 300,762 1

Index Scan using "SubscriptionPlanID" on "Subscription" subscr_1 (cost=0.42..43,398.52 rows=300,762 width=8) (actual time=0.020..185.709 rows=300,762 loops=1)

24. 46.783 257.932 ↑ 51.4 438,558 1

Materialize (cost=4,433,017.63..4,545,637.92 rows=22,524,058 width=8) (actual time=150.335..257.932 rows=438,558 loops=1)

25. 171.629 211.149 ↑ 93.4 241,209 1

Sort (cost=4,433,017.63..4,489,327.77 rows=22,524,058 width=8) (actual time=150.334..211.149 rows=241,209 loops=1)

  • Sort Key: rdp."PlanID
  • Sort Method: external merge Disk: 4240kB
26. 39.520 39.520 ↑ 93.4 241,229 1

CTE Scan on "RealDelegatedPlan" rdp (cost=0.00..450,481.16 rows=22,524,058 width=8) (actual time=0.025..39.520 rows=241,229 loops=1)

27. 98.025 98.025 ↑ 1.0 300,762 1

Seq Scan on "Subscription" subscr (cost=0.00..11,916.62 rows=300,762 width=8) (actual time=0.008..98.025 rows=300,762 loops=1)

28. 5.814 3,038.750 ↑ 314.3 1,995 1

Hash (cost=1,204,006.42..1,204,006.42 rows=627,073 width=481) (actual time=3,038.750..3,038.750 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 256 Memory Usage: 36kB
29. 57.941 3,032.936 ↑ 314.3 1,995 1

Hash Right Join (cost=1,022,213.99..1,204,006.42 rows=627,073 width=481) (actual time=2,973.688..3,032.936 rows=1,995 loops=1)

  • Hash Cond: ((planresource."PlanID" = "*SELECT* 1_1"."RootPlanID") AND (planresource."resourceID" = resource."resourceID"))
  • Filter: (COALESCE(planresource."MPNumber", resource."MPNumber") IS NOT NULL)
  • Rows Removed by Filter: 789
30. 3.811 3.811 ↑ 19.4 20,369 1

Seq Scan on "PlanBMResource" planresource (cost=0.00..4,191.26 rows=395,126 width=47) (actual time=0.013..3.811 rows=20,369 loops=1)

31. 6.209 2,971.184 ↑ 226.4 2,784 1

Hash (cost=976,448.63..976,448.63 rows=630,224 width=442) (actual time=2,971.184..2,971.184 rows=2,784 loops=1)

  • Buckets: 8192 Batches: 256 Memory Usage: 69kB
32. 40.957 2,964.975 ↑ 226.4 2,784 1

Hash Join (cost=365,486.56..976,448.63 rows=630,224 width=442) (actual time=1,696.428..2,964.975 rows=2,784 loops=1)

  • Hash Cond: ("*SELECT* 1_1"."PlanID" = "*SELECT* 1"."PlanID")
33. 23.536 1,229.235 ↑ 3.5 240,087 1

Append (cost=168.15..596,546.88 rows=828,599 width=8) (actual time=1.629..1,229.235 rows=240,087 loops=1)

34. 1.155 8.616 ↓ 1.0 8,828 1

Subquery Scan on *SELECT* 1_1 (cost=168.15..22,590.73 rows=8,739 width=8) (actual time=1.629..8.616 rows=8,828 loops=1)

35. 6.476 7.461 ↓ 1.0 8,828 1

Bitmap Heap Scan on "Plan" (cost=168.15..22,503.34 rows=8,739 width=8) (actual time=1.629..7.461 rows=8,828 loops=1)

  • Recheck Cond: ("Copied_From_PlanID" IS NULL)
  • Heap Blocks: exact=4272
36. 0.985 0.985 ↓ 1.0 8,828 1

Bitmap Index Scan on "Plan_EX_Copied_From_PlanID" (cost=0.00..165.96 rows=8,739 width=0) (actual time=0.985..0.985 rows=8,828 loops=1)

  • Index Cond: ("Copied_From_PlanID" IS NULL)
37. 30.275 1,197.083 ↑ 3.5 231,259 1

Subquery Scan on *SELECT* 2_1 (cost=22,612.58..573,956.15 rows=819,860 width=8) (actual time=7.501..1,197.083 rows=231,259 loops=1)

38. 77.321 1,166.808 ↑ 3.5 231,259 1

Hash Join (cost=22,612.58..565,757.55 rows=819,860 width=8) (actual time=7.500..1,166.808 rows=231,259 loops=1)

  • Hash Cond: (rdp_1."ParentPlanID" = p."PlanID")
39. 1,082.029 1,082.029 ↑ 93.4 241,229 1

CTE Scan on "RealDelegatedPlan" rdp_1 (cost=0.00..450,481.16 rows=22,524,058 width=8) (actual time=0.012..1,082.029 rows=241,229 loops=1)

40. 1.486 7.458 ↓ 1.0 8,828 1

Hash (cost=22,503.34..22,503.34 rows=8,739 width=4) (actual time=7.458..7.458 rows=8,828 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 439kB
41. 5.047 5.972 ↓ 1.0 8,828 1

Bitmap Heap Scan on "Plan" p (cost=168.15..22,503.34 rows=8,739 width=4) (actual time=1.563..5.972 rows=8,828 loops=1)

  • Recheck Cond: ("Copied_From_PlanID" IS NULL)
  • Heap Blocks: exact=4272
42. 0.925 0.925 ↓ 1.0 8,828 1

Bitmap Index Scan on "Plan_EX_Copied_From_PlanID" (cost=0.00..165.96 rows=8,739 width=0) (actual time=0.925..0.925 rows=8,828 loops=1)

  • Index Cond: ("Copied_From_PlanID" IS NULL)
43. 3.965 1,694.783 ↓ 18.3 2,784 1

Hash (cost=365,316.51..365,316.51 rows=152 width=442) (actual time=1,694.783..1,694.783 rows=2,784 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1381kB
44. 2.476 1,690.818 ↓ 18.3 2,784 1

Nested Loop Left Join (cost=2.40..365,316.51 rows=152 width=442) (actual time=292.782..1,690.818 rows=2,784 loops=1)

  • Join Filter: (owner."IsProvider" = 0)
45. 4.139 1,688.342 ↓ 18.3 2,784 1

Nested Loop (cost=1.97..364,144.93 rows=152 width=431) (actual time=292.778..1,688.342 rows=2,784 loops=1)

46. 1.917 1,670.073 ↓ 9.3 2,826 1

Nested Loop (cost=1.69..357,154.80 rows=304 width=120) (actual time=292.761..1,670.073 rows=2,826 loops=1)

47. 1.218 1,656.586 ↓ 42.9 1,157 1

Nested Loop Left Join (cost=1.26..356,031.13 rows=27 width=78) (actual time=292.743..1,656.586 rows=1,157 loops=1)

  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
  • Rows Removed by Join Filter: 184
48. 0.374 1,654.211 ↓ 42.9 1,157 1

Nested Loop (cost=0.84..355,803.05 rows=27 width=78) (actual time=292.740..1,654.211 rows=1,157 loops=1)

49. 0.009 0.009 ↑ 1.0 1 1

Index Scan using "Account_PK" on "Account" owner (cost=0.42..8.44 rows=1 width=20) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ("AccountID" = 1000015574)
50. 0.322 1,653.828 ↓ 42.9 1,157 1

Append (cost=0.42..355,794.34 rows=27 width=62) (actual time=292.727..1,653.828 rows=1,157 loops=1)

51. 0.000 0.004 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.42..76.60 rows=10 width=62) (actual time=0.004..0.004 rows=0 loops=1)

52. 0.004 0.004 ↓ 0.0 0 1

Index Scan using "PlanAccountID" on "Plan" "Plan_1" (cost=0.42..76.50 rows=10 width=62) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: ("AccountID" = 1000015574)
  • Filter: (published = 0)
53. 0.358 1,653.502 ↓ 68.1 1,157 1

Subquery Scan on *SELECT* 2 (cost=54,385.50..355,717.74 rows=17 width=62) (actual time=292.723..1,653.502 rows=1,157 loops=1)

54. 26.001 1,653.144 ↓ 68.1 1,157 1

Hash Join (cost=54,385.50..355,717.57 rows=17 width=62) (actual time=292.723..1,653.144 rows=1,157 loops=1)

  • Hash Cond: (COALESCE((SubPlan 9), delegated."PlanID") = p_1."PlanID")
  • Join Filter: (p_1."AccountID" <> res_subscr."AccountID")
55. 1.314 3.804 ↓ 73.7 2,137 1

Nested Loop (cost=0.98..100.33 rows=29 width=8) (actual time=0.022..3.804 rows=2,137 loops=1)

  • Join Filter: (res_subscr."serviceTemplateID" = delegated."serviceTemplateID")
56. 0.001 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.70..41.37 rows=1 width=12) (actual time=0.012..0.015 rows=1 loops=1)

57. 0.007 0.007 ↑ 3.0 1 1

Index Only Scan using "Subscription_EX_AccountID_serviceTemplateID" on "Subscription" res_subscr (cost=0.42..16.46 rows=3 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: ("AccountID" = 1000015574)
  • Heap Fetches: 1
58. 0.007 0.007 ↑ 1.0 1 1

Index Scan using "ServiceTemplate_PK" on "ServiceTemplate" template (cost=0.28..8.29 rows=1 width=4) (actual time=0.005..0.007 rows=1 loops=1)

  • Index Cond: ("serviceTemplateID" = res_subscr."serviceTemplateID")
  • Filter: ("GateID" = 9)
59. 2.475 2.475 ↓ 1.8 2,137 1

Index Only Scan using "PlanDelegated_PK" on "PlanDelegated" delegated (cost=0.29..43.94 rows=1,201 width=8) (actual time=0.008..2.475 rows=2,137 loops=1)

  • Index Cond: ("serviceTemplateID" = template."serviceTemplateID")
  • Heap Fetches: 2137
60. 48.203 275.684 ↑ 1.0 137,743 1

Hash (cost=51,174.09..51,174.09 rows=138,114 width=57) (actual time=275.684..275.684 rows=137,743 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 1826kB
61. 227.481 227.481 ↑ 1.0 137,743 1

Seq Scan on "Plan" p_1 (cost=0.00..51,174.09 rows=138,114 width=57) (actual time=0.009..227.481 rows=137,743 loops=1)

  • Filter: (published = 0)
  • Rows Removed by Filter: 102344
62.          

SubPlan (for Hash Join)

63. 6.590 1,347.655 ↑ 1.0 1 3,295

Aggregate (cost=20,672.16..20,672.17 rows=1 width=4) (actual time=0.409..0.409 rows=1 loops=3,295)

64.          

CTE AccountDelegatedPlan

65. 3.295 1,341.065 ↓ 0.0 0 3,295

Recursive Union (cost=10.18..20,554.91 rows=5,211 width=12) (actual time=0.407..0.407 rows=0 loops=3,295)

66. 13.180 1,334.475 ↓ 0.0 0 3,295

Nested Loop (cost=10.18..53.56 rows=1 width=12) (actual time=0.405..0.405 rows=0 loops=3,295)

67. 16.475 16.475 ↑ 1.5 2 3,295

Index Scan using "AccountInheritance_PK" on "AccountInheritance" ai (cost=0.42..12.22 rows=3 width=8) (actual time=0.004..0.005 rows=2 loops=3,295)

  • Index Cond: ("CustomerAccountID" = res_subscr."AccountID")
68. 13.180 1,304.820 ↓ 0.0 0 6,590

Bitmap Heap Scan on "Plan" p1_2 (cost=9.76..13.77 rows=1 width=8) (actual time=0.198..0.198 rows=0 loops=6,590)

  • Recheck Cond: (("AccountID" = ai."VendorAccountID") AND ("Copied_From_PlanID" = delegated."PlanID"))
69. 342.680 1,291.640 ↓ 0.0 0 6,590

BitmapAnd (cost=9.76..9.76 rows=1 width=0) (actual time=0.196..0.196 rows=0 loops=6,590)

70. 883.060 883.060 ↓ 37.4 1,309 6,590

Bitmap Index Scan on "Plan_EX_AccountID_serviceTemplateID" (cost=0.00..4.68 rows=35 width=0) (actual time=0.134..0.134 rows=1,309 loops=6,590)

  • Index Cond: ("AccountID" = ai."VendorAccountID")
71. 65.900 65.900 ↓ 1.5 79 6,590

Bitmap Index Scan on "Plan_EX_Copied_From_PlanID" (cost=0.00..4.81 rows=52 width=0) (actual time=0.010..0.010 rows=79 loops=6,590)

  • Index Cond: ("Copied_From_PlanID" = delegated."PlanID")
72. 0.000 3.295 ↓ 0.0 0 3,295

Hash Left Join (cost=12.67..2,039.71 rows=521 width=12) (actual time=0.001..0.001 rows=0 loops=3,295)

  • Hash Cond: (p1_3."AccountID" = ai_1."VendorAccountID")
  • Filter: ((ai_1."CustomerAccountID" IS NOT NULL) OR (p1_3."AccountID" = res_subscr."AccountID"))
73. 3.295 3.295 ↓ 0.0 0 3,295

Nested Loop (cost=0.42..2,024.13 rows=521 width=8) (actual time=0.001..0.001 rows=0 loops=3,295)

74. 0.000 0.000 ↓ 0.0 0 3,295

WorkTable Scan on "AccountDelegatedPlan" pp_1 (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=0 loops=3,295)

75. 0.000 0.000 ↓ 0.0 0

Index Scan using "Plan_EX_Copied_From_PlanID" on "Plan" p1_3 (cost=0.42..201.87 rows=52 width=12) (never executed)

  • Index Cond: ("Copied_From_PlanID" = pp_1."PlanID")
76. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.22..12.22 rows=3 width=12) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Index Scan using "AccountInheritance_PK" on "AccountInheritance" ai_1 (cost=0.42..12.22 rows=3 width=12) (never executed)

  • Index Cond: ("CustomerAccountID" = res_subscr."AccountID")
78. 1,341.065 1,341.065 ↓ 0.0 0 3,295

CTE Scan on "AccountDelegatedPlan" (cost=0.00..104.22 rows=5,211 width=8) (actual time=0.407..0.407 rows=0 loops=3,295)

79. 1.157 1.157 ↓ 0.0 0 1,157

Index Only Scan using "Plan_PK" on "Plan" parentplan (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1,157)

  • Index Cond: ("PlanID" = "*SELECT* 1"."Copied_From_PlanID")
  • Heap Fetches: 184
80. 11.570 11.570 ↑ 5.5 2 1,157

Index Scan using "PlanRatePlanID" on "PlanRate" resourcerate (cost=0.43..41.51 rows=11 width=42) (actual time=0.007..0.010 rows=2 loops=1,157)

  • Index Cond: ("PlanID" = "*SELECT* 1"."PlanID")
  • Filter: (("maxValue" <> "includedValue") OR ("IsMain" = 1) OR ("IsVisible" = 1))
  • Rows Removed by Filter: 1
81. 8.478 14.130 ↑ 1.0 1 2,826

Index Scan using "BMResource_PK" on "BMResource" resource (cost=0.29..22.98 rows=1 width=315) (actual time=0.005..0.005 rows=1 loops=2,826)

  • Index Cond: ("resourceID" = resourcerate."resourceID")
  • Filter: (NOT (SubPlan 7))
  • Rows Removed by Filter: 0
82.          

SubPlan (for Index Scan)

83. 0.000 5.652 ↓ 0.0 0 2,826

Limit (cost=8.30..16.32 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=2,826)

84. 5.652 5.652 ↑ 1.0 1 2,826

Index Only Scan using "IncludedResources_PK" on "IncludedResources" (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=2,826)

  • Index Cond: ("resourceID" = resource."resourceID")
  • Heap Fetches: 2007
85. 0.000 0.000 ↓ 0.0 0 2,784

Index Scan using "PlanResourceUnique" on "PlanRate" parentrate (cost=0.43..7.70 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=2,784)

  • Index Cond: (("PlanID" = parentplan."PlanID") AND ("resourceID" = resource."resourceID"))
86.          

SubPlan (for GroupAggregate)

87. 3.330 3.330 ↓ 0.0 0 1,110

Index Scan using "BMResourceActivationParams_PK" on "BMResourceActivationParams" ap (cost=0.29..8.31 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=1,110)

  • Index Cond: (("resourceID" = resource."resourceID") AND (("ParamName")::text = 'application_name'::text))
88. 0.943 6.601 ↑ 1.0 1 943

Nested Loop (cost=0.57..16.62 rows=1 width=26) (actual time=0.007..0.007 rows=1 loops=943)

89. 2.829 2.829 ↑ 1.0 1 943

Index Only Scan using "IncludedResources_PK" on "IncludedResources" ir (cost=0.28..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=943)

  • Index Cond: ("resourceID" = resource."resourceID")
  • Heap Fetches: 943
90. 2.829 2.829 ↑ 1.0 1 943

Index Scan using "BMResourceActivationParams_PK" on "BMResourceActivationParams" ap_1 (cost=0.29..8.31 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=943)

  • Index Cond: (("resourceID" = ir."Included_resourceID") AND (("ParamName")::text = 'application_name'::text))
91. 7.980 51.870 ↑ 1.0 1 1,995

Aggregate (cost=93.41..93.42 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=1,995)

92. 3.651 43.890 ↑ 4.0 1 1,995

Nested Loop Left Join (cost=5.17..93.40 rows=4 width=72) (actual time=0.017..0.022 rows=1 loops=1,995)

  • Join Filter: ((requiredplanresource."PlanID" = requiredrate."PlanID") AND (requiredplanresource."resourceID" = requiredresource."resourceID"))
  • Rows Removed by Join Filter: 5
  • Filter: (COALESCE(requiredplanresource."MPNumber", requiredresource."MPNumber") IS NOT NULL)
  • Rows Removed by Filter: 0
93. 1.752 27.930 ↑ 4.0 1 1,995

Nested Loop (cost=0.71..74.16 rows=4 width=41) (actual time=0.011..0.014 rows=1 loops=1,995)

94. 23.940 23.940 ↑ 4.0 1 1,995

Index Scan using "PlanRatePlanID" on "PlanRate" requiredrate (cost=0.43..40.91 rows=4 width=8) (actual time=0.009..0.012 rows=1 loops=1,995)

  • Index Cond: ("*SELECT* 1_1"."RootPlanID" = "PlanID")
  • Filter: (("includedValue" <> '-1'::numeric) AND ("minValue" > "includedValue") AND ("PlanRateID" <> resourcerate."PlanRateID"))
  • Rows Removed by Filter: 7
95. 2.238 2.238 ↑ 1.0 1 1,119

Index Scan using "BMResource_PK" on "BMResource" requiredresource (cost=0.29..8.30 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=1,119)

  • Index Cond: ("resourceID" = requiredrate."resourceID")
96. 5.595 12.309 ↓ 2.2 9 1,119

Materialize (cost=4.45..18.97 rows=4 width=47) (actual time=0.008..0.011 rows=9 loops=1,119)

97. 3.357 6.714 ↓ 2.2 9 1,119

Bitmap Heap Scan on "PlanBMResource" requiredplanresource (cost=4.45..18.95 rows=4 width=47) (actual time=0.005..0.006 rows=9 loops=1,119)

  • Recheck Cond: ("PlanID" = "*SELECT* 1_1"."RootPlanID")
  • Heap Blocks: exact=1236
98. 3.357 3.357 ↓ 2.2 9 1,119

Bitmap Index Scan on "PlanBMResource_PK" (cost=0.00..4.45 rows=4 width=0) (actual time=0.003..0.003 rows=9 loops=1,119)

  • Index Cond: ("PlanID" = "*SELECT* 1_1"."RootPlanID")
99. 1.995 13.965 ↑ 1.0 1 1,995

Aggregate (cost=21.69..21.70 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1,995)

100. 3.990 11.970 ↓ 0.0 0 1,995

Nested Loop (cost=9.64..21.68 rows=1 width=6) (actual time=0.006..0.006 rows=0 loops=1,995)

101. 0.000 7.980 ↓ 0.0 0 1,995

Bitmap Heap Scan on "Subscription" s_1 (cost=9.21..13.22 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1,995)

  • Recheck Cond: (("AccountID" = owner."AccountID") AND ("PlanID" = parentplan."PlanID"))
102. 1.995 7.980 ↓ 0.0 0 1,995

BitmapAnd (cost=9.21..9.21 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1,995)

103. 5.985 5.985 ↑ 3.0 1 1,995

Bitmap Index Scan on "Subscription_EX_AccountID_serviceTemplateID" (cost=0.00..4.45 rows=3 width=0) (actual time=0.003..0.003 rows=1 loops=1,995)

  • Index Cond: ("AccountID" = owner."AccountID")
104. 0.000 0.000 ↓ 0.0 0 1,995

Bitmap Index Scan on "SubscriptionPlanID" (cost=0.00..4.51 rows=12 width=0) (actual time=0.000..0.000 rows=0 loops=1,995)

  • Index Cond: ("PlanID" = parentplan."PlanID")
105. 0.000 0.000 ↓ 0.0 0

Index Scan using "SubscrParam_PK" on "SubscrParam" sp_2 (cost=0.43..8.45 rows=1 width=10) (never executed)

  • Index Cond: (("subscriptionID" = s_1."subscriptionID") AND ("resourceID" = resource."resourceID"))
106. 1.995 9.975 ↑ 1.0 1 1,995

Aggregate (cost=21.69..21.70 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1,995)

107. 1.995 7.980 ↓ 0.0 0 1,995

Nested Loop (cost=9.64..21.69 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1,995)

108. 0.000 5.985 ↓ 0.0 0 1,995

Bitmap Heap Scan on "Subscription" s (cost=9.21..13.22 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,995)

  • Recheck Cond: (("AccountID" = owner."AccountID") AND ("PlanID" = parentplan."PlanID"))
109. 1.995 5.985 ↓ 0.0 0 1,995

BitmapAnd (cost=9.21..9.21 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1,995)

110. 3.990 3.990 ↑ 3.0 1 1,995

Bitmap Index Scan on "Subscription_EX_AccountID_serviceTemplateID" (cost=0.00..4.45 rows=3 width=0) (actual time=0.002..0.002 rows=1 loops=1,995)

  • Index Cond: ("AccountID" = owner."AccountID")
111. 0.000 0.000 ↓ 0.0 0 1,995

Bitmap Index Scan on "SubscriptionPlanID" (cost=0.00..4.51 rows=12 width=0) (actual time=0.000..0.000 rows=0 loops=1,995)

  • Index Cond: ("PlanID" = parentplan."PlanID")
112. 0.000 0.000 ↓ 0.0 0

Index Scan using "SubscrParam_PK" on "SubscrParam" sp_1 (cost=0.43..8.45 rows=1 width=4) (never executed)

  • Index Cond: (("subscriptionID" = s."subscriptionID") AND ("resourceID" = resource."resourceID"))
  • Filter: (("Amount" + "IncludedValue") > '0'::numeric)