explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8ya3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.538 22,833.929 ↑ 28,916.1 1,110 1

Subquery Scan on FlatProductItem2 (cost=532,420,628.67..5,735,339,836.04 rows=32,096,833 width=1,125) (actual time=22,673.090..22,833.929 rows=1,110 loops=1)

  • 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))))
2. 54.847 22,833.391 ↑ 28,916.1 1,110 1

GroupAggregate (cost=532,420,628.67..5,735,018,867.71 rows=32,096,833 width=1,370) (actual time=22,673.089..22,833.391 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
  • Rows Removed by Filter: 8
3.          

CTE RealDelegatedPlan

4. 66.532 1,929.151 ↑ 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,929.151 rows=241,229 loops=1)

5. 1,144.145 1,144.145 ↑ 1.0 231,259 1

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

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

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

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

8. 105.310 564.046 ↑ 1.0 231,259 2

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

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

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
10. 7.023 22,673.905 ↑ 16,088.6 1,995 1

Sort (cost=530,368,517.84..530,448,759.92 rows=32,096,833 width=1,033) (actual time=22,672.847..22,673.905 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,673.299 22,666.882 ↑ 16,088.6 1,995 1

Hash Right Join (cost=427,823,009.60..453,412,223.82 rows=32,096,833 width=1,033) (actual time=21,177.048..22,666.882 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.192 4,989.367 ↑ 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,145.655..4,989.367 rows=147,830 loops=1)

13. 550.334 4,965.175 ↑ 1.6 147,830 1

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

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

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

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

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

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

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

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

19. 517.864 1,323.466 ↑ 458.7 570,248 1

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

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

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

21. 32.791 655.445 ↑ 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=172.120..655.445 rows=269,487 loops=1)

22. 161.266 622.654 ↑ 969.5 269,487 1

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

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

24. 48.041 270.045 ↑ 51.4 438,558 1

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

25. 181.481 222.004 ↑ 93.4 241,209 1

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

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

27. 98.454 98.454 ↑ 1.0 300,762 1

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

28. 30.025 16,004.216 ↑ 16,088.6 1,995 1

Hash (cost=10,030,421.50..10,030,421.50 rows=32,096,833 width=481) (actual time=16,004.216..16,004.216 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
29. 2,052.307 15,974.191 ↑ 16,088.6 1,995 1

Hash Right Join (cost=7,402,641.20..10,030,421.50 rows=32,096,833 width=481) (actual time=14,570.995..15,974.191 rows=1,995 loops=1)

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

31. 15.866 12,798.367 ↑ 16,088.6 1,995 1

Hash (cost=4,977,825.71..4,977,825.71 rows=32,096,833 width=470) (actual time=12,798.367..12,798.367 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
32. 90.845 12,782.501 ↑ 16,088.6 1,995 1

Hash Join (cost=2,627,570.55..4,977,825.71 rows=32,096,833 width=470) (actual time=9,894.867..12,782.501 rows=1,995 loops=1)

  • Hash Cond: ("*SELECT* 1_1"."PlanID" = "*SELECT* 1"."PlanID")
33. 2,816.944 9,544.617 ↑ 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,740.976..9,544.617 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.007 4.007 ↑ 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.007 rows=20,369 loops=1)

35. 1,057.315 6,723.666 ↑ 4.7 997,677 1

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

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

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

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

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

38. 1.163 7.747 ↓ 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.474..7.747 rows=8,828 loops=1)

39. 5.714 6.584 ↓ 1.0 8,828 1

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

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

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

42. 63.311 105.073 ↑ 3.5 231,259 1

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

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

44. 1.265 6.579 ↓ 1.0 8,828 1

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

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

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

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

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

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

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

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

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

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

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

Seq Scan on "BMResource" resource (cost=0.00..154,423.10 rows=4,706 width=315) (actual time=0.020..22.867 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.482 3,147.039 ↑ 1.2 1,190 1

Hash (cost=1,094,001.84..1,094,001.84 rows=1,382 width=78) (actual time=3,147.039..3,147.039 rows=1,190 loops=1)

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

Nested Loop Left Join (cost=47.61..1,094,001.84 rows=1,382 width=78) (actual time=0.450..3,146.557 rows=1,190 loops=1)

  • Join Filter: (NOT "*SELECT* 1"."isDelegated")
57. 0.177 3,145.422 ↑ 1.2 1,190 1

Nested Loop (cost=47.19..1,083,423.40 rows=1,382 width=78) (actual time=0.448..3,145.422 rows=1,190 loops=1)

58. 0.013 0.013 ↑ 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.013 rows=1 loops=1)

  • Index Cond: ("AccountID" = 1000002)
59. 0.102 3,145.232 ↑ 1.2 1,190 1

Append (cost=46.77..1,083,401.14 rows=1,382 width=62) (actual time=0.432..3,145.232 rows=1,190 loops=1)

60. 0.158 3.072 ↑ 1.2 1,190 1

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

61. 2.643 2.914 ↑ 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.432..2.914 rows=1,190 loops=1)

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

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

Subquery Scan on *SELECT* 2 (cost=54,485.27..1,075,482.70 rows=1 width=62) (actual time=3,142.058..3,142.058 rows=0 loops=1)

64. 62.571 3,142.057 ↓ 0.0 0 1

Hash Join (cost=54,485.27..1,075,482.69 rows=1 width=62) (actual time=3,142.057..3,142.057 rows=0 loops=1)

  • Hash Cond: (COALESCE((SubPlan 8), delegated."PlanID") = p_1."PlanID")
65. 0.277 2,025.804 ↓ 10.0 20 1

Hash Right Join (cost=100.76..507,740.51 rows=2 width=8) (actual time=2,025.760..2,025.804 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. 2,025.249 2,025.249 ↑ 479.2 235 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
68. 0.055 0.226 ↓ 8.7 253 1

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

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

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

70. 0.007 0.007 ↓ 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.007 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.143 0.143 ↑ 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.143 rows=253 loops=1)

  • Index Cond: ("serviceTemplateID" = template."serviceTemplateID")
  • Heap Fetches: 253
73. 51.621 293.522 ↑ 1.0 137,743 1

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

  • Buckets: 32768 Batches: 8 Memory Usage: 1826kB
74. 241.901 241.901 ↑ 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.019..241.901 rows=137,743 loops=1)

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

SubPlan (for Hash Join)

76. 0.060 760.160 ↑ 1.0 1 20

Aggregate (cost=511,871.65..511,871.66 rows=1 width=4) (actual time=38.008..38.008 rows=1 loops=20)

77. 0.128 760.100 ↓ 0.0 0 20

Nested Loop (cost=0.42..511,871.43 rows=88 width=8) (actual time=38.005..38.005 rows=0 loops=20)

  • Join Filter: (rdp_2."AccountID" = ai."VendorAccountID")
  • Rows Removed by Join Filter: 2
78. 759.580 759.580 ↑ 56,310.0 2 20

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

  • Filter: ("ParentPlanID" = delegated."PlanID")
  • Rows Removed by Filter: 241227
79. 0.072 0.392 ↑ 3.0 1 49

Materialize (cost=0.42..12.23 rows=3 width=8) (actual time=0.008..0.008 rows=1 loops=49)

80. 0.320 0.320 ↑ 3.0 1 20

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

  • Index Cond: ("CustomerAccountID" = res_subscr."AccountID")
81. 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
82.          

SubPlan (for GroupAggregate)

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

85. 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
86. 3.772 3.772 ↑ 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.004 rows=1 loops=943)

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

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

88. 4.527 47.880 ↑ 4.0 1 1,995

Nested Loop Left Join (cost=5.17..93.40 rows=4 width=72) (actual time=0.018..0.024 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
89. 0.633 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)

90. 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
91. 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")
92. 5.595 13.428 ↓ 2.2 9 1,119

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

93. 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.006..0.007 rows=9 loops=1,119)

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

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

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

97. 0.000 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"))
98. 3.990 13.965 ↓ 0.0 0 1,995

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

99. 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")
100. 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")
101. 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"))
102. 1.995 15.960 ↑ 1.0 1 1,995

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

103. 1.995 13.965 ↓ 0.0 0 1,995

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

104. 1.995 11.970 ↓ 0.0 0 1,995

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

106. 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")
107. 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")
108. 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)