Locks queries that include exclusive lock search can be slow
|Assigned To:||Ivan Necas|
|Target version:||Foreman - Team Ivan Iteration 5|
|Found in release:||Pull request:||https://github.com/theforeman/foreman-tasks/pull/205|
|Velocity based estimate||-|
When a locking query includes limit on 'exclusive' flag, the execution plan
can be inefficient due to missing index.
foreman=# explain analyze SELECT COUNT(*) FROM "foreman_tasks_locks" INNER JOIN "foreman_tasks_tasks" ON "foreman_tasks_tasks"."id" = "foreman_tasks_locks"."task_id" WHERE (foreman_tasks_tasks.state != 'stopped') AND ("foreman_tasks_locks"."task_id" NOT IN ('8abb9ca3-815f-49ea-b97b-6aa505f2a29a')) AND "foreman_tasks_locks"."name" = 'task_owner' AND "foreman_tasks_locks"."resource_id" = 1 AND "foreman_tasks_locks"."resource_type" = 'User' AND "foreman_tasks_locks"."exclusive" = 't';
This sql is generated when locks are getting checked when starting a new task, that requires exclusive lock.
I've also noticed we use (COUNT), while we are only interested into whether any such a record exist or not, so
using `exists?` instead of `any?` in https://github.com/theforeman/foreman-tasks/blob/4868e9df20c86fb54ab182fca4e7c1dd8fd8b6d0/app/models/foreman_tasks/lock.rb#L55
would be a bit more efficient. The most important thing though is the index here.
it turns out in Rails < 4.2, the way we added indexes was not working - this is prbably the reason we've seen the performance degradation lately in existing setups https://github.com/rails/rails/commit/9a0d35e820464f872b0340366dded639f00e19b9
Fixes #16779 - add missing locks
The support for defining index at column definition was added in Rails here
We need to fix migrations that happened before Rails 4.2