Slow query in production: queryid 2652168744
This query seems to be not frequently but when it does it's slow and it's running more frequently since the last deploy (though it's not entirely new):
gitlabhq_production=# explain analyze SELECT COUNT(*) FROM "projects" WHERE (EXISTS (SELECT 1 FROM "project_authorizations" WHERE "project_authorizations"."user_id" = 1939855 AND (project_authorizations.project_id = projects.id)) OR projects.visibility_level IN (10,20)) AND "projects"."visibility_level" = 0 AND "projects"."archived" = 'f';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=13428139.14..13428139.15 rows=1 width=8) (actual time=6151.512..6151.513 rows=1 loops=1)
-> Index Scan using index_projects_on_visibility_level on projects (cost=0.56..13423314.56 rows=1929833 width=0) (actual time=118.413..6151.492 rows=27 loops=1)
Index Cond: (visibility_level = 0)
Filter: ((NOT archived) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (visibility_level = ANY ('{10,20}'::integer[]))))
Rows Removed by Filter: 3552556
SubPlan 1
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations (cost=0.43..3.45 rows=1 width=0) (never executed)
Index Cond: ((user_id = 1939855) AND (project_id = projects.id))
Heap Fetches: 0
SubPlan 2
-> Index Only Scan using index_project_authorizations_on_user_id_project_id_access_level on project_authorizations project_authorizations_1 (cost=0.43..10.25 rows=94 width=4) (actual time=0.057..0.085 rows=27 loops=1)
Index Cond: (user_id = 1939855)
Heap Fetches: 1
Planning time: 0.226 ms
Execution time: 6151.603 ms
(15 rows)
Edited by Gregory Stark