query performance review - 1 - 19 of November 2019
We have the following query that is generating excessive load on the replicas ( 20% of the cpu time from all the replicas in average):
---SELECT COUNT(*) FROM "projects" WHERE "projects"."namespace_id" IN (WITH RECURSIVE "base_and_descendants" AS (SELECT "namespaces".* FROM "namespaces" INNER JOIN "members" ON "namespaces"."id" = "members"."source_id" WHERE "members"."type" IN ('GroupMember') AND "members"."source_type" = 'Namespace' AND "namespaces"."type" IN ('Group') AND "members"."user_id" = 4589059 AND "members"."requested_at" IS NULL AND (members.access_level >= 30)UNIONSELECT "namespaces".* FROM "namespaces", "base_and_descendants" WHERE "namespaces"."type" IN ('Group') AND "namespaces"."parent_id" = "base_and_descendants"."id") SELECT DISTINCT "namespaces"."id" FROM "base_and_descendants" AS "namespaces" WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_descendants" AS (SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" IN ('Group') AND (EXISTS (SELECT 1 FROM "plans" INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id" WHERE "plans"."name" IN ('silver', ...
---
The use of fixed variables causes access plans to be created every time the query is executed. Please consider using the binding variables, which do not cause said plans to be created, which will cause the load on the database to drop.
Edited by Jose Finotto