Bug #16584
closedforeman_tasks_tasks table needs index on task state
Description
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'));'
Updated by Chris Duryee over 7 years ago
- Project changed from Katello to foreman-tasks
- Category deleted (
81)
Updated by Shlomi Zadok over 7 years ago
- Status changed from New to Duplicate
Latest version has an index to state...
See https://github.com/theforeman/foreman-tasks/blob/master/db/migrate/20160920151810_add_more_lock_indexes.rb
Duplicate of #15283
Updated by Shlomi Zadok over 7 years ago
- Is duplicate of Bug #15283: Slow response times due to Foreman locking query added