Plugins » foreman-tasks

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.

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