query WITH CTE update - 7466522498371132859 / -6137311841828138141

Description

From the investigation described in this issue https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/12646#note_514086102

From @Finotto

I will propose to investigate and optimize the statement of WITH CTE ...UPDATE, which is having a long block of locking session, perhaps we should schedule more intervals between the execution?

Detailed breakdown here: https://gitlab.com/gitlab-com/gl-infra/infrastructure/-/issues/12646#note_512819193

Requested Data points

Please provide as many of these fields as possible when submitting a query performance report.

• Queries per second (on average or peak)
• Number of calls per second and relative to total number of calls
• Query timings (on average or peak)
• Database time relative to total database time
• Source of calls (Sidekiq, WebAPI, etc)
• Query ID : 7466522498371132859 -6137311841828138141
• SQL Statement :

WITH cte("cte_id", "cte_relative_position") AS (VALUES ($1::integer, $2::integer), ($3, $4))
 UPDATE issues SET "relative_position" = cte."cte_relative_position" FROM cte WHERE cte_id = id;

WITH cte("cte_id", "cte_relative_position") AS (VALUES ($1::integer, $2::integer))
 UPDATE issues SET "relative_position" = cte."cte_relative_position" FROM cte WHERE cte_id = id;

• Query Plan
• Query Example
• Total number of calls (relative)
• % of Total time