get_tags() in services/ui_backend_service/data/db/tables/base.py (line 272) has an inner subquery that hits every row in the table:
SELECT JSONB_ARRAY_ELEMENTS_TEXT(tags||system_tags) AS tag
FROM {table_name}No WHERE clause on the inner query, so it expands every JSONB array in every row before the outer DISTINCT runs. On a big runs_v3 table this is a full table scan with row multiplication.
Gets called from the periodic cache refresh (every 5 min in autocomplete.py line 33) and from the /tags endpoint (tag.py line 12). The GIN index on (tags || system_tags) doesn't help here since the query is using JSONB_ARRAY_ELEMENTS_TEXT for row expansion, not a containment operator.
Could probably scope the inner query to a time window or maintain the tag set incrementally instead of re-scanning everything.
get_tags()inservices/ui_backend_service/data/db/tables/base.py(line 272) has an inner subquery that hits every row in the table:No WHERE clause on the inner query, so it expands every JSONB array in every row before the outer DISTINCT runs. On a big runs_v3 table this is a full table scan with row multiplication.
Gets called from the periodic cache refresh (every 5 min in autocomplete.py line 33) and from the /tags endpoint (tag.py line 12). The GIN index on (tags || system_tags) doesn't help here since the query is using JSONB_ARRAY_ELEMENTS_TEXT for row expansion, not a containment operator.
Could probably scope the inner query to a time window or maintain the tag set incrementally instead of re-scanning everything.