explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fyXS : Optimization for: plan #Nvfl

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.468 20,722.666 ↑ 29,062.5 1,110 1

Subquery Scan on FlatProductItem3 (cost=532,383,066.13..5,761,655,795.36 rows=32,259,408 width=1,125) (actual time=20,578.706..20,722.666 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. 48.892 20,722.198 ↑ 29,062.5 1,110 1

GroupAggregate (cost=532,383,066.13..5,761,333,201.28 rows=32,259,408 width=1,370) (actual time=20,578.704..20,722.198 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. 63.339 1,002.536 ↑ 93.4 241,229 1

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

5. 232.121 232.121 ↑ 1.0 231,259 1

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

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

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

  • Hash Cond: (pp."ParentPlanID" = p1_1."PlanID")
7. 41.668 41.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.010..20.834 rows=120,614 loops=2)

8. 103.470 556.250 ↑ 1.0 231,259 2

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

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

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
10. 6.842 20,579.585 ↑ 16,170.1 1,995 1

Sort (cost=530,330,955.30..530,411,603.82 rows=32,259,408 width=1,033) (actual time=20,578.478..20,579.585 rows=1,995 loops=1)

  • Sort Key: resource."resourceID", (COALESCE(planresource."MPNumber", resource."MPNumber")), "*SELECT* 1"."BillingPeriod", "*SELECT* 1"."BillingPeriodType
  • Sort Method: quicksort Memory: 1828kB
11. 1,596.483 20,572.743 ↑ 16,170.1 1,995 1

Hash Right Join (cost=427,363,227.98..452,983,685.79 rows=32,259,408 width=1,033) (actual time=19,161.274..20,572.743 rows=1,995 loops=1)

  • Hash Cond: (("SubscriptionCounters"."PlanID" = "*SELECT* 1"."PlanID") AND ("SubscriptionCounters"."resourceID" = resource."resourceID"))
  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
12. 24.583 5,013.014 ↑ 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,144.302..5,013.014 rows=147,830 loops=1)

13. 571.190 4,988.431 ↑ 1.6 147,830 1

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

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

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

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

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

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

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

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

19. 515.970 1,312.783 ↑ 458.7 570,248 1

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

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

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

21. 33.555 644.067 ↑ 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=170.455..644.067 rows=269,487 loops=1)

22. 156.369 610.512 ↑ 969.5 269,487 1

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

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

24. 46.772 263.675 ↑ 51.4 438,558 1

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

25. 176.650 216.903 ↑ 93.4 241,209 1

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

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

27. 100.033 100.033 ↑ 1.0 300,762 1

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

28. 31.426 13,963.246 ↑ 16,170.1 1,995 1

Hash (cost=9,558,041.26..9,558,041.26 rows=32,259,408 width=481) (actual time=13,963.246..13,963.246 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
29. 1,997.601 13,931.820 ↑ 16,170.1 1,995 1

Hash Right Join (cost=6,920,415.81..9,558,041.26 rows=32,259,408 width=481) (actual time=12,559.268..13,931.820 rows=1,995 loops=1)

  • Hash Cond: ((parentrate."PlanID" = parentplan."PlanID") AND (parentrate."resourceID" = resource."resourceID"))
  • Join Filter: (owner."IsProvider" = 0)
30. 1,099.221 1,099.221 ↑ 1.0 1,275,291 1

Seq Scan on "PlanRate" parentrate (cost=0.00..235,645.91 rows=1,275,291 width=19) (actual time=0.092..1,099.221 rows=1,275,291 loops=1)

31. 16.899 10,834.998 ↑ 16,170.1 1,995 1

Hash (cost=4,483,317.69..4,483,317.69 rows=32,259,408 width=470) (actual time=10,834.998..10,834.998 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
32. 93.238 10,818.099 ↑ 16,170.1 1,995 1

Hash Join (cost=2,125,630.57..4,483,317.69 rows=32,259,408 width=470) (actual time=7,777.204..10,818.099 rows=1,995 loops=1)

  • Hash Cond: ("*SELECT* 1_1"."PlanID" = "*SELECT* 1"."PlanID")
33. 2,969.398 9,624.823 ↑ 6.0 780,366 1

Hash Right Join (cost=1,533,551.44..2,749,966.24 rows=4,644,983 width=400) (actual time=6,670.234..9,624.823 rows=780,366 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: 217311
34. 4.144 4.144 ↑ 19.4 20,369 1

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

35. 993.885 6,651.281 ↑ 4.7 997,677 1

Hash (cost=1,240,139.56..1,240,139.56 rows=4,668,325 width=361) (actual time=6,651.281..6,651.281 rows=997,677 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 16384 (originally 1024) Memory Usage: 2779kB
36. 1,138.033 5,657.396 ↑ 4.7 997,677 1

Hash Join (cost=448,862.73..1,240,139.56 rows=4,668,325 width=361) (actual time=4,360.511..5,657.396 rows=997,677 loops=1)

  • Hash Cond: ("*SELECT* 1_1"."PlanID" = resourcerate."PlanID")
37. 22.657 164.764 ↑ 3.5 240,087 1

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

38. 1.064 7.501 ↓ 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.469..7.501 rows=8,828 loops=1)

39. 5.574 6.437 ↓ 1.0 8,828 1

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

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

  • Index Cond: ("Copied_From_PlanID" IS NULL)
41. 28.712 134.606 ↑ 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=6.478..134.606 rows=231,259 loops=1)

42. 63.902 105.894 ↑ 3.5 231,259 1

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

  • Hash Cond: (rdp_1."ParentPlanID" = p."PlanID")
43. 35.567 35.567 ↑ 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.027..35.567 rows=241,229 loops=1)

44. 1.279 6.425 ↓ 1.0 8,828 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 439kB
45. 4.239 5.146 ↓ 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.498..5.146 rows=8,828 loops=1)

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

  • Index Cond: ("Copied_From_PlanID" IS NULL)
47. 963.894 4,354.599 ↓ 1.6 997,677 1

Hash (cost=410,846.85..410,846.85 rows=637,419 width=353) (actual time=4,354.599..4,354.599 rows=997,677 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 256 (originally 128) Memory Usage: 1985kB
48. 1,825.551 3,390.705 ↓ 1.6 997,677 1

Hash Join (cost=154,481.93..410,846.85 rows=637,419 width=353) (actual time=28.531..3,390.705 rows=997,677 loops=1)

  • Hash Cond: (resourcerate."resourceID" = resource."resourceID")
49. 1,536.661 1,536.661 ↑ 1.2 1,058,984 1

Seq Scan on "PlanRate" resourcerate (cost=0.00..245,210.59 rows=1,274,703 width=42) (actual time=0.023..1,536.661 rows=1,058,984 loops=1)

  • Filter: (("maxValue" <> "includedValue") OR ("IsMain" = 1) OR ("IsVisible" = 1))
  • Rows Removed by Filter: 216307
50. 5.535 28.493 ↓ 1.9 9,024 1

Hash (cost=154,423.10..154,423.10 rows=4,706 width=315) (actual time=28.493..28.493 rows=9,024 loops=1)

  • Buckets: 8192 (originally 8192) Batches: 2 (originally 1) Memory Usage: 1985kB
51. 4.136 22.958 ↓ 1.9 9,024 1

Seq Scan on "BMResource" resource (cost=0.00..154,423.10 rows=4,706 width=315) (actual time=0.016..22.958 rows=9,024 loops=1)

  • Filter: (NOT (SubPlan 7))
  • Rows Removed by Filter: 387
52.          

SubPlan (for Seq Scan)

53. 9.411 18.822 ↓ 0.0 0 9,411

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

54. 9.411 9.411 ↑ 1.0 1 9,411

Index Only Scan using "IncludedResources_PK" on "IncludedResources" (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=9,411)

  • Index Cond: ("resourceID" = resource."resourceID")
  • Heap Fetches: 4764
55. 0.432 1,100.038 ↑ 1.2 1,190 1

Hash (cost=592,061.77..592,061.77 rows=1,389 width=78) (actual time=1,100.038..1,100.038 rows=1,190 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 155kB
56. 0.000 1,099.606 ↑ 1.2 1,190 1

Nested Loop Left Join (cost=47.61..592,061.77 rows=1,389 width=78) (actual time=0.406..1,099.606 rows=1,190 loops=1)

  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
57. 0.189 1,098.495 ↑ 1.2 1,190 1

Nested Loop (cost=47.19..581,432.19 rows=1,389 width=78) (actual time=0.404..1,098.495 rows=1,190 loops=1)

58. 0.011 0.011 ↑ 1.0 1 1

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

  • Index Cond: ("AccountID" = 1000002)
59. 0.111 1,098.295 ↑ 1.2 1,190 1

Append (cost=46.77..581,409.86 rows=1,389 width=62) (actual time=0.389..1,098.295 rows=1,190 loops=1)

60. 0.171 2.915 ↑ 1.2 1,190 1

Subquery Scan on *SELECT* 1 (cost=46.77..7,918.43 rows=1,381 width=62) (actual time=0.389..2.915 rows=1,190 loops=1)

61. 2.504 2.744 ↑ 1.2 1,190 1

Bitmap Heap Scan on "Plan" "Plan_1" (cost=46.77..7,904.62 rows=1,381 width=62) (actual time=0.388..2.744 rows=1,190 loops=1)

  • Recheck Cond: ("AccountID" = 1000002)
  • Filter: (published = 0)
  • Rows Removed by Filter: 1137
  • Heap Blocks: exact=1263
62. 0.240 0.240 ↑ 1.0 2,327 1

Bitmap Index Scan on "Plan_EX_AccountID_serviceTemplateID" (cost=0.00..46.43 rows=2,401 width=0) (actual time=0.240..0.240 rows=2,327 loops=1)

  • Index Cond: ("AccountID" = 1000002)
63. 0.001 1,095.269 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=513,484.92..573,491.43 rows=8 width=62) (actual time=1,095.269..1,095.269 rows=0 loops=1)

64. 1.181 1,095.268 ↓ 0.0 0 1

Nested Loop (cost=513,484.92..573,491.35 rows=8 width=62) (actual time=1,095.268..1,095.268 rows=0 loops=1)

65. 0.824 1,093.987 ↓ 1.4 20 1

Hash Anti Join (cost=508,921.59..509,492.24 rows=14 width=8) (actual time=1,093.362..1,093.987 rows=20 loops=1)

  • Hash Cond: ((res_subscr."AccountID" = "RealDelegatedPlan"."AccountID") AND (delegated."PlanID" = "RealDelegatedPlan"."ParentPlanID"))
66. 0.067 0.256 ↓ 8.7 253 1

Nested Loop (cost=0.98..100.33 rows=29 width=8) (actual time=0.039..0.256 rows=253 loops=1)

  • Join Filter: (res_subscr."serviceTemplateID" = delegated."serviceTemplateID")
67. 0.003 0.033 ↑ 1.0 1 1

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

68. 0.010 0.010 ↓ 1.7 5 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.010 rows=5 loops=1)

  • Index Cond: ("AccountID" = 1000002)
  • Heap Fetches: 5
69. 0.020 0.020 ↓ 0.0 0 5

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

  • Index Cond: ("serviceTemplateID" = res_subscr."serviceTemplateID")
  • Filter: ("GateID" = 9)
  • Rows Removed by Filter: 1
70. 0.156 0.156 ↑ 4.7 253 1

Index Only Scan using "PlanDelegated_PK" on "PlanDelegated" delegated (cost=0.29..43.94 rows=1,201 width=8) (actual time=0.013..0.156 rows=253 loops=1)

  • Index Cond: ("serviceTemplateID" = template."serviceTemplateID")
  • Heap Fetches: 253
71. 0.234 1,092.907 ↑ 479.2 235 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 515kB
72. 1,092.673 1,092.673 ↑ 479.2 235 1

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

  • Filter: ("AccountID" = 1000002)
  • Rows Removed by Filter: 240994
73. 0.000 0.100 ↓ 0.0 0 20

Index Scan using "Plan_PK" on "Plan" p_1 (cost=4,563.33..4,571.35 rows=1 width=57) (actual time=0.005..0.005 rows=0 loops=20)

  • Index Cond: ("PlanID" = COALESCE((SubPlan 9), delegated."PlanID"))
  • Filter: (published = 0)
  • Rows Removed by Filter: 1
74.          

SubPlan (for Index Scan)

75. 0.040 1.160 ↑ 1.0 1 20

Aggregate (cost=4,562.90..4,562.91 rows=1 width=4) (actual time=0.057..0.058 rows=1 loops=20)

76.          

CTE AccountDelegatedPlan

77. 0.020 1.120 ↓ 0.0 0 20

Recursive Union (cost=10.18..4,561.53 rows=61 width=16) (actual time=0.056..0.056 rows=0 loops=20)

78. 0.060 1.040 ↓ 0.0 0 20

Nested Loop (cost=10.18..53.56 rows=1 width=16) (actual time=0.052..0.052 rows=0 loops=20)

79. 0.080 0.080 ↑ 3.0 1 20

Index Scan using "AccountInheritance_PK" on "AccountInheritance" ai (cost=0.42..12.22 rows=3 width=8) (actual time=0.004..0.004 rows=1 loops=20)

  • Index Cond: ("CustomerAccountID" = res_subscr."AccountID")
80. 0.020 0.900 ↓ 0.0 0 20

Bitmap Heap Scan on "Plan" p1_2 (cost=9.76..13.77 rows=1 width=12) (actual time=0.045..0.045 rows=0 loops=20)

  • Recheck Cond: (("AccountID" = ai."VendorAccountID") AND ("Copied_From_PlanID" = delegated."PlanID"))
81. 0.220 0.880 ↓ 0.0 0 20

BitmapAnd (cost=9.76..9.76 rows=1 width=0) (actual time=0.044..0.044 rows=0 loops=20)

82. 0.600 0.600 ↓ 8.3 291 20

Bitmap Index Scan on "Plan_EX_AccountID_serviceTemplateID" (cost=0.00..4.68 rows=35 width=0) (actual time=0.030..0.030 rows=291 loops=20)

  • Index Cond: ("AccountID" = ai."VendorAccountID")
83. 0.060 0.060 ↑ 52.0 1 20

Bitmap Index Scan on "Plan_EX_Copied_From_PlanID" (cost=0.00..4.81 rows=52 width=0) (actual time=0.003..0.003 rows=1 loops=20)

  • Index Cond: ("Copied_From_PlanID" = delegated."PlanID")
84. 0.060 0.060 ↓ 0.0 0 20

Hash Join (cost=5.44..450.68 rows=6 width=16) (actual time=0.003..0.003 rows=0 loops=20)

  • Hash Cond: (p1_3."Copied_From_PlanID" = pp_1."PlanID")
85. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.11..440.16 rows=2,701 width=16) (never executed)

86. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ("CustomerAccountID" = res_subscr."AccountID")
87. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "Plan" p1_3 (cost=4.69..142.30 rows=35 width=12) (never executed)

  • Recheck Cond: ("AccountID" = ai_1."VendorAccountID")
88. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "Plan_EX_AccountID_serviceTemplateID" (cost=0.00..4.68 rows=35 width=0) (never executed)

  • Index Cond: ("AccountID" = ai_1."VendorAccountID")
89. 0.000 0.000 ↓ 0.0 0 20

Hash (cost=0.20..0.20 rows=10 width=8) (actual time=0.000..0.000 rows=0 loops=20)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
90. 0.000 0.000 ↓ 0.0 0 20

WorkTable Scan on "AccountDelegatedPlan" pp_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.000..0.000 rows=0 loops=20)

91. 1.120 1.120 ↓ 0.0 0 20

CTE Scan on "AccountDelegatedPlan" (cost=0.00..1.22 rows=61 width=8) (actual time=0.056..0.056 rows=0 loops=20)

92. 1.190 1.190 ↓ 0.0 0 1,190

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

  • Index Cond: ("PlanID" = "*SELECT* 1"."Copied_From_PlanID")
  • Heap Fetches: 193
93.          

SubPlan (for GroupAggregate)

94. 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))
95. 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)

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

99. 2.775 41.895 ↑ 4.0 1 1,995

Nested Loop Left Join (cost=5.17..93.40 rows=4 width=72) (actual time=0.016..0.021 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
100. 3.747 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)

101. 21.945 21.945 ↑ 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.011 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
102. 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")
103. 4.476 11.190 ↓ 2.2 9 1,119

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

104. 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
105. 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")
106. 1.995 17.955 ↑ 1.0 1 1,995

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

107. 3.990 15.960 ↓ 0.0 0 1,995

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

108. 0.000 11.970 ↓ 0.0 0 1,995

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

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

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

110. 7.980 7.980 ↓ 1.7 5 1,995

Bitmap Index Scan on "Subscription_EX_AccountID_serviceTemplateID" (cost=0.00..4.45 rows=3 width=0) (actual time=0.004..0.004 rows=5 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_2 (cost=0.43..8.45 rows=1 width=10) (never executed)

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

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

114. 1.995 11.970 ↓ 0.0 0 1,995

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

115. 1.995 9.975 ↓ 0.0 0 1,995

Bitmap Heap Scan on "Subscription" s (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"))
116. 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)

117. 5.985 5.985 ↓ 1.7 5 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=5 loops=1,995)

  • Index Cond: ("AccountID" = owner."AccountID")
118. 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")
119. 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)