Skip to content

Improve snippet finders queries

Francisco Javier López (ex-Gitlab) requested to merge fj-227311-optimize-snippets-finder-query into master

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)

Merge request reports