Follow-up from "Reorder snippets in lists using `updated_at` column"
This is a follow up from !34393 (comment 375185273).
Now that we can sort snippets by several criteria, there is a worst-case scenario that should be addressed.
As @iroussos pointed out, when the snippets finder does not search by author nor project, we perform a wide range search using 3 unions. The query is something like:
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
Without the ORDER BY
, this query execution times is around 2.5-3 seconds (query plan). Nevertheless, when we introduce the ORDER BY
the query execution time is increased a lot (7.363 s | query plan).
With a small rewrite of the query, we can reduce this time to 2.581 s (query plan):
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 change also works for the case when the ORDER BY
is not set, where we can reduce the execution time to 1.5s (query plan)
We are also changing another query that is present in the SnippetFinder
EE extension:
explain 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
The execution time is quite fast, around 447ms (query plan). When we introduce a sorting criteria the execution time is very similar 494ms (query plan).
If we apply the same optimization, the query would be something like:
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)
The execution time now is 48ms (query plan) and with a sort criteria the time is 46ms (query plan.