Plugins » foreman-tasks

There is currently no index on foreman_tasks_tasks.state, which leads to very long queries when finding tasks that are not in 'stopped' state. For example, on a system with 1.5M tasks in the table, this query can take upwards of eight minutes:

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 ('some-uuid')) AND "foreman_tasks_locks"."name" = 'update' AND "foreman_tasks_locks"."resource_id" = 1234 AND "foreman_tasks_locks"."resource_type" = 'Host::Managed'

There is a big sequential scan on 'state' to exclude everything that's in stopped state.

Adding this index speeds the query up from 8 minutes to 9 msec (don't forget to run 'analyze;' after adding an index):

'create index test_idx on foreman_tasks_tasks ((state <> 'stopped'));'