We’ve been examining some of the slower Metaflow UI queries using the AWS RDS Performance Insights dashboard, and we’ve identified at least one slow query when running in RDS PostgreSQL 11.18 on a

db.m5.2xlarge

instance. The following query on a 200 million row

artifact_v3

takes up to four minutes to run:

SELECT * FROM ( SELECT flow_id,run_number,run_id,step_name,task_id,task_name,name,location,ds_type,sha,type,content_type,user_name,attempt_id,ts_epoch,tags,system_tags FROM artifact_v3 ) T WHERE flow_id = '[REDACTED]' AND run_id = '[REDACTED]' AND ("ds_type" = 'local') LIMIT 1;

While acknowledging that searching 200 million rows would be potentially slow, we’ve noticed via

EXPLAIN

and

EXPLAIN ANALYZE

that the query does not actually make use of the indexes defined on

artifacts_v3

. There is an index:

"artifact_v3_idx_str_ids_primary_key" btree (flow_id, run_id, step_name, task_name, attempt_id, name) WHERE run_id IS NOT NULL AND task_name IS NOT NULL

but for PostgreSQL to use the index we need to add

AND task_name IS NOT NULL

to the query. In general, would we ever expect either

run_id

or

task_name

to be null?

The original query takes 3m30s to run. The query with

AND task_name IS NOT NULL

takes 7s.

would this PR address the concern?

I think so? I’m not an SQL expert but I see that the migration creates the indices

metadata_v3_idx_str_ids_a_key

and

metadata_v3_idx_str_ids_a_key_with_task_id

which each include

run_id

in the key and no

WHERE

clauses.

… I mean, I could try this by creating the indices by hand. Aside from the time required to create them I assume they won’t break anything, correct?

index creation won’t break anything but you have to do it concurrently if you intend to use your db in parallel — it can take a while and can be detrimental to the performance of the db but unless you have a very very large db you should be fine.

Trying it now…

I created the indices as described in the PR and the UI response time is near-instant now. This is an improvement from the general response time of seconds for most dashboards and no response for the DAG boards. Thank you!

From AWS RDS Performance Insights, the query at the start of this thread at one point had an average latency of hundreds of seconds; with the new indices PI doesn’t even report a latency.