Index tuning

[jcoyne]

When we deployed to the new server we had trouble with the WorkflowQueuesController making long running queryies. We did some index tweaking, but it might be more that we could do if the need arose.

EXPLAIN ( (SELECT "workflow_steps"."druid" FROM "workflow_steps" WHERE "workflow_steps"."active_version" =
true AND "workflow_steps"."status" = 'waiting' AND "workflow_steps"."repository" = 'dor' AND
"workflow_steps"."workflow" = 'assemblyWF' AND
"workflow_steps"."process" = 'checksum-compute'
AND "workflow_steps"."lane_id" = 'default') INTERSECT
(SELECT "workflow_steps"."druid" FROM "workflow_steps"
WHERE "workflow_steps"."active_version" = true AND
"workflow_steps"."status" IN ('completed', 'skipped')
AND "workflow_steps"."repository" = 'dor'
AND "workflow_steps"."workflow" = 'assemblyWF'
AND "workflow_steps"."process" = 'content-metadata-create'
) );
                                                                                                                              QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
 HashSetOp Intersect  (cost=0.69..61532.74 rows=1 width=36)
   ->  Append  (cost=0.69..61493.17 rows=15829 width=36)
         ->  Subquery Scan on "*SELECT* 1"  (cost=0.69..6.88 rows=1 width=21)
               ->  Index Scan using step_name_with_druid_workflow_idx on workflow_steps  (cost=0.69..6.87 rows=1 width=17)
                     Index Cond: (((status)::text = 'waiting'::text) AND ((workflow)::text = 'assemblyWF'::text) AND ((process)::text = 'checksum-compu
te'::text) AND ((repository)::text = 'dor'::text))
                     Filter: (active_version AND ((lane_id)::text = 'default'::text))
         ->  Subquery Scan on "*SELECT* 2"  (cost=794.33..61407.14 rows=15828 width=21)
               ->  Bitmap Heap Scan on workflow_steps workflow_steps_1  (cost=794.33..61248.86 rows=15828 width=17)
                     Recheck Cond: (((status)::text = ANY ('{completed,skipped}'::text[])) AND ((workflow)::text = 'assemblyWF'::text) AND ((process)::
text = 'content-metadata-create'::text) AND ((repository)::text = 'dor'::text))
                     Filter: active_version
                     ->  Bitmap Index Scan on active_version_step_name_workflow_idx  (cost=0.00..790.38 rows=15828 width=0)
                           Index Cond: ((active_version = true) AND ((status)::text = ANY ('{completed,skipped}'::text[])) AND ((workflow)::text = 'ass
emblyWF'::text) AND ((process)::text = 'content-metadata-create'::text) AND ((repository)::text = 'dor'::text))
(12 rows)

of particular interest was why the first subquery was using the step_name_with_druid_workflow_idx index rather than active_version_step_name_workflow_idx

[ndushay]

Is this still relevant? or can it be closed?

[jcoyne]

I don't believe this has been addressed. On the other hand, I don't see this as something we should be concerned about right now. You can close it if you want.