Slow query in production Queryid 2532025652 in users_finder.rb in by_external
users_finder.rb
in by_external
has this code return users = users.where.not(external: true) unless current_user&.admin?
which runs this SQL SELECT COUNT(*) FROM "users" WHERE ("users"."external" != ?)
Which does a sequential scan on users:
gitlabhq_production=# explain analyze SELECT COUNT(*) FROM "users" WHERE ("users"."external" != 't');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=188530.41..188530.42 rows=1 width=8) (actual time=2137.728..2137.728 rows=1 loops=1)
-> Seq Scan on users (cost=0.00..183190.53 rows=2135953 width=0) (actual time=0.007..1673.111 rows=1942676 loops=1)
Filter: (NOT external)
Planning time: 0.774 ms
Execution time: 2137.756 ms
(5 rows)
This takes an average of 1.2s and is run about 0.2 times per second though more at times: https://performance.gitlab.net/dashboard/db/postgres-single-query-drill-down?orgId=1&var-environment=prd&var-queryid=2532025652&var-fqdn=All&from=now-7d&to=now
Edited by Gregory Stark