Bug #16779
Updated by Ivan Necas over 7 years ago
When a locking query includes limit on 'exclusive' flag, the execution plan
can be inefficient due to missing index.
<pre>
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';
</pre>
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.
EDIT:
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