Hide Forgot
+++ This bug was initially created as a clone of Bug #1381352 +++ 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. --- Additional comment from bkearney on 20161003T19:37:59 Created from redmine issue http://projects.theforeman.org/issues/16779 --- Additional comment from bkearney on 20161003T19:38:02 Upstream bug assigned to inecas --- Additional comment from bkearney on 20161101T14:01:09 Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/16779 has been resolved.
Upstream bug assigned to inecas
Moving this bug to POST for triage into Satellite 6 since the upstream issue http://projects.theforeman.org/issues/16779 has been resolved.
Verified. Satellite-6.3 Snap 9 Package versions: satellite-6.3.0-16.0.beta.el7sat.noarch tfm-rubygem-foreman-tasks-0.9.4-1.fm1_15.el7sat.noarch Indexes for foreman_tasks_tasks and foreman_tasks_locks are created properly. foreman=> \d foreman_tasks_tasks; Table "public.foreman_tasks_tasks" Column | Type | Modifiers ----------------+-----------------------------+----------- id | character varying | not null type | character varying | not null label | character varying | started_at | timestamp without time zone | ended_at | timestamp without time zone | state | character varying | not null result | character varying | not null external_id | character varying | parent_task_id | character varying | start_at | timestamp without time zone | start_before | timestamp without time zone | Indexes: "index_foreman_tasks_id_state" btree (id, state) "index_foreman_tasks_tasks_on_ended_at" btree (ended_at) "index_foreman_tasks_tasks_on_external_id" btree (external_id) "index_foreman_tasks_tasks_on_id" btree (id) "index_foreman_tasks_tasks_on_label" btree (label) "index_foreman_tasks_tasks_on_parent_task_id" btree (parent_task_id) "index_foreman_tasks_tasks_on_result" btree (result) "index_foreman_tasks_tasks_on_start_at" btree (start_at) "index_foreman_tasks_tasks_on_start_before" btree (start_before) "index_foreman_tasks_tasks_on_started_at" btree (started_at) "index_foreman_tasks_tasks_on_state" btree (state) "index_foreman_tasks_tasks_on_type" btree (type) "index_foreman_tasks_tasks_on_type_and_label" btree (type, label) foreman=> \d foreman_tasks_locks; Table "public.foreman_tasks_locks" Column | Type | Modifiers ---------------+-------------------+------------------------------------------------------------------ id | integer | not null default nextval('foreman_tasks_locks_id_seq'::regclass) task_id | character varying | not null name | character varying | not null resource_type | character varying | resource_id | integer | exclusive | boolean | Indexes: "foreman_tasks_locks_pkey" PRIMARY KEY, btree (id) "index_foreman_tasks_locks_name_resource_type_resource_id" btree (name, resource_type, resource_id) "index_foreman_tasks_locks_on_exclusive" btree (exclusive) "index_foreman_tasks_locks_on_name" btree (name) "index_foreman_tasks_locks_on_resource_type" btree (resource_type) "index_foreman_tasks_locks_on_resource_type_and_resource_id" btree (resource_type, resource_id) "index_foreman_tasks_locks_on_task_id" btree (task_id)
Since the problem described in this bug report should be resolved in a recent advisory, it has been closed with a resolution of ERRATA. For information on the advisory, and where to find the updated files, follow the link below. If the solution does not work for you, open a new bug report. https://access.redhat.com/errata/RHSA-2018:0336