feat(managementapi): accept repeated format and kind list filters

Hayley Swimelarrequested to merge
1152/repositories-list-multi-value-filters into main

Summary

Two UI surfaces need more than one kind per page: the repositories list's Type filter, and the virtual repository's upstream picker, which lists hosted and remote sources of one format together. The list filter takes one value today, so a client either merges two keyset streams (breaking page boundaries) or, as the Phase 3 picker in docs/specs/monolith/S07-repository-crud.md does, sends no filter and drops rows client-side, which can empty a whole page. This MR widens format and kind to repeated parameters (kind=hosted&kind=remote), declared in api/openapi/v1.yaml with explode: true.

Non-obvious parts:

  • Values dedup by first occurrence at the handler and again at the store (andRepositoryEnumFilter). An empty set appends nothing to the predicate, one distinct value renders =, several render IN, so every single-value SQL-text and EXPLAIN pin stays green unchanged.
  • No new index. TestRepositoryStore_List_MultiValueFilterPlanShape pins the two plans the planner picks by the requested values' share of the namespace (CI runs it on PG 16, 17, and 18). Dense, two thirds of the rows: the (namespace_id, name) index with format = ANY as a post-index Filter. Sparse, 2%: the (namespace_id, format, name) index, or (namespace_id, kind) for kind, then a top-N sort. A kind-heavy filter pays what the single-value case already paid. The new S17 Resolutions bullet states both shapes.
  • Compat, on purpose: format=npm&format= answered 200 on main (the second value was dropped) and answers 400 now. format=npm&format=maven moves from npm-only to the union. sort, order, and limit still take the first repeated value silently: pre-existing, unchanged, a follow-up candidate.
  • Rolling deploy: an old pod answers the repeated form with a first-value-only page and advances the cursor past the other values' rows, so a Link walk across pod versions can skip rows silently. No client sends the repeated form today. The S04 and S07 consumers are unbuilt and adopt it once this release is fully live.
  • Merge order: this rebases onto whichever lands first of three open MRs. Clean today against docs(specs): declare the S17 container-redesign... (!2317 - merged) • Hayley Swimelar • 19.4. One conflict, the docs {} paragraph in list-repositories.bru, against feat(managementapi): repository list envelope (... (!2295 - merged) • João Pereira • 19.4. One appended row each in docs/testing/e2e/npm.md and oci.md against fix(datastore): fence the npm publish and conta... (!2268 - merged) • Pawel Rozlach • 19.4. Measured with git merge-tree at ed6aab972.
  • Size: 953 changed lines (+765/-188) at c1c96638a, past the 500 LOC guardrail. Production Go 94, unit-test Go 377, integration-test Go 442, OpenAPI 22, Bruno 5, docs 13. The handler is inert without the store change and vice versa, and most of the diff is the test floor the struct-field retype forces across the mirrored suites plus the plan pins. Splitting would not help.
  • e2e catalog: two scenarios added (docs/testing/e2e/npm.md multi-kind, docs/testing/e2e/oci.md docker and oci family). Bruno: list-repositories.bru gains repeated example lines and docs prose, with no operation added, renamed, or removed.

Governing ADRs

  • ADR-007 (Database Schema). No index added. The dense plan is the post-index format and kind Filter ADR-007 already accepts for the non-name sorts, and the sparse plan reads the (namespace_id, format, name) and (namespace_id, kind) indexes it lists.
  • ADR-004 (Data and Application Limits). Both plans are bounded by the namespace's active rows, at most the 1,000 per-format repository cap times four formats.
  • ADR-009 (API Design). The list route "supports filtering by format and repository type" under keyset pagination and fixes no wire form for the filter. Conforms.

Testing

Spec coverage

Spec: docs/specs/S17-rest-management-api.md, Acceptance Criteria item 8 (line 1280) and the Resolutions bullets "Format-filtered name listing is indexed" (line 1615) and "Multi-value format and kind filters" (line 1616), at ed6aab972.

Acceptance criteria

# Criterion Tests
AC-8 S17 AC 8: GET list filters by one or more format and kind values, each a repeated parameter, and rejects an unrecognized value with 400 naming the parameter TestListHandler_ParamMapping, TestListHandler_Authz_FilteredBranch_ParamMapping, TestListHandler_InvalidParams_Return400, TestRepositoryStore_List_Filter, TestRepositoryStore_EnumerateRepositoryKeys_Filters
C-1 The OpenAPI contract and the handler agree on the repeated form: seven queries, the contract's verdict equals the handler's 200 or 400, and both reject the comma form TestContract_ListFilters_RepeatedValuesAgreeWithHandler
P1-1 format=npm&format=maven returns the union in one keyset page, and both values survive the Link walk TestListHandler_ParamMapping/two_formats_keep_first-occurrence_order, TestListHandler_Authz_FilteredBranch_ParamMapping/two_formats_keep_first-occurrence_order, TestRepositoryStore_List_Filter/two_formats_return_both_formats'_rows, TestRepositoryStore_EnumerateRepositoryKeys_Filters/two_formats_return_both_formats'_rows,_whatever_their_kind, TestListHandler_KeysetWalk_PreservesFilter, TestReadHandlersIntegration_MultiValueFilterWalk
P1-2 A single-value request behaves exactly as before The single-value rows of TestListHandler_ParamMapping, TestListHandler_Authz_FilteredBranch_ParamMapping, TestRepositoryStore_List_Filter, and TestRepositoryStore_EnumerateRepositoryKeys_Filters, plus TestListHandler_Authz_FormatFilterReachesEnumerator, TestListHandler_Authz_Filtered_MultiChunkCollection, TestEnumerateRepositoryKeysStmt_Clauses/carries_the_format_and_kind_equality_filters, and Q-4
P1-3 An unrecognized value anywhere in the set returns 400 naming the parameter, store untouched TestListHandler_InvalidParams_Return400/unknown_format_among_valid_ones, TestListHandler_InvalidParams_Return400/unknown_kind_among_valid_ones
P1-4 Every defined value listed returns the same page as omitting the filter TestRepositoryStore_List_Filter/every_defined_format_listed_returns_the_same_rows_as_no_filter, TestRepositoryStore_List_Filter/every_defined_kind_listed_returns_the_same_rows_as_no_filter, TestRepositoryStore_EnumerateRepositoryKeys_Filters/every_defined_format_listed_returns_the_same_rows_as_no_filter, TestRepositoryStore_EnumerateRepositoryKeys_Filters/every_defined_kind_listed_returns_the_same_rows_as_no_filter, TestListHandler_ParamMapping/every_defined_kind_listed_reaches_the_store_as_three_values
R-1615 S17 Resolutions: a single-value format filter on the default name sort rides the (namespace_id, format, name) index TestRepositoryStore_List_FormatFilterIsIndexBacked (assertions unchanged)

Error cases

# Condition Tests
E-1 Unknown value alone (format=container, kind=local): 400 bad_request naming the parameter, no store call TestListHandler_InvalidParams_Return400/unknown_format, TestListHandler_InvalidParams_Return400/unknown_kind
E-2 Unknown value among valid ones (format=npm&format=container, kind=hosted&kind=local): same contract TestListHandler_InvalidParams_Return400/unknown_format_among_valid_ones, TestListHandler_InvalidParams_Return400/unknown_kind_among_valid_ones
E-3 Empty value alone (format=, kind=): same contract, preserved TestListHandler_InvalidParams_Return400/empty_format, TestListHandler_InvalidParams_Return400/empty_kind
E-4 Empty value among valid ones (format=npm&format=): 400, where main answers 200 TestListHandler_InvalidParams_Return400/empty_format_among_valid_ones
E-5 A duplicate collapses to one distinct value (the = path), first-occurrence order kept TestListHandler_ParamMapping/a_duplicated_kind_collapses_to_one_value, TestListHandler_ParamMapping/a_repeated_format_deduplicates_by_first_occurrence_and_keeps_order, TestListHandler_Authz_FilteredBranch_ParamMapping/a_duplicated_kind_collapses_to_one_value
E-6 Every defined value listed is accepted and rendered as a full IN list (accepted trade-off, no special case) TestListHandler_ParamMapping/every_defined_kind_listed_reaches_the_store_as_three_values, TestListHandler_Authz_FilteredBranch_ParamMapping/every_defined_kind_listed_reaches_the_enumerator_as_three_values, TestAndRepositoryEnumFilter/three_values_render_three_placeholders
E-7 Store guard: an out-of-range element anywhere in Formats or Kinds returns errRepositoryInvalidFormat or errRepositoryInvalidKind before any SQL TestRepositoryStore_EnumerateRepositoryKeys_Guards (four filter rows), TestRepositoryStore_List_ArgumentGuards/invalid_format_filter, TestRepositoryStore_List_ArgumentGuards/invalid_kind_filter
E-8 Absent parameters leave Formats and Kinds nil, not empty, on the wire-to-store seam TestListHandler_ParamMapping/no_filter_parameters_leave_both_filter_sets_nil, TestListHandler_Authz_FilteredBranch_ParamMapping/no_filter_parameters_leave_both_filter_sets_nil

Security considerations

# Concern Tests
S-1 Input validation precedes any database access: every 400 row asserts repos.ListCalls() is empty TestListHandler_InvalidParams_Return400 (every row)
S-2 Tenant isolation is unchanged: the filter predicate is ANDed onto the namespace scope TestEnumerateRepositoryKeysStmt_Clauses/scopes_the_tenant_and_excludes_soft-deleted_rows, TestRepositoryStore_List_Filter (namespace-scoped seed)

SQL and plan pins

# Pin Tests
Q-1 Duplicates collapse before the shape is chosen, and exactly one distinct value renders repositories.<col> = $n::smallint, never IN TestAndRepositoryEnumFilter/one_format_value_renders_the_equality, TestAndRepositoryEnumFilter/one_kind_value_renders_the_equality, TestAndRepositoryEnumFilter/a_duplicated_value_collapses_to_the_equality, TestAndRepositoryEnumFilter/duplicates_collapse_to_the_distinct_values_in_first-seen_order, TestEnumerateRepositoryKeysStmt_Clauses/carries_the_format_and_kind_equality_filters
Q-2 Two or more values render repositories.<col> IN ($n::smallint, ...) in the given order, replacing the equality TestAndRepositoryEnumFilter/two_values_render_an_IN_list_in_the_given_order, TestAndRepositoryEnumFilter/three_values_render_three_placeholders, TestEnumerateRepositoryKeysStmt_Clauses/carries_the_format_and_kind_IN_filters_for_several_values
Q-3 A nil or empty set appends nothing: the statement renders byte-identical to the input predicate's, with no AND, IN (), or bound boolean, and an empty Formats leaves the enumeration's WHERE clause without the column TestAndRepositoryEnumFilter_EmptySetAppendsNothing/nil, TestAndRepositoryEnumFilter_EmptySetAppendsNothing/empty, TestEnumerateRepositoryKeysStmt_Clauses/empty_filter_sets_add_no_filter_clause
Q-4 Single-value plans unchanged: the name-sort format filter rides (namespace_id, format, name) with no Rows Removed by Filter, and the counter-sort enumeration keeps an index scan with no Sort Key: TestRepositoryStore_List_FormatFilterIsIndexBacked, TestRepositoryStore_EnumerateRepositoryKeys_FormatFilterIsIndexBacked
Q-5 Multi-value plan shapes on the name sort, for format and for kind. A dense share (two thirds of 5,000 rows) is an Index Scan on (namespace_id, name) with Filter: (<col> = ANY (...)), Rows Removed by Filter, and no Sort Key:. A sparse share (2%) reads (namespace_id, format, name) or (namespace_id, kind) with Sort Key: repositories.name and no Filter: (<col> TestRepositoryStore_List_MultiValueFilterPlanShape (dense and sparse cases for each column)

Commands

go test ./internal/managementapi/ ./internal/datastore/
go test ./internal/managementapi/ -run Contract
ARTIFACT_REGISTRY_DATABASE_TEST_DSN="$DSN" go test -tags=integration ./internal/datastore/ -run 'TestRepositoryStore_List_'
ARTIFACT_REGISTRY_DATABASE_TEST_DSN="$DSN" go test -tags=integration ./internal/managementapi/ -run 'TestReadHandlersIntegration_MultiValueFilterWalk'
golangci-lint run ./internal/managementapi/ ./internal/datastore/
golangci-lint run --build-tags=integration --max-same-issues=0 --max-issues-per-linter=0 --uniq-by-line=false ./internal/managementapi/ ./internal/datastore/

Against a running instance:

curl -sS -H "Authorization: Bearer $AR_TOKEN_DEV" \
  "http://localhost:8080/api/v1/$SLUG/repositories?format=npm&kind=hosted&kind=remote"

Closes #1152 (closed)

Context for LLM agents

Rationale

  • Comma-separated values (explode: false). Rejected: style: form with explode: true is the OpenAPI default for an array query parameter, and a GraphQL list argument maps onto it with no join step. format=npm,maven stays one unknown value and a 400.
  • Accepting both forms. Rejected: two spellings double the parsing surface and the contract tests for a capability neither consumer asks for.
  • A special case for every defined value listed. Rejected: a client that wants no filter omits the parameter, and the redundant IN costs at most four elements per row.
  • maxItems on the array schema. Rejected: dedup bounds the set at the enumeration's size before the store sees it, and a raw-count cap in the schema would be a promise the handler does not enforce.
  • A compound kind index with a sort column. Rejected: a migration plus an ADR-007 amendment the issue does not ask for, and the measured sparse-kind plan stays within the namespace bound on the existing (namespace_id, kind) index.
  • A neutral pg.Bool(true) arm in the helper behind len > 0 guards at both call sites (the first cut). Replaced after the AppSec review: the helper takes the predicate and returns it unchanged for an empty set, so one site implements the field's nil-or-empty contract, no AND $n::boolean can render, and there is no guard to forget.
  • Chosen: repeated parameter, dedup at both layers, = for one value and IN for several, no new index.

Consequences: a new format or kind value joins the filter through the enumeration alone, and the plan pins need re-measuring if the row-share thresholds move.

Non-goals

  • sort, order, and limit repeat semantics. First value wins, as before. A follow-up can make a repeat a 400.
  • Name and URL search on the list route. Add support by search by name/url on Repository... (#877) • Unassigned.
  • Monolith adoption in S04 (Type token) and S07 (upstream picker). Those specs' plans pick up the repeated form.
  • A total count. Keyset pagination stays total-free.

Database Review Evidence

Migration mode did not run: the diff against origin/main (merge-base 16bc85e39) adds or modifies no file under internal/datastore/migrations/sql/. Query mode ran on internal/datastore/repositories.go, the one changed Go file that dispatches a statement (instrumentQuery). The changed chain is repositoryListClauses, shared by listRepositoriesStmt (RepositoryStore.List) and enumerateRepositoryKeysStmt (RepositoryStore.EnumerateRepositoryKeys), so both statements are rendered at every filter cardinality andRepositoryEnumFilter takes (one value renders =, several render IN), on format and on kind, at the default name sort and the artifacts_count sort. Measured at c1c96638a.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), started with CI's .pg-service-options flags, with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers come from moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Seeds mirror seedExplainRepositories in internal/datastore/repositories_list_integration_test.go: four namespaces of 5,000 active rows each, one per share, with the name cursor at explain-2500, the counter cursor at (50, <id>), and LIMIT 21 (page 20 plus the probe row). Dense seeds (rows without a _Sparse suffix) spread three values evenly, so a two-value set holds 3,334 of 5,000 rows, one value 1,667, and every value 5,000. Sparse seeds give each of the two requested values 50 rows, 100 of 5,000 together. Every plan below ran under default planner settings. Each was re-run with enable_seqscan = off, the setting the branch's plan-shape tests pin under, and all 32 kept the same node, index, Filter, and Sort.

Method Plan node Index Rows (plan / actual) Cost Time Buffers (hit / read) Partitions
datastore.RepositoryStore.List.Formats1_Name Limit repositories_p18_namespace_id_format_name_idx 21 / 21 5.51 0.016ms 4 / 0 1/64
datastore.RepositoryStore.List.Formats1_ArtifactsCount Limit repositories_p18_namespace_id_artifacts_count_id_idx 21 / 21 8.13 0.061ms 64 / 0 1/64
datastore.RepositoryStore.List.Formats2_Name Limit repositories_p18_namespace_id_name_idx1 21 / 21 4.02 0.029ms 3 / 0 1/64
datastore.RepositoryStore.List.Formats2_Name_Sparse Limit repositories_p13_namespace_id_format_name_idx 21 / 21 79.80 0.073ms 51 / 0 1/64
datastore.RepositoryStore.List.Formats2_ArtifactsCount Limit repositories_p18_namespace_id_artifacts_count_id_idx 21 / 21 4.20 0.037ms 32 / 0 1/64
datastore.RepositoryStore.List.Formats2_ArtifactsCount_Sparse Limit repositories_p13_namespace_id_format_name_idx 21 / 21 99.37 0.080ms 53 / 0 1/64
datastore.RepositoryStore.List.Formats4_Name Limit repositories_p18_namespace_id_name_idx1 21 / 21 3.90 0.021ms 3 / 0 1/64
datastore.RepositoryStore.List.Formats4_ArtifactsCount Limit repositories_p18_namespace_id_artifacts_count_id_idx 21 / 21 4.07 0.021ms 23 / 0 1/64
datastore.RepositoryStore.List.Kinds1_Name Limit repositories_p03_namespace_id_name_idx1 21 / 21 7.76 0.023ms 4 / 0 1/64
datastore.RepositoryStore.List.Kinds1_ArtifactsCount Limit repositories_p03_namespace_id_artifacts_count_id_idx 21 / 21 8.13 0.031ms 67 / 0 1/64
datastore.RepositoryStore.List.Kinds2_Name Limit repositories_p03_namespace_id_name_idx1 21 / 21 4.02 0.017ms 3 / 0 1/64
datastore.RepositoryStore.List.Kinds2_Name_Sparse Limit repositories_p09_namespace_id_kind_idx 21 / 21 99.04 0.083ms 53 / 0 1/64
datastore.RepositoryStore.List.Kinds2_ArtifactsCount Limit repositories_p03_namespace_id_artifacts_count_id_idx 21 / 21 4.20 0.023ms 36 / 0 1/64
datastore.RepositoryStore.List.Kinds2_ArtifactsCount_Sparse Limit repositories_p09_namespace_id_kind_idx 21 / 21 99.37 0.069ms 53 / 0 1/64
datastore.RepositoryStore.List.Kinds3_Name Limit repositories_p03_namespace_id_name_idx1 21 / 21 3.87 0.015ms 3 / 0 1/64
datastore.RepositoryStore.List.Kinds3_ArtifactsCount Limit repositories_p03_namespace_id_artifacts_count_id_idx 21 / 21 4.04 0.017ms 23 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats1_Name Limit repositories_p18_namespace_id_format_name_idx 21 / 21 5.51 0.014ms 4 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats1_ArtifactsCount Limit repositories_p18_namespace_id_artifacts_count_id_idx 21 / 21 8.13 0.030ms 64 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_Name Limit repositories_p18_namespace_id_name_idx1 21 / 21 4.02 0.015ms 3 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_Name_Sparse Limit repositories_p13_namespace_id_format_name_idx 21 / 21 79.80 0.053ms 51 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_ArtifactsCount Limit repositories_p18_namespace_id_artifacts_count_id_idx 21 / 21 4.20 0.019ms 32 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_ArtifactsCount_Sparse Limit repositories_p13_namespace_id_format_name_idx 21 / 21 99.37 0.059ms 53 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats4_Name Limit repositories_p18_namespace_id_name_idx1 21 / 21 3.90 0.018ms 3 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Formats4_ArtifactsCount Limit repositories_p18_namespace_id_artifacts_count_id_idx 21 / 21 4.07 0.014ms 23 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds1_Name Limit repositories_p03_namespace_id_name_idx1 21 / 21 7.76 0.018ms 4 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds1_ArtifactsCount Limit repositories_p03_namespace_id_artifacts_count_id_idx 21 / 21 8.13 0.027ms 67 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_Name Limit repositories_p03_namespace_id_name_idx1 21 / 21 4.02 0.015ms 3 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_Name_Sparse Limit repositories_p09_namespace_id_kind_idx 21 / 21 99.04 0.070ms 53 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_ArtifactsCount Limit repositories_p03_namespace_id_artifacts_count_id_idx 21 / 21 4.20 0.026ms 36 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_ArtifactsCount_Sparse Limit repositories_p09_namespace_id_kind_idx 21 / 21 99.37 0.059ms 53 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds3_Name Limit repositories_p03_namespace_id_name_idx1 21 / 21 3.87 0.014ms 3 / 0 1/64
datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds3_ArtifactsCount Limit repositories_p03_namespace_id_artifacts_count_id_idx 21 / 21 4.04 0.016ms 23 / 0 1/64

Query notes:

  • Sparse two-value sets sort (the 8 _Sparse rows). At a 2% share the planner reads the filter column's own index ((namespace_id, format, name) for format, (namespace_id, kind) for kind) and top-N sorts the 49 to 67 matching rows instead of walking the ordered (namespace_id, name) or counter index and discarding 98% of it. This is the shape TestRepositoryStore_List_MultiValueFilterPlanShape pins for the name sort and the S17 Resolutions bullet states. The artifacts_count arm takes the same shape and no test pins it. The Sort's input is bounded by the namespace's active rows that match the filter (ADR-004 caps repositories at 1,000 per format). Not flagged.
  • Dense two-value sets and every-value sets keep the unfiltered page's plan: the ordered index in index order with col = ANY as a post-index Filter and no Sort. The every-value Filter removes zero rows (the line is absent from those plans), so listing every defined value costs one per-row check.
  • Single-value plans are unchanged from main: format on the name sort rides (namespace_id, format, name) with no Filter, and the counter sorts carry the filter as a post-index Filter with no Sort.
  • EnumerateRepositoryKeys takes the same plan as List in every arm, differing only in row width (the projection). Every statement prunes to 1 of 64 repositories partitions through the namespace_id = $1 predicate, no Seq Scan appears, all buffers are shared hits, and LIMIT $n is bound to probeLimit(Limit) with Limit clamped at maxPageSize (100) in internal/managementapi/list.go.
datastore.RepositoryStore.List.Formats1_Name

Summary: Plan matches the unchanged single-value shape: Index Scan over repositories_p18_namespace_id_format_name_idx, the (namespace_id, format, name) index, with format, the namespace, and the name cursor all in the Index Cond, no Filter, and no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format = $2::smallint)) AND (repositories.name > $3::text)
ORDER BY repositories.name ASC
LIMIT $4;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..5.51 rows=21 width=203) (actual time=0.012..0.016 rows=21 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using repositories_p18_namespace_id_format_name_idx on repositories_p18 repositories  (cost=0.28..203.75 rows=818 width=203) (actual time=0.011..0.015 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (format = '2'::smallint) AND (name > 'explain-2500'::text))
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=61
Planning Time: 0.402 ms
Execution Time: 0.032 ms

Timings: planning 0.402ms, execution 0.032ms, total 0.434ms.

datastore.RepositoryStore.List.Formats1_ArtifactsCount

Summary: Plan matches the unchanged single-value shape at a counter sort: Index Scan over repositories_p18_namespace_id_artifacts_count_id_idx with the keyset row bound in the Index Cond and format as a post-index Filter (40 rows removed), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format = $2::smallint)) AND ((repositories.artifacts_count, repositories.id) < ($3, $4::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $5;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.13 rows=21 width=203) (actual time=0.020..0.061 rows=21 loops=1)
  Buffers: shared hit=64
  ->  Index Scan using repositories_p18_namespace_id_artifacts_count_id_idx on repositories_p18 repositories  (cost=0.28..324.16 rows=867 width=203) (actual time=0.019..0.059 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (format = '2'::smallint)
        Rows Removed by Filter: 40
        Buffers: shared hit=64
Planning:
  Buffers: shared hit=10 read=1
Planning Time: 0.437 ms
Execution Time: 0.111 ms

Timings: planning 0.437ms, execution 0.111ms, total 0.548ms.

datastore.RepositoryStore.List.Formats2_Name

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the active (namespace_id, name) index (repositories_p18_namespace_id_name_idx1) in index order with format = ANY as a post-index Filter (11 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.02 rows=21 width=203) (actual time=0.021..0.029 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p18_namespace_id_name_idx1 on repositories_p18 repositories  (cost=0.28..291.73 rows=1636 width=203) (actual time=0.021..0.027 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (name > 'explain-2500'::text))
        Filter: (format = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 11
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=8
Planning Time: 0.348 ms
Execution Time: 0.049 ms

Timings: planning 0.348ms, execution 0.049ms, total 0.397ms.

datastore.RepositoryStore.List.Formats2_Name_Sparse

Summary: Sparse two-value set (2% of the namespace): Index Scan over repositories_p13_namespace_id_format_name_idx, the (namespace_id, format, name) index, with format = ANY inside the Index Cond, then a top-N heapsort on name over the 49 matching rows. The Sort is the cost-based choice the branch pins: the planner rejected the ordered (namespace_id, name) walk because it would discard 98% of rows. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p13. format: docker 4,900, maven 50, npm 50, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=79.75..79.80 rows=21 width=203) (actual time=0.071..0.073 rows=21 loops=1)
  Buffers: shared hit=51
  ->  Sort  (cost=79.75..79.87 rows=49 width=203) (actual time=0.071..0.071 rows=21 loops=1)
        Sort Key: repositories.name
        Sort Method: top-N heapsort  Memory: 29kB
        Buffers: shared hit=51
        ->  Index Scan using repositories_p13_namespace_id_format_name_idx on repositories_p13 repositories  (cost=0.28..78.43 rows=49 width=203) (actual time=0.020..0.035 rows=49 loops=1)
              Index Cond: ((namespace_id = '3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid) AND (format = ANY ('{1,2}'::smallint[])) AND (name > 'explain-2500'::text))
              Buffers: shared hit=51
Planning:
  Buffers: shared hit=67
Planning Time: 0.640 ms
Execution Time: 0.103 ms

Timings: planning 0.640ms, execution 0.103ms, total 0.743ms.

datastore.RepositoryStore.List.Formats2_ArtifactsCount

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p18_namespace_id_artifacts_count_id_idx) in index order with format = ANY as a post-index Filter (8 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.20 rows=21 width=203) (actual time=0.020..0.037 rows=21 loops=1)
  Buffers: shared hit=32
  ->  Index Scan using repositories_p18_namespace_id_artifacts_count_id_idx on repositories_p18 repositories  (cost=0.28..324.16 rows=1734 width=203) (actual time=0.020..0.035 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (format = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 8
        Buffers: shared hit=32
Planning:
  Buffers: shared hit=2
Planning Time: 0.256 ms
Execution Time: 0.064 ms

Timings: planning 0.256ms, execution 0.064ms, total 0.32ms.

datastore.RepositoryStore.List.Formats2_ArtifactsCount_Sparse

Summary: Sparse two-value set (2% of the namespace) at a counter sort: Bitmap Index Scan on repositories_p13_namespace_id_format_name_idx, the (namespace_id, format, name) index, with format = ANY in the Index Cond, the keyset row bound as a heap Filter (33 rows removed), then a top-N heapsort on (artifacts_count DESC, id DESC). Same cost-based trade as the name sort: the ordered counter index exists and the planner rejected it at this share. No test pins this arm. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p13. format: docker 4,900, maven 50, npm 50, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=99.32..99.37 rows=21 width=203) (actual time=0.078..0.080 rows=21 loops=1)
  Buffers: shared hit=53
  ->  Sort  (cost=99.32..99.45 rows=52 width=203) (actual time=0.078..0.079 rows=21 loops=1)
        Sort Key: repositories.artifacts_count DESC, repositories.id DESC
        Sort Method: top-N heapsort  Memory: 29kB
        Buffers: shared hit=53
        ->  Bitmap Heap Scan on repositories_p13 repositories  (cost=9.58..97.92 rows=52 width=203) (actual time=0.023..0.057 rows=67 loops=1)
              Recheck Cond: ((namespace_id = '3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid) AND (format = ANY ('{1,2}'::smallint[])) AND (soft_deleted_at IS NULL))
              Filter: (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid))
              Rows Removed by Filter: 33
              Heap Blocks: exact=51
              Buffers: shared hit=53
              ->  Bitmap Index Scan on repositories_p13_namespace_id_format_name_idx  (cost=0.00..9.56 rows=100 width=0) (actual time=0.014..0.014 rows=100 loops=1)
                    Index Cond: ((namespace_id = '3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid) AND (format = ANY ('{1,2}'::smallint[])))
                    Buffers: shared hit=2
Planning:
  Buffers: shared hit=11
Planning Time: 0.255 ms
Execution Time: 0.122 ms

Timings: planning 0.255ms, execution 0.122ms, total 0.377ms.

datastore.RepositoryStore.List.Formats4_Name

Summary: Every defined format value listed: the planner walks the active (namespace_id, name) index (repositories_p18_namespace_id_name_idx1) in index order and applies format = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint, $4::smallint, $5::smallint))) AND (repositories.name > $6::text)
ORDER BY repositories.name ASC
LIMIT $7;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 0, 1, 2, 3, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..3.90 rows=21 width=203) (actual time=0.017..0.021 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p18_namespace_id_name_idx1 on repositories_p18 repositories  (cost=0.28..297.86 rows=1726 width=203) (actual time=0.016..0.020 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (name > 'explain-2500'::text))
        Filter: (format = ANY ('{0,1,2,3}'::smallint[]))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=2
Planning Time: 0.267 ms
Execution Time: 0.044 ms

Timings: planning 0.267ms, execution 0.044ms, total 0.311ms.

datastore.RepositoryStore.List.Formats4_ArtifactsCount

Summary: Every defined format value listed: the planner walks the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p18_namespace_id_artifacts_count_id_idx) in index order and applies format = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint, $4::smallint, $5::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($6, $7::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $8;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 0, 1, 2, 3, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.07 rows=21 width=203) (actual time=0.010..0.021 rows=21 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using repositories_p18_namespace_id_artifacts_count_id_idx on repositories_p18 repositories  (cost=0.28..330.66 rows=1830 width=203) (actual time=0.010..0.019 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (format = ANY ('{0,1,2,3}'::smallint[]))
        Buffers: shared hit=23
Planning:
  Buffers: shared hit=2
Planning Time: 0.190 ms
Execution Time: 0.034 ms

Timings: planning 0.190ms, execution 0.034ms, total 0.224ms.

datastore.RepositoryStore.List.Kinds1_Name

Summary: Plan matches the unchanged single-value shape for kind, which has no index with a sort column: Index Scan over repositories_p03_namespace_id_name_idx1, the active (namespace_id, name) index, with kind as a post-index Filter (42 rows removed), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind = $2::smallint)) AND (repositories.name > $3::text)
ORDER BY repositories.name ASC
LIMIT $4;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..7.76 rows=21 width=203) (actual time=0.015..0.023 rows=21 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using repositories_p03_namespace_id_name_idx1 on repositories_p03 repositories  (cost=0.28..291.73 rows=818 width=203) (actual time=0.015..0.021 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (name > 'explain-2500'::text))
        Filter: (kind = '2'::smallint)
        Rows Removed by Filter: 42
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=69
Planning Time: 0.358 ms
Execution Time: 0.036 ms

Timings: planning 0.358ms, execution 0.036ms, total 0.394ms.

datastore.RepositoryStore.List.Kinds1_ArtifactsCount

Summary: Plan matches the unchanged single-value shape at a counter sort: Index Scan over repositories_p03_namespace_id_artifacts_count_id_idx with the keyset row bound in the Index Cond and kind as a post-index Filter (43 rows removed), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind = $2::smallint)) AND ((repositories.artifacts_count, repositories.id) < ($3, $4::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $5;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.13 rows=21 width=203) (actual time=0.012..0.031 rows=21 loops=1)
  Buffers: shared hit=67
  ->  Index Scan using repositories_p03_namespace_id_artifacts_count_id_idx on repositories_p03 repositories  (cost=0.28..324.16 rows=867 width=203) (actual time=0.011..0.029 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (kind = '2'::smallint)
        Rows Removed by Filter: 43
        Buffers: shared hit=67
Planning:
  Buffers: shared hit=10
Planning Time: 0.257 ms
Execution Time: 0.054 ms

Timings: planning 0.257ms, execution 0.054ms, total 0.311ms.

datastore.RepositoryStore.List.Kinds2_Name

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the active (namespace_id, name) index (repositories_p03_namespace_id_name_idx1) in index order with kind = ANY as a post-index Filter (11 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.02 rows=21 width=203) (actual time=0.011..0.017 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p03_namespace_id_name_idx1 on repositories_p03 repositories  (cost=0.28..291.73 rows=1636 width=203) (actual time=0.011..0.015 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (name > 'explain-2500'::text))
        Filter: (kind = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 11
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=7
Planning Time: 0.210 ms
Execution Time: 0.029 ms

Timings: planning 0.210ms, execution 0.029ms, total 0.239ms.

datastore.RepositoryStore.List.Kinds2_Name_Sparse

Summary: Sparse two-value set (2% of the namespace): Bitmap Index Scan on repositories_p09_namespace_id_kind_idx, the (namespace_id, kind) index, with kind = ANY in the Index Cond, the name cursor as a heap Filter (51 rows removed), then a top-N heapsort on name. The Sort is the cost-based choice the branch pins for kind, which has no index carrying a sort column. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p09. kind: hosted 4,900, virtual 50, remote 50. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=98.99..99.04 rows=21 width=203) (actual time=0.081..0.083 rows=21 loops=1)
  Buffers: shared hit=53
  ->  Sort  (cost=98.99..99.11 rows=49 width=203) (actual time=0.080..0.081 rows=21 loops=1)
        Sort Key: repositories.name
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=53
        ->  Bitmap Heap Scan on repositories_p09 repositories  (cost=9.58..97.66 rows=49 width=203) (actual time=0.039..0.055 rows=49 loops=1)
              Recheck Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])) AND (soft_deleted_at IS NULL))
              Filter: (name > 'explain-2500'::text)
              Rows Removed by Filter: 51
              Heap Blocks: exact=51
              Buffers: shared hit=53
              ->  Bitmap Index Scan on repositories_p09_namespace_id_kind_idx  (cost=0.00..9.56 rows=100 width=0) (actual time=0.016..0.016 rows=100 loops=1)
                    Index Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])))
                    Buffers: shared hit=2
Planning:
  Buffers: shared hit=75
Planning Time: 0.361 ms
Execution Time: 0.111 ms

Timings: planning 0.361ms, execution 0.111ms, total 0.472ms.

datastore.RepositoryStore.List.Kinds2_ArtifactsCount

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p03_namespace_id_artifacts_count_id_idx) in index order with kind = ANY as a post-index Filter (12 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.20 rows=21 width=203) (actual time=0.010..0.023 rows=21 loops=1)
  Buffers: shared hit=36
  ->  Index Scan using repositories_p03_namespace_id_artifacts_count_id_idx on repositories_p03 repositories  (cost=0.28..324.16 rows=1734 width=203) (actual time=0.009..0.021 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (kind = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 12
        Buffers: shared hit=36
Planning:
  Buffers: shared hit=1
Planning Time: 0.186 ms
Execution Time: 0.036 ms

Timings: planning 0.186ms, execution 0.036ms, total 0.222ms.

datastore.RepositoryStore.List.Kinds2_ArtifactsCount_Sparse

Summary: Sparse two-value set (2% of the namespace) at a counter sort: Bitmap Index Scan on repositories_p09_namespace_id_kind_idx, the (namespace_id, kind) index, with kind = ANY in the Index Cond, the keyset row bound as a heap Filter (33 rows removed), then a top-N heapsort on (artifacts_count DESC, id DESC). Same cost-based trade as the name sort: the ordered counter index exists and the planner rejected it at this share. No test pins this arm. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p09. kind: hosted 4,900, virtual 50, remote 50. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=99.32..99.37 rows=21 width=203) (actual time=0.068..0.069 rows=21 loops=1)
  Buffers: shared hit=53
  ->  Sort  (cost=99.32..99.45 rows=52 width=203) (actual time=0.067..0.068 rows=21 loops=1)
        Sort Key: repositories.artifacts_count DESC, repositories.id DESC
        Sort Method: top-N heapsort  Memory: 29kB
        Buffers: shared hit=53
        ->  Bitmap Heap Scan on repositories_p09 repositories  (cost=9.58..97.92 rows=52 width=203) (actual time=0.022..0.048 rows=67 loops=1)
              Recheck Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])) AND (soft_deleted_at IS NULL))
              Filter: (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid))
              Rows Removed by Filter: 33
              Heap Blocks: exact=51
              Buffers: shared hit=53
              ->  Bitmap Index Scan on repositories_p09_namespace_id_kind_idx  (cost=0.00..9.56 rows=100 width=0) (actual time=0.015..0.015 rows=100 loops=1)
                    Index Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])))
                    Buffers: shared hit=2
Planning:
  Buffers: shared hit=10
Planning Time: 0.224 ms
Execution Time: 0.102 ms

Timings: planning 0.224ms, execution 0.102ms, total 0.326ms.

datastore.RepositoryStore.List.Kinds3_Name

Summary: Every defined kind value listed: the planner walks the active (namespace_id, name) index (repositories_p03_namespace_id_name_idx1) in index order and applies kind = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint, $4::smallint))) AND (repositories.name > $5::text)
ORDER BY repositories.name ASC
LIMIT $6;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 0, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..3.87 rows=21 width=203) (actual time=0.011..0.015 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p03_namespace_id_name_idx1 on repositories_p03 repositories  (cost=0.28..294.79 rows=1726 width=203) (actual time=0.010..0.014 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (name > 'explain-2500'::text))
        Filter: (kind = ANY ('{0,1,2}'::smallint[]))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=1
Planning Time: 0.171 ms
Execution Time: 0.027 ms

Timings: planning 0.171ms, execution 0.027ms, total 0.198ms.

datastore.RepositoryStore.List.Kinds3_ArtifactsCount

Summary: Every defined kind value listed: the planner walks the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p03_namespace_id_artifacts_count_id_idx) in index order and applies kind = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.namespace_id AS "repositories.namespace_id",
     repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count",
     repositories.downloads_count AS "repositories.downloads_count",
     repositories.size_bytes AS "repositories.size_bytes",
     repositories.created_at AS "repositories.created_at",
     repositories.last_updated_at AS "repositories.last_updated_at",
     repositories.soft_deleted_at AS "repositories.soft_deleted_at",
     repositories.format AS "repositories.format",
     repositories.kind AS "repositories.kind",
     repositories.visibility AS "repositories.visibility",
     repositories.name AS "repositories.name",
     repositories.description AS "repositories.description",
     repositories.gitlab_created_by_user_id AS "repositories.gitlab_created_by_user_id",
     repositories.gitlab_last_updated_by_user_id AS "repositories.gitlab_last_updated_by_user_id",
     repositories.last_reconciled_at AS "repositories.last_reconciled_at"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint, $4::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($5, $6::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $7;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 0, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.04 rows=21 width=203) (actual time=0.009..0.017 rows=21 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using repositories_p03_namespace_id_artifacts_count_id_idx on repositories_p03 repositories  (cost=0.28..327.41 rows=1830 width=203) (actual time=0.009..0.016 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (kind = ANY ('{0,1,2}'::smallint[]))
        Buffers: shared hit=23
Planning:
  Buffers: shared hit=1
Planning Time: 0.157 ms
Execution Time: 0.030 ms

Timings: planning 0.157ms, execution 0.030ms, total 0.187ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats1_Name

Summary: Plan matches the unchanged single-value shape: Index Scan over repositories_p18_namespace_id_format_name_idx, the (namespace_id, format, name) index, with format, the namespace, and the name cursor all in the Index Cond, no Filter, and no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format = $2::smallint)) AND (repositories.name > $3::text)
ORDER BY repositories.name ASC
LIMIT $4;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..5.51 rows=21 width=29) (actual time=0.010..0.014 rows=21 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using repositories_p18_namespace_id_format_name_idx on repositories_p18 repositories  (cost=0.28..203.75 rows=818 width=29) (actual time=0.010..0.013 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (format = '2'::smallint) AND (name > 'explain-2500'::text))
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=2
Planning Time: 0.140 ms
Execution Time: 0.024 ms

Timings: planning 0.140ms, execution 0.024ms, total 0.164ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats1_ArtifactsCount

Summary: Plan matches the unchanged single-value shape at a counter sort: Index Scan over repositories_p18_namespace_id_artifacts_count_id_idx with the keyset row bound in the Index Cond and format as a post-index Filter (40 rows removed), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format = $2::smallint)) AND ((repositories.artifacts_count, repositories.id) < ($3, $4::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $5;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.13 rows=21 width=24) (actual time=0.012..0.030 rows=21 loops=1)
  Buffers: shared hit=64
  ->  Index Scan using repositories_p18_namespace_id_artifacts_count_id_idx on repositories_p18 repositories  (cost=0.28..324.16 rows=867 width=24) (actual time=0.012..0.029 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (format = '2'::smallint)
        Rows Removed by Filter: 40
        Buffers: shared hit=64
Planning:
  Buffers: shared hit=2
Planning Time: 0.130 ms
Execution Time: 0.041 ms

Timings: planning 0.130ms, execution 0.041ms, total 0.171ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_Name

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the active (namespace_id, name) index (repositories_p18_namespace_id_name_idx1) in index order with format = ANY as a post-index Filter (11 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.02 rows=21 width=29) (actual time=0.010..0.015 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p18_namespace_id_name_idx1 on repositories_p18 repositories  (cost=0.28..291.73 rows=1636 width=29) (actual time=0.010..0.014 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (name > 'explain-2500'::text))
        Filter: (format = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 11
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=2
Planning Time: 0.130 ms
Execution Time: 0.025 ms

Timings: planning 0.130ms, execution 0.025ms, total 0.155ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_Name_Sparse

Summary: Sparse two-value set (2% of the namespace): Index Scan over repositories_p13_namespace_id_format_name_idx, the (namespace_id, format, name) index, with format = ANY inside the Index Cond, then a top-N heapsort on name over the 49 matching rows. The Sort is the cost-based choice the branch pins: the planner rejected the ordered (namespace_id, name) walk because it would discard 98% of rows. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p13. format: docker 4,900, maven 50, npm 50, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=79.75..79.80 rows=21 width=29) (actual time=0.051..0.053 rows=21 loops=1)
  Buffers: shared hit=51
  ->  Sort  (cost=79.75..79.87 rows=49 width=29) (actual time=0.051..0.052 rows=21 loops=1)
        Sort Key: repositories.name
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=51
        ->  Index Scan using repositories_p13_namespace_id_format_name_idx on repositories_p13 repositories  (cost=0.28..78.43 rows=49 width=29) (actual time=0.014..0.025 rows=49 loops=1)
              Index Cond: ((namespace_id = '3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid) AND (format = ANY ('{1,2}'::smallint[])) AND (name > 'explain-2500'::text))
              Buffers: shared hit=51
Planning:
  Buffers: shared hit=2
Planning Time: 0.175 ms
Execution Time: 0.068 ms

Timings: planning 0.175ms, execution 0.068ms, total 0.243ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_ArtifactsCount

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p18_namespace_id_artifacts_count_id_idx) in index order with format = ANY as a post-index Filter (8 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.20 rows=21 width=24) (actual time=0.009..0.019 rows=21 loops=1)
  Buffers: shared hit=32
  ->  Index Scan using repositories_p18_namespace_id_artifacts_count_id_idx on repositories_p18 repositories  (cost=0.28..324.16 rows=1734 width=24) (actual time=0.009..0.017 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (format = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 8
        Buffers: shared hit=32
Planning:
  Buffers: shared hit=2
Planning Time: 0.156 ms
Execution Time: 0.028 ms

Timings: planning 0.156ms, execution 0.028ms, total 0.184ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats2_ArtifactsCount_Sparse

Summary: Sparse two-value set (2% of the namespace) at a counter sort: Bitmap Index Scan on repositories_p13_namespace_id_format_name_idx, the (namespace_id, format, name) index, with format = ANY in the Index Cond, the keyset row bound as a heap Filter (33 rows removed), then a top-N heapsort on (artifacts_count DESC, id DESC). Same cost-based trade as the name sort: the ordered counter index exists and the planner rejected it at this share. No test pins this arm. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p13. format: docker 4,900, maven 50, npm 50, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=99.32..99.37 rows=21 width=24) (actual time=0.057..0.059 rows=21 loops=1)
  Buffers: shared hit=53
  ->  Sort  (cost=99.32..99.45 rows=52 width=24) (actual time=0.057..0.058 rows=21 loops=1)
        Sort Key: repositories.artifacts_count DESC, repositories.id DESC
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=53
        ->  Bitmap Heap Scan on repositories_p13 repositories  (cost=9.58..97.92 rows=52 width=24) (actual time=0.022..0.044 rows=67 loops=1)
              Recheck Cond: ((namespace_id = '3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid) AND (format = ANY ('{1,2}'::smallint[])) AND (soft_deleted_at IS NULL))
              Filter: (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid))
              Rows Removed by Filter: 33
              Heap Blocks: exact=51
              Buffers: shared hit=53
              ->  Bitmap Index Scan on repositories_p13_namespace_id_format_name_idx  (cost=0.00..9.56 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                    Index Cond: ((namespace_id = '3bc77bee-468e-75ba-a49b-4df4d5e0519e'::uuid) AND (format = ANY ('{1,2}'::smallint[])))
                    Buffers: shared hit=2
Planning:
  Buffers: shared hit=2
Planning Time: 0.187 ms
Execution Time: 0.084 ms

Timings: planning 0.187ms, execution 0.084ms, total 0.271ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats4_Name

Summary: Every defined format value listed: the planner walks the active (namespace_id, name) index (repositories_p18_namespace_id_name_idx1) in index order and applies format = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint, $4::smallint, $5::smallint))) AND (repositories.name > $6::text)
ORDER BY repositories.name ASC
LIMIT $7;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 0, 1, 2, 3, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..3.90 rows=21 width=29) (actual time=0.014..0.018 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p18_namespace_id_name_idx1 on repositories_p18 repositories  (cost=0.28..297.86 rows=1726 width=29) (actual time=0.013..0.017 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (name > 'explain-2500'::text))
        Filter: (format = ANY ('{0,1,2,3}'::smallint[]))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=2
Planning Time: 0.139 ms
Execution Time: 0.026 ms

Timings: planning 0.139ms, execution 0.026ms, total 0.165ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Formats4_ArtifactsCount

Summary: Every defined format value listed: the planner walks the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p18_namespace_id_artifacts_count_id_idx) in index order and applies format = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p18. format: docker 1,666, maven 1,667, npm 1,667, oci 0. kind: hosted 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.format IN ($2::smallint, $3::smallint, $4::smallint, $5::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($6, $7::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $8;

Bound args: ['3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid, 0, 1, 2, 3, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.07 rows=21 width=24) (actual time=0.007..0.014 rows=21 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using repositories_p18_namespace_id_artifacts_count_id_idx on repositories_p18 repositories  (cost=0.28..330.66 rows=1830 width=24) (actual time=0.007..0.012 rows=21 loops=1)
        Index Cond: ((namespace_id = '3347fbce-ae77-7f2b-94d5-c2751d23a3ae'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (format = ANY ('{0,1,2,3}'::smallint[]))
        Buffers: shared hit=23
Planning:
  Buffers: shared hit=2
Planning Time: 0.135 ms
Execution Time: 0.022 ms

Timings: planning 0.135ms, execution 0.022ms, total 0.157ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds1_Name

Summary: Plan matches the unchanged single-value shape for kind, which has no index with a sort column: Index Scan over repositories_p03_namespace_id_name_idx1, the active (namespace_id, name) index, with kind as a post-index Filter (42 rows removed), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind = $2::smallint)) AND (repositories.name > $3::text)
ORDER BY repositories.name ASC
LIMIT $4;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..7.76 rows=21 width=29) (actual time=0.012..0.018 rows=21 loops=1)
  Buffers: shared hit=4
  ->  Index Scan using repositories_p03_namespace_id_name_idx1 on repositories_p03 repositories  (cost=0.28..291.73 rows=818 width=29) (actual time=0.011..0.017 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (name > 'explain-2500'::text))
        Filter: (kind = '2'::smallint)
        Rows Removed by Filter: 42
        Buffers: shared hit=4
Planning:
  Buffers: shared hit=1
Planning Time: 0.140 ms
Execution Time: 0.029 ms

Timings: planning 0.140ms, execution 0.029ms, total 0.169ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds1_ArtifactsCount

Summary: Plan matches the unchanged single-value shape at a counter sort: Index Scan over repositories_p03_namespace_id_artifacts_count_id_idx with the keyset row bound in the Index Cond and kind as a post-index Filter (43 rows removed), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind = $2::smallint)) AND ((repositories.artifacts_count, repositories.id) < ($3, $4::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $5;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.13 rows=21 width=24) (actual time=0.010..0.027 rows=21 loops=1)
  Buffers: shared hit=67
  ->  Index Scan using repositories_p03_namespace_id_artifacts_count_id_idx on repositories_p03 repositories  (cost=0.28..324.16 rows=867 width=24) (actual time=0.009..0.026 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (kind = '2'::smallint)
        Rows Removed by Filter: 43
        Buffers: shared hit=67
Planning:
  Buffers: shared hit=1
Planning Time: 0.146 ms
Execution Time: 0.038 ms

Timings: planning 0.146ms, execution 0.038ms, total 0.184ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_Name

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the active (namespace_id, name) index (repositories_p03_namespace_id_name_idx1) in index order with kind = ANY as a post-index Filter (11 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.02 rows=21 width=29) (actual time=0.010..0.015 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p03_namespace_id_name_idx1 on repositories_p03 repositories  (cost=0.28..291.73 rows=1636 width=29) (actual time=0.010..0.014 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (name > 'explain-2500'::text))
        Filter: (kind = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 11
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=1
Planning Time: 0.135 ms
Execution Time: 0.024 ms

Timings: planning 0.135ms, execution 0.024ms, total 0.159ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_Name_Sparse

Summary: Sparse two-value set (2% of the namespace): Bitmap Index Scan on repositories_p09_namespace_id_kind_idx, the (namespace_id, kind) index, with kind = ANY in the Index Cond, the name cursor as a heap Filter (51 rows removed), then a top-N heapsort on name. The Sort is the cost-based choice the branch pins for kind, which has no index carrying a sort column. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p09. kind: hosted 4,900, virtual 50, remote 50. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND (repositories.name > $4::text)
ORDER BY repositories.name ASC
LIMIT $5;

Bound args: ['e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=98.99..99.04 rows=21 width=29) (actual time=0.068..0.070 rows=21 loops=1)
  Buffers: shared hit=53
  ->  Sort  (cost=98.99..99.11 rows=49 width=29) (actual time=0.068..0.068 rows=21 loops=1)
        Sort Key: repositories.name
        Sort Method: top-N heapsort  Memory: 26kB
        Buffers: shared hit=53
        ->  Bitmap Heap Scan on repositories_p09 repositories  (cost=9.58..97.66 rows=49 width=29) (actual time=0.033..0.047 rows=49 loops=1)
              Recheck Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])) AND (soft_deleted_at IS NULL))
              Filter: (name > 'explain-2500'::text)
              Rows Removed by Filter: 51
              Heap Blocks: exact=51
              Buffers: shared hit=53
              ->  Bitmap Index Scan on repositories_p09_namespace_id_kind_idx  (cost=0.00..9.56 rows=100 width=0) (actual time=0.012..0.012 rows=100 loops=1)
                    Index Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])))
                    Buffers: shared hit=2
Planning:
  Buffers: shared hit=1
Planning Time: 0.165 ms
Execution Time: 0.091 ms

Timings: planning 0.165ms, execution 0.091ms, total 0.256ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_ArtifactsCount

Summary: Dense two-value set (two thirds of the namespace): Index Scan over the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p03_namespace_id_artifacts_count_id_idx) in index order with kind = ANY as a post-index Filter (12 rows removed before the 21-row page filled), no Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.20 rows=21 width=24) (actual time=0.012..0.026 rows=21 loops=1)
  Buffers: shared hit=36
  ->  Index Scan using repositories_p03_namespace_id_artifacts_count_id_idx on repositories_p03 repositories  (cost=0.28..324.16 rows=1734 width=24) (actual time=0.012..0.025 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (kind = ANY ('{1,2}'::smallint[]))
        Rows Removed by Filter: 12
        Buffers: shared hit=36
Planning:
  Buffers: shared hit=1
Planning Time: 0.175 ms
Execution Time: 0.042 ms

Timings: planning 0.175ms, execution 0.042ms, total 0.217ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds2_ArtifactsCount_Sparse

Summary: Sparse two-value set (2% of the namespace) at a counter sort: Bitmap Index Scan on repositories_p09_namespace_id_kind_idx, the (namespace_id, kind) index, with kind = ANY in the Index Cond, the keyset row bound as a heap Filter (33 rows removed), then a top-N heapsort on (artifacts_count DESC, id DESC). Same cost-based trade as the name sort: the ordered counter index exists and the planner rejected it at this share. No test pins this arm. Its input is bounded by the namespace's active rows matching the filter. Prunes to 1 of 64 partitions.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p09. kind: hosted 4,900, virtual 50, remote 50. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($4, $5::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $6;

Bound args: ['e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=99.32..99.37 rows=21 width=24) (actual time=0.057..0.059 rows=21 loops=1)
  Buffers: shared hit=53
  ->  Sort  (cost=99.32..99.45 rows=52 width=24) (actual time=0.057..0.058 rows=21 loops=1)
        Sort Key: repositories.artifacts_count DESC, repositories.id DESC
        Sort Method: top-N heapsort  Memory: 27kB
        Buffers: shared hit=53
        ->  Bitmap Heap Scan on repositories_p09 repositories  (cost=9.58..97.92 rows=52 width=24) (actual time=0.022..0.044 rows=67 loops=1)
              Recheck Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])) AND (soft_deleted_at IS NULL))
              Filter: (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid))
              Rows Removed by Filter: 33
              Heap Blocks: exact=51
              Buffers: shared hit=53
              ->  Bitmap Index Scan on repositories_p09_namespace_id_kind_idx  (cost=0.00..9.56 rows=100 width=0) (actual time=0.013..0.013 rows=100 loops=1)
                    Index Cond: ((namespace_id = 'e442bbd4-ec69-7154-b74f-b98f5a042b0d'::uuid) AND (kind = ANY ('{1,2}'::smallint[])))
                    Buffers: shared hit=2
Planning:
  Buffers: shared hit=1
Planning Time: 0.173 ms
Execution Time: 0.081 ms

Timings: planning 0.173ms, execution 0.081ms, total 0.254ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds3_Name

Summary: Every defined kind value listed: the planner walks the active (namespace_id, name) index (repositories_p03_namespace_id_name_idx1) in index order and applies kind = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.name AS "repositories.name"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint, $4::smallint))) AND (repositories.name > $5::text)
ORDER BY repositories.name ASC
LIMIT $6;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 0, 1, 2, 'explain-2500', 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..3.87 rows=21 width=29) (actual time=0.010..0.014 rows=21 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using repositories_p03_namespace_id_name_idx1 on repositories_p03 repositories  (cost=0.28..294.79 rows=1726 width=29) (actual time=0.010..0.013 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (name > 'explain-2500'::text))
        Filter: (kind = ANY ('{0,1,2}'::smallint[]))
        Buffers: shared hit=3
Planning:
  Buffers: shared hit=1
Planning Time: 0.140 ms
Execution Time: 0.025 ms

Timings: planning 0.140ms, execution 0.025ms, total 0.165ms.

datastore.RepositoryStore.EnumerateRepositoryKeys.Kinds3_ArtifactsCount

Summary: Every defined kind value listed: the planner walks the (namespace_id, artifacts_count DESC, id DESC) index (repositories_p03_namespace_id_artifacts_count_id_idx) in index order and applies kind = ANY as a Filter that removes no row, the same scan the unfiltered page takes. No Sort. Prunes to 1 of 64 partitions. No anomalies.

Seed shape: namespaces=1, repositories=5000 in partitions.repositories_p03. kind: hosted 1,666, virtual 1,667, remote 1,667. format: npm 5,000. Rows named explain-0000 through explain-4999, artifacts_count = g % 97, soft_deleted_at null on every row.

Rendered SQL:

SELECT repositories.id AS "repositories.id",
     repositories.artifacts_count AS "repositories.artifacts_count"
FROM public.repositories
WHERE (((repositories.namespace_id = $1::uuid) AND (repositories.soft_deleted_at IS NULL)) AND (repositories.kind IN ($2::smallint, $3::smallint, $4::smallint))) AND ((repositories.artifacts_count, repositories.id) < ($5, $6::uuid))
ORDER BY repositories.artifacts_count DESC, repositories.id DESC
LIMIT $7;

Bound args: ['9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid, 0, 1, 2, 50, '01991234-5678-7abc-8def-000000000001'::uuid, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..4.04 rows=21 width=24) (actual time=0.008..0.016 rows=21 loops=1)
  Buffers: shared hit=23
  ->  Index Scan using repositories_p03_namespace_id_artifacts_count_id_idx on repositories_p03 repositories  (cost=0.28..327.41 rows=1830 width=24) (actual time=0.008..0.015 rows=21 loops=1)
        Index Cond: ((namespace_id = '9dd2e36a-97cc-790e-8daa-89a2872bb7ee'::uuid) AND (ROW(artifacts_count, id) < ROW('50'::bigint, '01991234-5678-7abc-8def-000000000001'::uuid)))
        Filter: (kind = ANY ('{0,1,2}'::smallint[]))
        Buffers: shared hit=23
Planning:
  Buffers: shared hit=1
Planning Time: 0.145 ms
Execution Time: 0.028 ms

Timings: planning 0.145ms, execution 0.028ms, total 0.173ms.

Edited by Hayley Swimelar

Merge request reports