explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nvfl

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.509 134,196.490 ↑ 376.6 1,110 1

Subquery Scan on FlatProductItem2 (cost=442,507,949.24..510,273,986.21 rows=418,049 width=1,125) (actual time=134,032.486..134,196.490 rows=1,110 loops=1)

2. 60.670 134,195.981 ↑ 376.6 1,110 1

GroupAggregate (cost=442,507,949.24..510,269,805.72 rows=418,049 width=1,370) (actual time=134,032.486..134,195.981 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) + 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))))
  • Rows Removed by Filter: 8
3.          

CTE RealDelegatedPlan

4. 66.423 1,006.598 ↑ 93.4 241,229 1

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

5. 240.039 240.039 ↑ 1.0 231,259 1

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

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

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

  • Hash Cond: (pp."ParentPlanID" = p1_1."PlanID")
7. 42.368 42.368 ↑ 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.009..21.184 rows=120,614 loops=2)

8. 98.998 547.118 ↑ 1.0 231,259 2

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

  • Buckets: 65536 Batches: 8 Memory Usage: 1645kB
9. 448.120 448.120 ↑ 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..224.060 rows=231,259 loops=2)

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
10. 4.461 134,033.321 ↑ 209.5 1,995 1

Sort (cost=440,455,838.41..440,456,883.53 rows=418,049 width=1,033) (actual time=134,032.193..134,033.321 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. 87.975 134,028.860 ↑ 209.5 1,995 1

Hash Right Join (cost=420,292,586.99..439,846,677.61 rows=418,049 width=1,033) (actual time=133,992.145..134,028.860 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. 23.344 5,138.927 ↑ 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,278.827..5,138.927 rows=147,830 loops=1)

13. 556.476 5,115.583 ↑ 1.6 147,830 1

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

  • Group Key: "*SELECT* 1_2"."PlanID", sp."resourceID
14. 1,159.092 4,559.107 ↑ 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,278.796..4,559.107 rows=1,012,143 loops=1)

  • Sort Key: "*SELECT* 1_2"."PlanID", sp."resourceID
  • Sort Method: external merge Disk: 25920kB
15. 290.609 3,400.015 ↑ 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,680.948..3,400.015 rows=1,012,143 loops=1)

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

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

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

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

  • Filter: (("Amount" + "IncludedValue") > '0'::numeric)
  • Rows Removed by Filter: 1418761
18. 118.045 1,534.939 ↑ 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,261.145..1,534.939 rows=1,079,967 loops=1)

19. 546.149 1,416.894 ↑ 458.7 570,248 1

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

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

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

21. 34.486 710.564 ↑ 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=202.795..710.564 rows=269,487 loops=1)

22. 166.596 676.078 ↑ 969.5 269,487 1

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

  • Merge Cond: (subscr_1."PlanID" = rdp."PlanID")
23. 244.006 244.006 ↑ 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.852..244.006 rows=300,762 loops=1)

24. 48.714 265.476 ↑ 51.4 438,558 1

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

25. 176.778 216.762 ↑ 93.4 241,209 1

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

  • Sort Key: rdp."PlanID
  • Sort Method: external merge Disk: 4240kB
26. 39.984 39.984 ↑ 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.007..39.984 rows=241,229 loops=1)

27. 106.609 106.609 ↑ 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..106.609 rows=300,762 loops=1)

28. 4.604 128,801.958 ↑ 209.5 1,995 1

Hash (cost=4,955,104.65..4,955,104.65 rows=418,049 width=481) (actual time=128,801.958..128,801.958 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 128 Memory Usage: 40kB
29. 36.158 128,797.354 ↑ 209.5 1,995 1

Hash Right Join (cost=4,829,868.01..4,955,104.65 rows=418,049 width=481) (actual time=128,722.172..128,797.354 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. 40.717 40.717 ↑ 19.4 20,369 1

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

31. 6.212 128,720.479 ↑ 150.9 2,784 1

Hash (cost=4,799,357.76..4,799,357.76 rows=420,150 width=442) (actual time=128,720.479..128,720.479 rows=2,784 loops=1)

  • Buckets: 8192 Batches: 128 Memory Usage: 73kB
32. 43.878 128,714.267 ↑ 150.9 2,784 1

Hash Join (cost=610,143.77..4,799,357.76 rows=420,150 width=442) (actual time=44,106.830..128,714.267 rows=2,784 loops=1)

  • Hash Cond: ("*SELECT* 1"."PlanID" = "*SELECT* 1_1"."PlanID")
33. 4.620 128,463.191 ↓ 27.3 2,784 1

Nested Loop Left Join (cost=2.40..4,171,176.63 rows=102 width=442) (actual time=43,668.272..128,463.191 rows=2,784 loops=1)

  • Join Filter: (owner."IsProvider" = 0)
34. 6.034 128,458.571 ↓ 27.3 2,784 1

Nested Loop (cost=1.97..4,170,390.44 rows=102 width=431) (actual time=43,668.269..128,458.571 rows=2,784 loops=1)

35. 2.792 128,412.973 ↓ 13.9 2,826 1

Nested Loop (cost=1.69..4,165,722.69 rows=203 width=120) (actual time=43,668.251..128,412.973 rows=2,826 loops=1)

36. 4.284 128,384.727 ↓ 64.3 1,157 1

Nested Loop Left Join (cost=1.26..4,164,973.58 rows=18 width=78) (actual time=43,668.227..128,384.727 rows=1,157 loops=1)

  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
  • Rows Removed by Join Filter: 184
37. 0.997 128,376.972 ↓ 64.3 1,157 1

Nested Loop (cost=0.84..4,164,821.52 rows=18 width=78) (actual time=43,668.220..128,376.972 rows=1,157 loops=1)

38. 75.164 75.164 ↑ 1.0 1 1

Index Scan using "Account_IsProvider" on "Account" owner (cost=0.42..16,280.16 rows=1 width=20) (actual time=0.083..75.164 rows=1 loops=1)

  • Filter: ("AccountID" = 1000015574)
  • Rows Removed by Filter: 168454
39. 0.571 128,300.811 ↓ 64.3 1,157 1

Append (cost=0.42..4,148,541.18 rows=18 width=62) (actual time=43,668.131..128,300.811 rows=1,157 loops=1)

40. 0.002 0.035 ↓ 0.0 0 1

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

41. 0.033 0.033 ↓ 0.0 0 1

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

  • Index Cond: ("AccountID" = 1000015574)
  • Filter: (published = 0)
42. 0.746 128,300.205 ↓ 144.6 1,157 1

Subquery Scan on *SELECT* 2 (cost=563,306.10..4,148,464.58 rows=8 width=62) (actual time=43,668.096..128,300.205 rows=1,157 loops=1)

43. 41.794 128,299.459 ↓ 144.6 1,157 1

Hash Join (cost=563,306.10..4,148,464.50 rows=8 width=62) (actual time=43,668.096..128,299.459 rows=1,157 loops=1)

  • Hash Cond: (COALESCE((SubPlan 8), delegated."PlanID") = p."PlanID")
44. 4.207 1,110.904 ↓ 152.6 2,137 1

Hash Anti Join (cost=508,921.59..509,492.24 rows=14 width=8) (actual time=1,100.244..1,110.904 rows=2,137 loops=1)

  • Hash Cond: ((res_subscr."AccountID" = "RealDelegatedPlan"."AccountID") AND (delegated."PlanID" = "RealDelegatedPlan"."ParentPlanID"))
45. 0.957 6.657 ↓ 73.7 2,137 1

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

  • Join Filter: (res_subscr."serviceTemplateID" = delegated."serviceTemplateID")
46. 0.004 0.114 ↑ 1.0 1 1

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

47. 0.092 0.092 ↑ 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.091..0.092 rows=1 loops=1)

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

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

  • Index Cond: ("serviceTemplateID" = res_subscr."serviceTemplateID")
  • Filter: ("GateID" = 9)
49. 5.586 5.586 ↓ 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.017..5.586 rows=2,137 loops=1)

  • Index Cond: ("serviceTemplateID" = template."serviceTemplateID")
  • Heap Fetches: 2137
50. 0.002 1,100.040 ↓ 0.0 0 1

Hash (cost=506,791.30..506,791.30 rows=112,620 width=8) (actual time=1,100.040..1,100.040 rows=0 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 512kB
51. 1,100.038 1,100.038 ↓ 0.0 0 1

CTE Scan on "RealDelegatedPlan" (cost=0.00..506,791.30 rows=112,620 width=8) (actual time=1,100.038..1,100.038 rows=0 loops=1)

  • Filter: ("AccountID" = 1000015574)
  • Rows Removed by Filter: 241229
52. 47.558 276.081 ↑ 1.0 137,743 1

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

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

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

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

SubPlan (for Hash Join)

55. 9.885 126,870.680 ↑ 1.0 1 3,295

Aggregate (cost=511,871.65..511,871.66 rows=1 width=4) (actual time=38.504..38.504 rows=1 loops=3,295)

56. 217.470 126,860.795 ↓ 0.0 0 3,295

Nested Loop (cost=0.42..511,871.43 rows=88 width=8) (actual time=38.501..38.501 rows=0 loops=3,295)

  • Join Filter: (rdp_2."AccountID" = ai."VendorAccountID")
  • Rows Removed by Join Filter: 159
57. 126,643.325 126,643.325 ↑ 1,425.6 79 3,295

CTE Scan on "RealDelegatedPlan" rdp_2 (cost=0.00..506,791.30 rows=112,620 width=8) (actual time=14.624..38.435 rows=79 loops=3,295)

  • Filter: ("ParentPlanID" = delegated."PlanID")
  • Rows Removed by Filter: 241150
58. 0.000 0.000 ↑ 1.5 2 261,402

Materialize (cost=0.42..12.23 rows=3 width=8) (actual time=0.000..0.000 rows=2 loops=261,402)

59. 46.130 46.130 ↑ 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.014..0.014 rows=2 loops=3,295)

  • Index Cond: ("CustomerAccountID" = res_subscr."AccountID")
60. 3.471 3.471 ↓ 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.003..0.003 rows=0 loops=1,157)

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

Index Scan using "PlanRatePlanID" on "PlanRate" resourcerate (cost=0.43..41.51 rows=11 width=42) (actual time=0.018..0.022 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
62. 28.260 39.564 ↑ 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.014..0.014 rows=1 loops=2,826)

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

SubPlan (for Index Scan)

64. 2.826 11.304 ↓ 0.0 0 2,826

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

65. 8.478 8.478 ↑ 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.003..0.003 rows=1 loops=2,826)

  • Index Cond: ("resourceID" = resource."resourceID")
  • Heap Fetches: 2007
66. 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"))
67. 46.090 207.198 ↑ 3.5 240,087 1

Hash (cost=596,546.88..596,546.88 rows=828,599 width=8) (actual time=207.198..207.198 rows=240,087 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 807kB
68. 21.509 161.108 ↑ 3.5 240,087 1

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

69. 1.086 7.205 ↓ 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.485..7.205 rows=8,828 loops=1)

70. 5.228 6.119 ↓ 1.0 8,828 1

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

  • Recheck Cond: ("Copied_From_PlanID" IS NULL)
  • Heap Blocks: exact=4272
71. 0.891 0.891 ↓ 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.891..0.891 rows=8,828 loops=1)

  • Index Cond: ("Copied_From_PlanID" IS NULL)
72. 27.998 132.394 ↑ 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=8.042..132.394 rows=231,259 loops=1)

73. 62.439 104.396 ↑ 3.5 231,259 1

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

  • Hash Cond: (rdp_1."ParentPlanID" = p_1."PlanID")
74. 33.975 33.975 ↑ 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.006..33.975 rows=241,229 loops=1)

75. 1.568 7.982 ↓ 1.0 8,828 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 439kB
76. 5.515 6.414 ↓ 1.0 8,828 1

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

  • Recheck Cond: ("Copied_From_PlanID" IS NULL)
  • Heap Blocks: exact=4272
77. 0.899 0.899 ↓ 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.899..0.899 rows=8,828 loops=1)

  • Index Cond: ("Copied_From_PlanID" IS NULL)
78.          

SubPlan (for GroupAggregate)

79. 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))
80. 0.943 18.860 ↑ 1.0 1 943

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

81. 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
82. 15.088 15.088 ↑ 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.016..0.016 rows=1 loops=943)

  • Index Cond: (("resourceID" = ir."Included_resourceID") AND (("ParamName")::text = 'application_name'::text))
83. 9.975 55.860 ↑ 1.0 1 1,995

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

84. 3.651 45.885 ↑ 4.0 1 1,995

Nested Loop Left Join (cost=5.17..93.40 rows=4 width=72) (actual time=0.018..0.023 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
85. 2.628 29.925 ↑ 4.0 1 1,995

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

86. 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.010..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
87. 3.357 3.357 ↑ 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.003 rows=1 loops=1,119)

  • Index Cond: ("resourceID" = requiredrate."resourceID")
88. 4.476 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)

89. 4.476 7.833 ↓ 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.007 rows=9 loops=1,119)

  • Recheck Cond: ("PlanID" = "*SELECT* 1_1"."RootPlanID")
  • Heap Blocks: exact=1236
90. 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")
91. 1.995 13.965 ↑ 1.0 1 1,995

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

92. 1.995 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)

93. 1.995 9.975 ↓ 0.0 0 1,995

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

  • Recheck Cond: (("AccountID" = owner."AccountID") AND ("PlanID" = parentplan."PlanID"))
94. 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)

95. 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")
96. 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")
97. 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"))
98. 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)

99. 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)

100. 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"))
101. 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)

102. 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")
103. 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")
104. 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)