Geo SQL query for counting projects with wikis is very slow
As part of https://gitlab.com/gitlab-com/infrastructure/issues/4207 I started poking around pg_stat_statements
, and found the following query:
SELECT COUNT(*) FROM projects LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (project_features.wiki_access_level IN (?, ?) OR project_features.wiki_access_level IS NULL)
Real example:
SELECT COUNT(*) FROM projects LEFT JOIN project_features ON projects.id = project_features.project_id WHERE (project_features.wiki_access_level IN (20, 10) OR project_features.wiki_access_level IS NULL);
This query runs on the primary and takes an average time of 7.3 seconds to execute, a minimum of 5.5 seconds, and it's the slowest query across the entire primary database. Its query plan is as follows:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=386183.62..386183.63 rows=1 width=8) (actual time=13921.399..13921.400 rows=1 loops=1)
-> Merge Left Join (cost=4.03..374823.27 rows=4544138 width=0) (actual time=0.023..12690.793 rows=4485139 loops=1)
Merge Cond: (projects.id = project_features.project_id)
Filter: ((project_features.wiki_access_level = ANY ('{20,10}'::integer[])) OR (project_features.wiki_access_level IS NULL))
Rows Removed by Filter: 141500
-> Index Only Scan using projects_pkey on projects (cost=0.43..128671.47 rows=4718268 width=4) (actual time=0.011..2617.985 rows=4626639 loops=1)
Heap Fetches: 150097
-> Index Scan using index_project_features_on_project_id on project_features (cost=0.43..164982.47 rows=4625039 width=8) (actual time=0.007..5077.536 rows=4626640 loops=1)
Planning time: 0.368 ms
Execution time: 13921.460 ms
This query needs to be improved ASAP as this query is far too heavy to be running.
Grafana data for this query: https://performance.gitlab.net/dashboard/db/postgres-single-query-drill-down?orgId=1&var-environment=prd&var-queryid=3212735507&var-fqdn=All
cc @stanhu