explain.depesz.com

PostgreSQL's explain analyze made readable

Result: psI4 : Optimization for: plan #8ya3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.485 21,887.619 ↑ 29,062.5 1,110 1

Subquery Scan on FlatProductItem2 (cost=535,974,311.01..5,765,247,040.24 rows=32,259,408 width=1,125) (actual time=21,727.098..21,887.619 rows=1,110 loops=1)

2. 54.563 21,887.134 ↑ 29,062.5 1,110 1

GroupAggregate (cost=535,974,311.01..5,764,924,446.16 rows=32,259,408 width=1,370) (actual time=21,727.096..21,887.134 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. 69.634 1,049.399 ↑ 93.4 241,229 1

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

5. 256.933 256.933 ↑ 1.0 231,259 1

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

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

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

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

8. 104.652 571.572 ↑ 1.0 231,259 2

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

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

  • Filter: ("Copied_From_PlanID" IS NOT NULL)
  • Rows Removed by Filter: 8828
10. 7.052 21,727.932 ↑ 16,170.1 1,995 1

Sort (cost=533,922,200.18..534,002,848.70 rows=32,259,408 width=1,033) (actual time=21,726.828..21,727.932 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,542.284 21,720.880 ↑ 16,170.1 1,995 1

Hash Right Join (cost=430,954,472.86..456,574,930.67 rows=32,259,408 width=1,033) (actual time=20,360.988..21,720.880 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.254 4,927.416 ↑ 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,095.198..4,927.416 rows=147,830 loops=1)

13. 542.325 4,903.162 ↑ 1.6 147,830 1

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

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

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

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

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

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

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

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

19. 502.758 1,284.503 ↑ 458.7 570,248 1

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

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

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

21. 32.233 632.531 ↑ 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=163.762..632.531 rows=269,487 loops=1)

22. 156.358 600.298 ↑ 969.5 269,487 1

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

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

24. 47.019 257.582 ↑ 51.4 438,558 1

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

25. 171.536 210.563 ↑ 93.4 241,209 1

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

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

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

28. 30.320 15,251.180 ↑ 16,170.1 1,995 1

Hash (cost=13,149,286.14..13,149,286.14 rows=32,259,408 width=481) (actual time=15,251.180..15,251.180 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
29. 1,982.078 15,220.860 ↑ 16,170.1 1,995 1

Hash Right Join (cost=10,511,660.69..13,149,286.14 rows=32,259,408 width=481) (actual time=13,871.294..15,220.860 rows=1,995 loops=1)

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

31. 16.185 12,143.797 ↑ 16,170.1 1,995 1

Hash (cost=8,074,562.57..8,074,562.57 rows=32,259,408 width=470) (actual time=12,143.797..12,143.797 rows=1,995 loops=1)

  • Buckets: 4096 Batches: 8192 Memory Usage: 32kB
32. 92.777 12,127.612 ↑ 16,170.1 1,995 1

Hash Join (cost=5,716,875.44..8,074,562.57 rows=32,259,408 width=470) (actual time=9,275.685..12,127.612 rows=1,995 loops=1)

  • Hash Cond: ("*SELECT* 1_1"."PlanID" = "*SELECT* 1"."PlanID")
33. 2,781.130 10,803.166 ↑ 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=8,036.613..10,803.166 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.352 4.352 ↑ 19.4 20,369 1

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

35. 1,048.788 8,017.684 ↑ 4.7 997,677 1

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

  • Buckets: 8192 (originally 8192) Batches: 16384 (originally 1024) Memory Usage: 2779kB
36. 1,173.353 6,968.896 ↑ 4.7 997,677 1

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

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

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

38. 1.128 7.652 ↓ 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.492..7.652 rows=8,828 loops=1)

39. 5.620 6.524 ↓ 1.0 8,828 1

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

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

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

42. 81.679 1,223.135 ↑ 3.5 231,259 1

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

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

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

44. 1.434 6.698 ↓ 1.0 8,828 1

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

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

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

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

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

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

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

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

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

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

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

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

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

SubPlan (for Seq Scan)

53. 0.000 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. 18.822 18.822 ↑ 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.002 rows=1 loops=9,411)

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

Hash (cost=4,183,306.64..4,183,306.64 rows=1,389 width=78) (actual time=1,231.669..1,231.669 rows=1,190 loops=1)

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

Nested Loop Left Join (cost=47.61..4,183,306.64 rows=1,389 width=78) (actual time=86.686..1,231.194 rows=1,190 loops=1)

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

Nested Loop (cost=47.19..4,172,677.07 rows=1,389 width=78) (actual time=86.681..1,229.971 rows=1,190 loops=1)

58. 86.228 86.228 ↑ 1.0 1 1

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

  • Filter: ("AccountID" = 1000002)
  • Rows Removed by Filter: 168454
59. 0.109 1,143.525 ↑ 1.2 1,190 1

Append (cost=46.77..4,156,383.01 rows=1,389 width=62) (actual time=0.445..1,143.525 rows=1,190 loops=1)

60. 0.176 3.201 ↑ 1.2 1,190 1

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

61. 2.740 3.025 ↑ 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.444..3.025 rows=1,190 loops=1)

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

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

Subquery Scan on *SELECT* 2 (cost=563,306.10..4,148,464.58 rows=8 width=62) (actual time=1,140.215..1,140.215 rows=0 loops=1)

64. 19.395 1,140.214 ↓ 0.0 0 1

Hash Join (cost=563,306.10..4,148,464.50 rows=8 width=62) (actual time=1,140.214..1,140.214 rows=0 loops=1)

  • Hash Cond: (COALESCE((SubPlan 8), delegated."PlanID") = p_1."PlanID")
65. 1.143 40.667 ↓ 1.4 20 1

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

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

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

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

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

68. 0.017 0.017 ↓ 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.013..0.017 rows=5 loops=1)

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

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

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

  • Index Cond: ("serviceTemplateID" = template."serviceTemplateID")
  • Heap Fetches: 253
71. 0.144 39.236 ↑ 479.2 235 1

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

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

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

  • Filter: ("AccountID" = 1000002)
  • Rows Removed by Filter: 240994
73. 51.227 288.332 ↑ 1.0 137,743 1

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

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

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

SubPlan (for Hash Join)

76. 0.080 791.820 ↑ 1.0 1 20

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

77. 0.119 791.740 ↓ 0.0 0 20

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

  • Join Filter: (rdp_2."AccountID" = ai."VendorAccountID")
  • Rows Removed by Join Filter: 2
78. 791.180 791.180 ↑ 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.513..39.559 rows=2 loops=20)

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

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

80. 0.340 0.340 ↑ 3.0 1 20

Index Scan using "AccountInheritance_PK" on "AccountInheritance" ai (cost=0.42..12.22 rows=3 width=8) (actual time=0.017..0.017 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. 5.646 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. 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)

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