explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bF0k

Settings
# exclusive inclusive rows x rows loops node
1. 0.445 23,582.659 ↑ 28,916.1 1,110 1

Subquery Scan on FlatProductItem2 (cost=531,865,136.63..5,734,784,344.00 rows=32,096,833 width=1,125) (actual time=23,430.757..23,582.659 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. 51.919 23,582.214 ↑ 28,916.1 1,110 1

GroupAggregate (cost=531,865,136.63..5,734,463,375.67 rows=32,096,833 width=1,370) (actual time=23,430.756..23,582.214 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. 61.035 994.671 ↑ 93.4 241,229 1

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

5. 230.144 230.144 ↑ 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..230.144 rows=231,259 loops=1)

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

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

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

8. 103.908 557.332 ↑ 1.0 231,259 2

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

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

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
10. 6.766 23,431.641 ↑ 16,088.6 1,995 1

Sort (cost=529,813,025.80..529,893,267.88 rows=32,096,833 width=1,033) (actual time=23,430.537..23,431.641 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,553.263 23,424.875 ↑ 16,088.6 1,995 1

Hash Right Join (cost=427,267,517.56..452,856,731.78 rows=32,096,833 width=1,033) (actual time=22,064.795..23,424.875 rows=1,995 loops=1)

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

13. 563.153 4,959.303 ↑ 1.6 147,830 1

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

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

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

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

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

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

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

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

19. 517.396 1,317.443 ↑ 458.7 570,248 1

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

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

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

21. 33.511 650.055 ↑ 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=173.380..650.055 rows=269,487 loops=1)

22. 157.418 616.544 ↑ 969.5 269,487 1

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

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

24. 47.008 268.378 ↑ 51.4 438,558 1

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

25. 180.013 221.370 ↑ 93.4 241,209 1

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

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

27. 97.548 97.548 ↑ 1.0 300,762 1

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

28. 31.827 16,886.979 ↑ 16,088.6 1,995 1

Hash (cost=9,474,929.46..9,474,929.46 rows=32,096,833 width=481) (actual time=16,886.979..16,886.979 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
29. 2,004.825 16,855.152 ↑ 16,088.6 1,995 1

Hash Right Join (cost=6,847,149.16..9,474,929.46 rows=32,096,833 width=481) (actual time=15,498.687..16,855.152 rows=1,995 loops=1)

  • Hash Cond: ((parentrate."PlanID" = parentplan."PlanID") AND (parentrate."resourceID" = resource."resourceID"))
  • Join Filter: (owner."IsProvider" = 0)
30. 1,135.403 1,135.403 ↑ 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.099..1,135.403 rows=1,275,291 loops=1)

31. 16.159 13,714.924 ↑ 16,088.6 1,995 1

Hash (cost=4,422,333.66..4,422,333.66 rows=32,096,833 width=470) (actual time=13,714.924..13,714.924 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
32. 91.332 13,698.765 ↑ 16,088.6 1,995 1

Hash Join (cost=2,072,078.51..4,422,333.66 rows=32,096,833 width=470) (actual time=10,818.397..13,698.765 rows=1,995 loops=1)

  • Hash Cond: ("*SELECT* 1_1"."PlanID" = "*SELECT* 1"."PlanID")
33. 2,811.445 9,547.696 ↑ 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,751.507..9,547.696 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.404 4.404 ↑ 19.4 20,369 1

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

35. 995.870 6,731.847 ↑ 4.7 997,677 1

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

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

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

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

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

38. 1.193 8.131 ↓ 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.493..8.131 rows=8,828 loops=1)

39. 6.049 6.938 ↓ 1.0 8,828 1

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

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

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

42. 66.099 107.869 ↑ 3.5 231,259 1

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

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

44. 1.350 6.561 ↓ 1.0 8,828 1

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

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

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

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

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

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

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

  • Hash Cond: (resourcerate."resourceID" = resource."resourceID")
49. 1,547.813 1,547.813 ↑ 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.096..1,547.813 rows=1,058,984 loops=1)

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

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

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

Seq Scan on "BMResource" resource (cost=0.00..154,423.10 rows=4,706 width=315) (actual time=0.018..23.342 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.384 4,059.737 ↑ 1.2 1,190 1

Hash (cost=538,509.80..538,509.80 rows=1,382 width=78) (actual time=4,059.737..4,059.737 rows=1,190 loops=1)

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

Nested Loop Left Join (cost=47.61..538,509.80 rows=1,382 width=78) (actual time=0.459..4,059.353 rows=1,190 loops=1)

  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
57. 0.194 4,058.292 ↑ 1.2 1,190 1

Nested Loop (cost=47.19..527,931.35 rows=1,382 width=78) (actual time=0.456..4,058.292 rows=1,190 loops=1)

58. 0.014 0.014 ↑ 1.0 1 1

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

  • Index Cond: ("AccountID" = 1000002)
59. 0.097 4,058.084 ↑ 1.2 1,190 1

Append (cost=46.77..527,909.10 rows=1,382 width=62) (actual time=0.438..4,058.084 rows=1,190 loops=1)

60. 0.171 2.917 ↑ 1.2 1,190 1

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

61. 2.458 2.746 ↑ 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.436..2.746 rows=1,190 loops=1)

  • Recheck Cond: ("AccountID" = 1000002)
  • Filter: (published = 0)
  • Rows Removed by Filter: 1137
  • Heap Blocks: exact=1263
62. 0.288 0.288 ↑ 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.288..0.288 rows=2,327 loops=1)

  • Index Cond: ("AccountID" = 1000002)
63. 0.000 4,055.070 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=6,217.80..519,990.66 rows=1 width=62) (actual time=4,055.070..4,055.070 rows=0 loops=1)

64. 2,968.726 4,055.070 ↓ 0.0 0 1

Nested Loop (cost=6,217.80..519,990.65 rows=1 width=62) (actual time=4,055.070..4,055.070 rows=0 loops=1)

65. 0.253 1,086.104 ↓ 10.0 20 1

Hash Right Join (cost=100.76..507,740.51 rows=2 width=8) (actual time=1,086.040..1,086.104 rows=20 loops=1)

  • Hash Cond: (("RealDelegatedPlan"."AccountID" = res_subscr."AccountID") AND ("RealDelegatedPlan"."ParentPlanID" = delegated."PlanID"))
  • Filter: ("RealDelegatedPlan"."PlanID" IS NULL)
  • Rows Removed by Filter: 233
66. 1,085.597 1,085.597 ↑ 479.2 235 1

CTE Scan on "RealDelegatedPlan" (cost=0.00..506,791.30 rows=112,620 width=12) (actual time=0.020..1,085.597 rows=235 loops=1)

  • Filter: ("AccountID" = 1000002)
  • Rows Removed by Filter: 240994
67. 0.049 0.254 ↓ 8.7 253 1

Hash (cost=100.33..100.33 rows=29 width=8) (actual time=0.254..0.254 rows=253 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
68. 0.044 0.205 ↓ 8.7 253 1

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

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

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

70. 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.005..0.010 rows=5 loops=1)

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

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

  • Index Cond: ("serviceTemplateID" = res_subscr."serviceTemplateID")
  • Filter: ("GateID" = 9)
  • Rows Removed by Filter: 1
72. 0.135 0.135 ↑ 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.012..0.135 rows=253 loops=1)

  • Index Cond: ("serviceTemplateID" = template."serviceTemplateID")
  • Heap Fetches: 253
73. 0.000 0.240 ↓ 0.0 0 20

Index Scan using "Plan_PK" on "Plan" p_1 (cost=6,117.04..6,125.06 rows=1 width=57) (actual time=0.012..0.012 rows=0 loops=20)

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

SubPlan (for Index Scan)

75. 0.020 2,968.520 ↓ 0.0 0 20

Limit (cost=0.42..6,116.62 rows=1 width=8) (actual time=148.426..148.426 rows=0 loops=20)

76. 0.100 2,968.500 ↓ 0.0 0 20

Nested Loop (cost=0.42..538,226.10 rows=88 width=8) (actual time=148.425..148.425 rows=0 loops=20)

  • Join Filter: (rdp_2."AccountID" = ai."VendorAccountID")
  • Rows Removed by Join Filter: 2
77. 2,189.920 2,189.920 ↑ 3.0 1 20

Index Scan using "AccountInheritanceDepth" on "AccountInheritance" ai (cost=0.42..24,079.14 rows=3 width=8) (actual time=0.010..109.496 rows=1 loops=20)

  • Filter: ("CustomerAccountID" = res_subscr."AccountID")
  • Rows Removed by Filter: 472887
78. 778.480 778.480 ↑ 56,310.0 2 20

CTE Scan on "RealDelegatedPlan" rdp_2 (cost=0.00..506,791.30 rows=112,620 width=8) (actual time=25.023..38.924 rows=2 loops=20)

  • Filter: ("ParentPlanID" = delegated."PlanID")
  • Rows Removed by Filter: 241227
79. 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
80.          

SubPlan (for GroupAggregate)

81. 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))
82. 1.886 7.544 ↑ 1.0 1 943

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

83. 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
84. 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))
85. 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)

86. 1.656 45.885 ↑ 4.0 1 1,995

Nested Loop Left Join (cost=5.17..93.40 rows=4 width=72) (actual time=0.017..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
87. 3.747 31.920 ↑ 4.0 1 1,995

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

88. 25.935 25.935 ↑ 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.013 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
89. 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")
90. 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)

91. 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
92. 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")
93. 0.000 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)

94. 3.990 17.955 ↓ 0.0 0 1,995

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

95. 1.995 13.965 ↓ 0.0 0 1,995

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

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

97. 9.975 9.975 ↓ 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.005..0.005 rows=5 loops=1,995)

  • Index Cond: ("AccountID" = owner."AccountID")
98. 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")
99. 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"))
100. 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)

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

102. 0.000 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"))
103. 3.990 9.975 ↓ 0.0 0 1,995

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

104. 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")
105. 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")
106. 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)