Add missing metadata index.

[trhodeos]

We're running metaflow-service v2.4.4, and noticed the metaflow-backend was missing some metadata_v3 indices. This fixes the issue by adding the expected index.

[savingoyal]

@trhodeos can you help me with where is the index expected?

[trhodeos]

I only have a slow query that I was trying to fix... I'm not 100% sure where it gets generated:

SELECT * FROM (
SELECT
tasks_v3.flow_id AS flow_id,tasks_v3.run_number AS run_number,tasks_v3.run_id AS run_id,tasks_v3.step_name AS step_name,tasks_v3.task_id AS task_id,tasks_v3.task_name AS task_name,tasks_v3.user_name AS user_name,tasks_v3.ts_epoch AS ts_epoch,tasks_v3.last_heartbeat_ts AS last_heartbeat_ts,tasks_v3.tags AS tags,tasks_v3.system_tags AS system_tags,COALESCE(attempt.attempt_id, 0) as attempt_id,attempt.started_at as started_at,
(CASE
WHEN COALESCE(GREATEST(attempt.attempt_finished_at, attempt.task_ok_finished_at), next_attempt_start.ts_epoch) IS NULL
AND tasks_v3.last_heartbeat_ts IS NOT NULL
AND @(extract(epoch from now())-tasks_v3.last_heartbeat_ts)>120
THEN tasks_v3.last_heartbeat_ts*1000
ELSE COALESCE(GREATEST(attempt.attempt_finished_at, attempt.task_ok_finished_at), next_attempt_start.ts_epoch)
END) as finished_at
,attempt.attempt_ok as attempt_ok,
(CASE
WHEN attempt.attempt_ok IS NOT NULL
THEN NULL
ELSE attempt.task_ok_location
END) as task_ok
,
(CASE
WHEN attempt.attempt_ok IS TRUE
THEN 'completed'
WHEN attempt.attempt_ok IS FALSE
THEN 'failed'
WHEN COALESCE(attempt.attempt_finished_at, attempt.task_ok_finished_at) IS NOT NULL
AND attempt_ok IS NULL
THEN 'unknown'
WHEN COALESCE(attempt.attempt_finished_at, attempt.task_ok_finished_at) IS NOT NULL
THEN 'completed'
WHEN next_attempt_start.ts_epoch IS NOT NULL
THEN 'failed'
WHEN tasks_v3.last_heartbeat_ts IS NOT NULL
AND @(extract(epoch from now())-tasks_v3.last_heartbeat_ts)>120
AND COALESCE(attempt.attempt_finished_at, attempt.task_ok_finished_at) IS NULL
THEN 'failed'
WHEN tasks_v3.last_heartbeat_ts IS NULL
AND @(extract(epoch from now())*1000 - COALESCE(attempt.started_at, tasks_v3.ts_epoch))>86400000
AND COALESCE(attempt.attempt_finished_at, attempt.task_ok_finished_at) IS NULL
THEN 'failed'
WHEN tasks_v3.last_heartbeat_ts IS NULL
AND attempt IS NULL
THEN 'pending'
ELSE 'running'
END) AS status
,
(CASE
WHEN tasks_v3.last_heartbeat_ts IS NULL
AND @(extract(epoch from now())*1000 - COALESCE(attempt.started_at, tasks_v3.ts_epoch))>86400000
AND COALESCE(attempt.attempt_finished_at, attempt.task_ok_finished_at) IS NULL
THEN NULL
WHEN tasks_v3.last_heartbeat_ts IS NULL
AND attempt IS NULL
THEN NULL
ELSE
COALESCE(
GREATEST(attempt.attempt_finished_at, attempt.task_ok_finished_at),
next_attempt_start.ts_epoch,
tasks_v3.last_heartbeat_ts*1000,
@(extract(epoch from now())::bigint*1000)
) - COALESCE(attempt.started_at, tasks_v3.ts_epoch)
END) as duration
FROM tasks_v3
LEFT JOIN LATERAL (
SELECT
max(started_at) as started_at,
max(attempt_finished_at) as attempt_finished_at,
max(task_ok_finished_at) as task_ok_finished_at,
max(task_ok_location) as task_ok_location,
attempt_id :: int as attempt_id,
max(attempt_ok) :: boolean as attempt_ok,
task_id
FROM (
SELECT
task_id,
ts_epoch as started_at,
NULL::bigint as attempt_finished_at,
NULL::bigint as task_ok_finished_at,
NULL::text as task_ok_location,
NULL::text as attempt_ok,
(CASE
WHEN pg_typeof(value)='jsonb'::regtype
THEN value::jsonb->>0
ELSE value::text
END)::int as attempt_id
FROM metadata_v3 as meta
WHERE
tasks_v3.flow_id = meta.flow_id AND
tasks_v3.run_number = meta.run_number AND
tasks_v3.step_name = meta.step_name AND
tasks_v3.task_id = meta.task_id AND
meta.field_name = 'attempt'
UNION
SELECT
task_id,
NULL as started_at,
ts_epoch as attempt_finished_at,
NULL as task_ok_finished_at,
NULL as task_ok_location,
NULL as attempt_ok,
(CASE
WHEN pg_typeof(value)='jsonb'::regtype
THEN value::json->>0
ELSE value::text
END)::int as attempt_id
FROM metadata_v3 as meta
WHERE
tasks_v3.flow_id = meta.flow_id AND
tasks_v3.run_number = meta.run_number AND
tasks_v3.step_name = meta.step_name AND
tasks_v3.task_id = meta.task_id AND
meta.field_name = 'attempt-done'
UNION
SELECT
task_id,
NULL as started_at,
ts_epoch as attempt_finished_at,
NULL as task_ok_finished_at,
NULL as task_ok_location,
(CASE
WHEN pg_typeof(value)='jsonb'::regtype
THEN value::jsonb->>0
ELSE value::text
END) as attempt_ok,
(regexp_matches(tags::text, 'attempt_id:(\d+)'))[1]::int as attempt_id
FROM metadata_v3 as meta
WHERE
tasks_v3.flow_id = meta.flow_id AND
tasks_v3.run_number = meta.run_number AND
tasks_v3.step_name = meta.step_name AND
tasks_v3.task_id = meta.task_id AND
meta.field_name = 'attempt_ok'
UNION
SELECT
task_id,
NULL as started_at,
NULL as attempt_finished_at,
ts_epoch as task_ok_finished_at,
location as task_ok_location,
NULL as attempt_ok,
attempt_id as attempt_id
FROM artifact_v3 as task_ok
WHERE
tasks_v3.flow_id = task_ok.flow_id AND
tasks_v3.run_number = task_ok.run_number AND
tasks_v3.step_name = task_ok.step_name AND
tasks_v3.task_id = task_ok.task_id AND
task_ok.name = '_task_ok'
) a
WHERE a.attempt_id IS NOT NULL
GROUP BY a.task_id, a.attempt_id
) as attempt ON true
LEFT JOIN LATERAL (
SELECT ts_epoch
FROM metadata_v3 as next_attempt_start
WHERE
tasks_v3.flow_id = next_attempt_start.flow_id AND
tasks_v3.run_number = next_attempt_start.run_number AND
tasks_v3.step_name = next_attempt_start.step_name AND
tasks_v3.task_id = next_attempt_start.task_id AND
next_attempt_start.field_name = 'attempt' AND
(attempt.attempt_id + 1) = (next_attempt_start.value::jsonb->>0)::int
LIMIT 1
) as next_attempt_start ON true
) T
WHERE flow_id = 'MyFlow' AND run_number = '78624' AND step_name = 'start' AND task_id = '681747'
ORDER BY attempt_id DESC

[trhodeos]

The diff is that this query uses run_number and flow_id, where most of the metadata indices have run_id and flow_id... The metadata table PKey uses run_number and flow_id, but it also includes id, which this query doesn't include.

Without this index, MF backend was doing a lot of partial index scans on the run_id + flow_id indices

[savingoyal]

the index already exists, no?

[trhodeos]

Ah looks like it does, but doesn't on our db 🤦 Not sure why it doesn't exist for us. Let me manually apply that migrations (and double check that all of them were actually applied).