Skip to content

Update project overrides query

Arturo Herrero requested to merge 218252-update-project-overrides-query into master

What does this MR do?

After introducing group-level integration, we have to exclude them from the query.

Basically, this excludes the possible groups with custom integration retrieved in the subquery. So, we fix a possible bug where pagination is not working as expected because some of the services could be group services that are going to be excluded from the query.

Follow-up from !33043 (merged) and !33838 (merged). Project.with_custom_integration_for is not used at the moment, the frontend is still work-in-progress. No user-facing change as there is no change to show.

Migration

We add an index to improve the query performance:

CREATE INDEX index_services_on_project_and_type_where_inherit_null ON services USING btree (project_id, type) WHERE (inherit_from_id IS NULL);

rails db:migrate

$ rails db:migrate
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: migrating =
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:services, [:project_id, :type], {:where=>"inherit_from_id IS NULL", :name=>"index_services_on_project_and_type_where_inherit_null", :algorithm=>:concurrently})
   -> 0.0053s
-- execute("SET statement_timeout TO 0")
   -> 0.0008s
-- add_index(:services, [:project_id, :type], {:where=>"inherit_from_id IS NULL", :name=>"index_services_on_project_and_type_where_inherit_null", :algorithm=>:concurrently})
   -> 0.0099s
-- execute("RESET ALL")
   -> 0.0007s
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: migrated (0.0178s)

rails db:migrate:down VERSION=20210414095944

$ rails db:migrate:down VERSION=20210414095944
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: reverting =
-- transaction_open?()
   -> 0.0000s
-- indexes(:services)
   -> 0.0051s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- remove_index(:services, {:algorithm=>:concurrently, :name=>"index_services_on_project_and_type_where_inherit_null"})
   -> 0.0064s
-- execute("RESET ALL")
   -> 0.0007s
== 20210414095944 AddIndexServicesOnProjectAndTypeWhereInheritNull: reverted (0.0139s)

Query

SELECT "projects".*
FROM "projects"
WHERE "projects"."id" IN
    (SELECT "services"."project_id"
     FROM "services"
     WHERE "services"."type" = 'JiraService'
       AND "services"."inherit_from_id" IS NULL
       AND "services"."project_id" IS NOT NULL
     LIMIT 20
     OFFSET 480)
Time: 90.220 ms
  - planning: 6.028 ms
  - execution: 84.192 ms
    - I/O read: 78.714 ms
    - I/O write: N/A

https://console.postgres.ai/shared/e053a4fa-bee1-40ce-ba0b-0febc8a757eb

Related to #218252 (closed)

Edited by Arturo Herrero

Merge request reports