Geo SQL query for counting LFS objects is very slow
This query started executing since the 14th:
SELECT COUNT(*) FROM "lfs_objects" WHERE ("lfs_objects"."file_store" = ? OR "lfs_objects"."file_store" IS NULL)
It takes an average of 1.9 seconds to execute, with a minimum of 1.2 seconds, and a worst case of 2.4 seconds. Grafana data for this query: https://performance.gitlab.net/dashboard/db/postgres-single-query-drill-down?orgId=1&var-environment=prd&var-queryid=1534384884&var-fqdn=All
The plan for this query is:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=216593.05..216593.06 rows=1 width=8) (actual time=4814.910..4814.911 rows=1 loops=1)
-> Seq Scan on lfs_objects (cost=0.00..203023.88 rows=5427670 width=0) (actual time=0.010..3382.029 rows=5434054 loops=1)
Filter: ((file_store = 2) OR (file_store IS NULL))
Rows Removed by Filter: 6
Planning time: 0.076 ms
Execution time: 4815.003 ms
The file_store
column is not indexed. However, even with an index this query will not perform too well because of the OR file_store IS NULL
clause. Using a non-null default value would circumvent this issue.