Fix EE::User#any_namespace_{with,without}_trial? methods to use correct table
What does this MR do?
EE::User#any_namespace_with_trial? and EE::User#any_namespace_without_trial? were using the namespaces.trial_ends_on column, which is no longer correct: it should be gitlab_subscriptions.trial_ends_on.
Most cases that use these columns are fine, because we have this in EE::Namespace:
delegate :trial?, :trial_ends_on, :trial_starts_on, :upgradable?, to: :gitlab_subscription, allow_nil: trueBut here we're using the columns in a query, so the delegations don't work there.
We don't actually need EE::User#any_namespace_with_trial? at all. This is only used when we also check if a user has paid namespaces. But a trial will also be on a paid plan (otherwise it would be pointless!) so we don't need two queries: !29908 (comment 333798298)
Query plans
This query plan depends a lot on the user. Here's the old and new queries for my user:
Old query for smcgivern https://explain.depesz.com/s/1QFw
gitlabhq_production=> EXPLAIN ANALYZE SELECT 1 AS one FROM ((SELECT "namespaces"."trial_ends_on" FROM "namespaces" WHERE "namespaces"."type" IS NULL AND "namespaces"."owner_id" = 443319)
gitlabhq_production(> UNION
gitlabhq_production(> (SELECT "namespaces"."trial_ends_on" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "namespaces"."type" = 'Group' AND "members"."user_id" = 443319 AND "members"."requested_at" IS NULL AND "members"."access_level" = 50 AND "namespaces"."parent_id" IS NULL)) namespaces WHERE "namespaces"."trial_ends_on" IS NULL LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Limit (cost=25.38..25.40 rows=1 width=4) (actual time=4.653..4.655 rows=1 loops=1)
-> Subquery Scan on namespaces (cost=25.38..25.41 rows=2 width=4) (actual time=4.652..4.652 row
s=1 loops=1)
-> Unique (cost=25.38..25.39 rows=2 width=8) (actual time=4.652..4.652 rows=1 loops=1)
-> Sort (cost=25.38..25.39 rows=2 width=8) (actual time=4.651..4.651 rows=1 loops=1
)
Sort Key: namespaces_1.trial_ends_on
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.43..25.37 rows=2 width=8) (actual time=0.156..4.628 rows=3
loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces namespace
s_1 (cost=0.43..3.45 rows=1 width=8) (actual time=0.156..0.157 rows=1 loops=1)
Index Cond: (owner_id = 443319)
Filter: ((type IS NULL) AND (trial_ends_on IS NULL))
-> Nested Loop (cost=0.87..21.90 rows=1 width=8) (actual time=1.357..4.
471 rows=2 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.4
4..18.44 rows=1 width=4) (actual time=0.416..3.689 rows=12 loops=1)
Index Cond: (user_id = 443319)
Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMem
ber'::text) AND ((source_type)::text = 'Namespace'::text) AND (access_level = 50))
Rows Removed by Filter: 106
-> Index Scan using namespaces_pkey on namespaces namespaces_2 (c
ost=0.43..3.45 rows=1 width=12) (actual time=0.064..0.064 rows=0 loops=12)
Index Cond: (id = members.source_id)
Filter: ((parent_id IS NULL) AND (trial_ends_on IS NULL) AND
((type)::text = 'Group'::text))
Rows Removed by Filter: 1
Planning time: 4.525 ms
Execution time: 4.766 ms
(21 rows)New query for smcgivern https://explain.depesz.com/s/s6Xx
gitlabhq_production=> EXPLAIN ANALYZE SELECT 1 AS one FROM ((SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" IS NULL AND "namespaces"."owner_id" = 443319)
gitlabhq_production(> UNION
gitlabhq_production(> (SELECT "namespaces"."id" FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "namespaces"."type" = 'Group' AND "members"."user_id" = 443319 AND "members"."requested_at" IS NULL AND "members"."access_level" = 50 AND "namespaces"."parent_id" IS NULL)) namespaces LEFT OUTER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."namespace_id" = "namespaces"."id" WHERE "gitlab_subscriptions"."trial_ends_on" IS NULL LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Limit (cost=25.81..29.07 rows=1 width=4) (actual time=0.555..0.557 rows=1 loops=1)
-> Nested Loop Left Join (cost=25.81..32.33 rows=2 width=4) (actual time=0.554..0.554 rows=1 lo
ops=1)
Filter: (gitlab_subscriptions.trial_ends_on IS NULL)
-> Unique (cost=25.38..25.39 rows=2 width=4) (actual time=0.481..0.481 rows=1 loops=1)
-> Sort (cost=25.38..25.39 rows=2 width=4) (actual time=0.481..0.481 rows=1 loops=1
)
Sort Key: namespaces.id
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.43..25.37 rows=2 width=4) (actual time=0.016..0.465 rows=4
loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces (cost=0.
43..3.45 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (owner_id = 443319)
Filter: (type IS NULL)
-> Nested Loop (cost=0.87..21.90 rows=1 width=4) (actual time=0.184..0.
448 rows=3 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.4
4..18.44 rows=1 width=4) (actual time=0.075..0.323 rows=12 loops=1)
Index Cond: (user_id = 443319)
Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMem
ber'::text) AND ((source_type)::text = 'Namespace'::text) AND (access_level = 50))
Rows Removed by Filter: 106
-> Index Scan using namespaces_pkey on namespaces namespaces_1 (c
ost=0.43..3.45 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=12)
Index Cond: (id = members.source_id)
Filter: ((parent_id IS NULL) AND ((type)::text = 'Group'::tex
t))
Rows Removed by Filter: 1
-> Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (c
ost=0.43..3.45 rows=1 width=8) (actual time=0.071..0.071 rows=1 loops=1)
Index Cond: (namespace_id = namespaces.id)
Planning time: 0.677 ms
Execution time: 0.625 ms
(24 rows)However, I only own three root namespaces:
gitlabhq_production=> SELECT COUNT(*) FROM members INNER JOIN namespaces ON namespaces.id = source_id WHERE user_id = 443319 AND requested_at IS NULL AND source_type = 'Namespace' AND access_level = 50 AND namespaces.parent_id IS NULL;
count
-------
3
(1 row)Let's pick an arbitrary user with more:
gitlabhq_production=> SELECT members.user_id, COUNT(*) FROM members INNER JOIN namespaces ON namespaces.id = source_id WHERE requested_at IS NULL AND source_type = 'Namespace' AND access_level = 50 AND namespaces.parent_id IS NULL GROUP BY members.user_id HAVING COUNT(*) > 100 LIMIT 1;
user_id | count
---------+-------
86545 | 206
(1 row)Old plan for 86545
QUERY PLAN
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Limit (cost=25.38..25.40 rows=1 width=4) (actual time=3.825..3.827 rows=1 loops=1)
-> Subquery Scan on namespaces (cost=25.38..25.41 rows=2 width=4) (actual time=3.824..3.824 row
s=1 loops=1)
-> Unique (cost=25.38..25.39 rows=2 width=8) (actual time=3.822..3.822 rows=1 loops=1)
-> Sort (cost=25.38..25.39 rows=2 width=8) (actual time=3.821..3.821 rows=1 loops=1
)
Sort Key: namespaces_1.trial_ends_on
Sort Method: quicksort Memory: 31kB
-> Append (cost=0.43..25.37 rows=2 width=8) (actual time=0.060..3.780 rows=20
7 loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces namespace
s_1 (cost=0.43..3.45 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1)
Index Cond: (owner_id = 86545)
Filter: ((type IS NULL) AND (trial_ends_on IS NULL))
-> Nested Loop (cost=0.87..21.90 rows=1 width=8) (actual time=0.111..3.
708 rows=206 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.4
4..18.44 rows=1 width=4) (actual time=0.086..0.532 rows=207 loops=1)
Index Cond: (user_id = 86545)
Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMem
ber'::text) AND ((source_type)::text = 'Namespace'::text) AND (access_level = 50))
Rows Removed by Filter: 71
-> Index Scan using namespaces_pkey on namespaces namespaces_2 (c
ost=0.43..3.45 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=207)
Index Cond: (id = members.source_id)
Filter: ((parent_id IS NULL) AND (trial_ends_on IS NULL) AND
((type)::text = 'Group'::text))
Rows Removed by Filter: 0
Planning time: 0.584 ms
Execution time: 3.867 ms
(21 rows)New query for 86545 https://explain.depesz.com/s/7NCb
QUERY PLAN
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Limit (cost=25.81..29.07 rows=1 width=4) (actual time=3.055..3.058 rows=1 loops=1)
-> Nested Loop Left Join (cost=25.81..32.33 rows=2 width=4) (actual time=3.054..3.054 rows=1 lo
ops=1)
Filter: (gitlab_subscriptions.trial_ends_on IS NULL)
-> Unique (cost=25.38..25.39 rows=2 width=4) (actual time=3.021..3.021 rows=1 loops=1)
-> Sort (cost=25.38..25.39 rows=2 width=4) (actual time=3.020..3.020 rows=1 loops=1
)
Sort Key: namespaces.id
Sort Method: quicksort Memory: 34kB
-> Append (cost=0.43..25.37 rows=2 width=4) (actual time=0.019..2.940 rows=20
7 loops=1)
-> Index Scan using index_namespaces_on_owner_id on namespaces (cost=0.
43..3.45 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)
Index Cond: (owner_id = 86545)
Filter: (type IS NULL)
-> Nested Loop (cost=0.87..21.90 rows=1 width=4) (actual time=0.069..2.
897 rows=206 loops=1)
-> Index Scan using index_members_on_user_id on members (cost=0.4
4..18.44 rows=1 width=4) (actual time=0.056..0.606 rows=207 loops=1)
Index Cond: (user_id = 86545)
Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMem
ber'::text) AND ((source_type)::text = 'Namespace'::text) AND (access_level = 50))
Rows Removed by Filter: 71
-> Index Scan using namespaces_pkey on namespaces namespaces_1 (c
ost=0.43..3.45 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=207)
Index Cond: (id = members.source_id)
Filter: ((parent_id IS NULL) AND ((type)::text = 'Group'::tex
t))
Rows Removed by Filter: 0
-> Index Scan using index_gitlab_subscriptions_on_namespace_id on gitlab_subscriptions (c
ost=0.43..3.45 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
Index Cond: (namespace_id = namespaces.id)
Planning time: 0.910 ms
Execution time: 3.150 ms
(24 rows)Plan for a user with 3059 namespaces https://explain.depesz.com/s/U5p6
Edited by Alper Akgun