Tag listing is slower than expected
# Overview
Our company uses GitLab container (version 4.34.0) registry separately from GitLab itself. Our workload requires handling a high number of image tags per repository, with the most problematic repository currently having ~500,000 tags. Tag listing for this largest repository takes much longer than expected (currently 72 seconds).
# Problem
The [documentation](https://gitlab.com/gitlab-org/container-registry/-/blob/master/docs/spec/gitlab/http-api-queries.md#tags) shows SQL query for tag listing. Even without pagination, it executes much faster than what we're observing:
```
# EXPLAIN (ANALYZE,BUFFERS,VERBOSE,TIMING) SELECT id, top_level_namespace_id, repository_id, manifest_id, name, created_at, updated_at FROM tags WHERE top_level_namespace_id = '3' AND repository_id = '2' ORDER BY name;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tags_p_57_top_level_namespace_id_repository_id_name_key on partitions.tags_p_57 tags (cost=0.55..49535.39 rows=491457 width=162) (actual time=0.027..424.259 rows=491841 loops=1)
Output: tags.id, tags.top_level_namespace_id, tags.repository_id, tags.manifest_id, tags.name, tags.created_at, tags.updated_at
Index Cond: ((tags.top_level_namespace_id = '3'::bigint) AND (tags.repository_id = '2'::bigint))
Buffers: shared hit=506706
Query Identifier: 4304366928498447998
Planning:
Buffers: shared hit=99
Planning Time: 0.405 ms
Execution Time: 442.756 ms
(9 rows)
```
`pg_stat_statements` reveals that the problem is actually in another query:
```
# SELECT total_exec_time, mean_exec_time, calls, query FROM pg_stat_statements ORDER BY total_exec_time DESC limit 3;
total_exec_time | mean_exec_time | calls | query
--------------------+--------------------+----------+------------------------------------------------------------------------------------------------------------------
3327369600.2000575 | 98.34944978308418 | 33832112 | SELECT +
| | | $1 +
| | | FROM +
| | | tags +
| | | WHERE +
| | | top_level_namespace_id = $2 +
| | | AND repository_id = $3 +
| | | AND name LIKE $4 AND name > $5
2241813280.313653 | 457.7347755929858 | 4897625 | DELETE FROM manifests WHERE top_level_namespace_id = $1 AND repository_id = $2 AND digest = decode( $3 , $4)
61920865.86521688 | 1.8300905795042925 | 33834864 | SELECT +
| | | id, +
| | | top_level_namespace_id, +
| | | name, +
| | | repository_id, +
| | | manifest_id, +
| | | created_at, +
| | | updated_at +
| | | FROM +
| | | tags +
| | | WHERE +
| | | top_level_namespace_id = $1 +
| | | AND repository_id = $2 +
| | | AND name > $3 +
| | | ORDER BY +
| | | name +
| | | LIMIT $4
(3 rows)
```
Notice the very similar number of calls between the top query and the third query. However, the first query has no `LIMIT`, which it probably should. As a result, we see a significantly higher `mean_exec_time`.
`pg_stat_activity` shows many such queries running concurrently. In fact, most of the time `SELECT query FROM pg_stat_activity WHERE state='active' ORDER BY query_start;` returns nothing but queries like this (and those queries do not return anything meaningful, all of them are `SELECT 1 FROM tags ...`).
# Suspected cause
A cursory search of the source code identifies only one location with a similar query: function [HasTagsBeforeName](https://gitlab.com/gitlab-org/container-registry/-/blob/master/registry/datastore/repository.go?ref_type=heads#L1480). Perhaps a reasonable `LIMIT` can be added there.
issue