Geo SQL query for counting uploads is very slow
Geo uses the following query for counting uploads:
SELECT COUNT(*) FROM "uploads" WHERE ("uploads"."store" = ? OR "uploads"."store" IS NULL)
This query on average takes 1.2 seconds to execute, with a minimum of 820 milliseconds, and a maximum of 1.7 seconds. The grafana data for this query can be found at https://performance.gitlab.net/dashboard/db/postgres-single-query-drill-down?orgId=1&var-environment=prd&var-queryid=3052530256&var-fqdn=All.
The query plan of such a query is as follows:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=131912.58..131912.59 rows=1 width=8) (actual time=2905.989..2905.989 rows=1 loops=1)
-> Seq Scan on uploads (cost=0.00..125377.85 rows=2613892 width=0) (actual time=0.021..2059.659 rows=3159669 loops=1)
Filter: ((store = 1) OR (store IS NULL))
Planning time: 0.267 ms
Execution time: 2906.034 ms
(5 rows)
This query can only be optimised by using an approximate count, or by using an explicit counter stored/maintained in a separate table.