Improve snippet finders queries
What does this MR do?
In this MR we improve two queries of the SnippetFinder
.
First Query
The original query is
Old Query
SELECT “snippets”.*
FROM
(
(
SELECT “snippets”.*
FROM “snippets”
WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152) AND “snippets”.“project_id” IS NULL
)
UNION
(
SELECT “snippets”.*
FROM “snippets”
INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id”
INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id”
WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152)
AND (projects.visibility_level IN (0,10,20))
AND “project_features”.“snippets_access_level” IN (20, 30)
)
UNION
(
SELECT “snippets”.*
FROM “snippets”
INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id”
INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id”
WHERE “project_features”.“snippets_access_level” IN (20, 30, 10)
AND (EXISTS (SELECT 1 FROM “project_authorizations” WHERE (project_id = snippets.project_id) AND “project_authorizations”.“user_id” = 5249152))
)
) snippets
ORDER BY “snippets”.“updated_at” DESC
LIMIT 20
New Query
SELECT “snippets”.*
FROM “snippets”
WHERE id IN
(
(
SELECT “snippets”.id
FROM “snippets”
WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152) AND “snippets”.“project_id” IS NULL
)
UNION
(
SELECT “snippets”.id
FROM “snippets”
INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id”
INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id”
WHERE (“snippets”.“visibility_level” IN (0, 10, 20) OR “snippets”.“author_id” = 5249152)
AND (projects.visibility_level IN (0,10,20))
AND “project_features”.“snippets_access_level” IN (20, 30)
)
UNION
(
SELECT “snippets”.id
FROM “snippets”
INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id”
INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id”
WHERE “project_features”.“snippets_access_level” IN (20, 30, 10)
AND (EXISTS (SELECT 1 FROM “project_authorizations” WHERE (project_id = snippets.project_id) AND “project_authorizations”.“user_id” = 5249152))
)
)
ORDER BY “snippets”.“updated_at” DESC
LIMIT 20
The table of execution times is:
Type | Old Query | New Query |
---|---|---|
Without Order | 2.5-3s (query plan) | 1.5s (query plan) |
With Order | 7.363s (query plan) | 2.581s (query plan) |
Second Query
The original query is
Old Query
SELECT “snippets”.*
FROM (
(SELECT “snippets”.*
FROM “snippets”
WHERE “snippets”.“author_id” = 5249152
AND “snippets”.“project_id” IS NULL)
UNION
(SELECT “snippets”.*
FROM “snippets”
INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id”
INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id”
WHERE “project_features”.“snippets_access_level” IN (20,
30,
10)
AND (EXISTS
(SELECT 1
FROM “project_authorizations”
WHERE (project_id = snippets.project_id)
AND “project_authorizations”.“user_id” = 5249152)))) snippets
LIMIT 20
New Query
SELECT “snippets”.*
FROM “snippets”
WHERE “snippets”.“id” IN
(SELECT “snippets”.“id”
FROM (
(SELECT “snippets”.“id”
FROM “snippets”
WHERE “snippets”.“author_id” = 5249152
AND “snippets”.“project_id” IS NULL)
UNION
(SELECT “snippets”.“id”
FROM “snippets”
INNER JOIN “projects” ON “projects”.“id” = “snippets”.“project_id”
INNER JOIN “project_features” ON “project_features”.“project_id” = “projects”.“id”
WHERE “project_features”.“snippets_access_level” IN (20,
30,
10)
AND (EXISTS
(SELECT 1
FROM “project_authorizations”
WHERE (project_id = snippets.project_id)
AND “project_authorizations”.“user_id” = 5249152)))) snippets)
LIMIT 20
The table of execution times is:
Type | Old Query | New Query |
---|---|---|
Without Order | 447ms (query plan) | 48ms (query plan) |
With Order | 494ms (query plan) | 46ms (query plan) |
Ref #227311 (closed)
Does this MR meet the acceptance criteria?
Conformity
Edited by Francisco Javier López (ex-Gitlab)