Project

General

Profile

Actions

Bug #16584

closed

foreman_tasks_tasks table needs index on task state

Added by Chris Duryee over 7 years ago. Updated over 7 years ago.

Status:
Duplicate
Priority:
Normal
Assignee:
-
Category:
-
Target version:
-
Difficulty:
Triaged:
Fixed in Releases:
Found in Releases:

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'));'


Related issues 1 (0 open1 closed)

Is duplicate of foreman-tasks - Bug #15283: Slow response times due to Foreman locking queryClosedIvan Necas06/03/2016Actions
Actions #1

Updated by Chris Duryee over 7 years ago

  • Project changed from Katello to foreman-tasks
  • Category deleted (81)
Actions #2

Updated by Shlomi Zadok over 7 years ago

  • Status changed from New to Duplicate
Actions #3

Updated by Shlomi Zadok over 7 years ago

  • Is duplicate of Bug #15283: Slow response times due to Foreman locking query added
Actions

Also available in: Atom PDF