Why Does My Search Get Slower as the Collection Grows, Even With a Narrow Filter?

Why does my search get slower as the collection grows, even with a narrow filter?

Some step of the query is still reading the whole field instead of the rows the filter kept. A filter that matches 500 rows should cost about the same in a collection of 1 million rows as in one of 10 million. When the time grows with the collection while the number of matches stays flat, the usual cause is a facet count, a sort or a keyword clause that walks an index built over every row of a field and checks each entry against the filter. The fix is to start from the filter's matches whenever they are the smaller set, and count, sort and test words over those rows only.

Which parts of a query grow with the collection?

The slow path is the right choice when the filter is broad. If a filter keeps 60% of a collection, walking the index once is cheaper than reading millions of matches one at a time. So a query planner has to estimate how many rows the filter keeps and pick a path, and the slowdown appears when that estimate is missing or wrong.

Why does the database pick the slow path?

The selective clause is hidden. APIs often wrap the caller's filter inside an AND group together

with a tenant or permission scope. A planner that reads only the top-level clauses never sees the narrow one and drives from a broad clause instead.

Sorting with a limit. For ORDER BY created_at LIMIT 50 a planner may walk the created_at

index and filter as it goes. That is fast when most rows pass and slow when few do, because it may walk most of the index to find 50. In PostgreSQL's [EXPLAIN ANALYZE](https://www.postgresql.org/docs/current/using-explain.html) this shows as an index scan on the sort column with a Filter line and a large "Rows Removed by Filter" count.

Facets on fields with many values. Counting a field such as asset_id with hundreds of thousands

of distinct values by visiting each value's list is expensive however few rows matched. Elasticsearch's terms aggregation exposes this choice as [execution_hint](https://www.elastic.co/docs/reference/aggregations/search-aggregations-bucket-terms-aggregation): the default global ordinals path suits broad queries, and Elastic's documentation says map, which buckets the matching documents' own values, should only be considered when very few documents match.

Long lists of values. A clause like "folder is one of these 300 IDs" can pass a planner's limit for

using the index and fall back to a scan.

How do I find the slow step?

1. Time the query with each part removed in turn: no facet, no sort, no keyword clause. 2. Run the same filter against collections of different sizes. If the time grows with the collection while the match count stays the same, one step is walking the field. 3. Read the plan: EXPLAIN ANALYZE in PostgreSQL, or "profile": true in Elasticsearch and OpenSearch (search profile API). 4. Move the narrow clause to the top level of the filter and time it again. If that fixes it, the planner could not see the clause.

How do I fix it?

Let the planner see the narrow clause. Flatten nested AND groups where you control the query, and

keep table statistics current.

Give the sort an index that starts with the filter. In PostgreSQL a composite index on

(tenant_id, created_at) returns one tenant's rows already in date order, so the sort reads only matching rows.

Count facets from the matches when the filter is narrow. In Elasticsearch that is

execution_hint: map on the terms aggregation for those queries.

Precompute what does not need to be fresh. Counts shown as "about 12,000" can come from a cache or a

nightly job.

Check the answers, too. A faster path has to return the same rows, the same counts and the same

order. Compare a sample of queries before and after.

What did this look like in our own vector store?

MVS, the vector store behind Mixpeek, had this problem on filtered facet counts, sorted pages and keyword search inside a filter: each walked the field's index, so they slowed down as a collection grew even when the filter matched a few hundred rows. It now reads the filter's matches first, including clauses nested inside AND groups, and counts, sorts and tests words over those rows when a cost rule says that is cheaper. Filters that keep most of a collection still take the old path.



Every answer matched the old path exactly. The change is in the changelog for 7 November 2026. MVS keeps vectors and payloads on object storage, S3 or GCS, and serves filtered vector search, keyword search and facets from the same index.

Related: why vector search returns fewer results when you add a filter and filtered vector search.

Frequently Asked Questions

Why is my facet count slow when the filter matches only a few rows?

The count is probably walking every value of the field and checking each against the filter, which costs the same however few rows matched. Count from the matches instead, or in Elasticsearch set execution_hint to map for narrow queries.

Why is ORDER BY with LIMIT slow on a filtered query?

The planner is likely walking the sort column's index and discarding rows that fail the filter until it has enough. With a narrow filter that means reading most of the index. An index that starts with the filter column and ends with the sort column fixes it in PostgreSQL.

How do I know whether my search will slow down as data grows?

Run the same filtered query on a copy of your data at two or three sizes. If the time rises with the collection while the number of matches stays flat, it will keep rising.

Does the faster path change the results?

It should not, and you should check. Compare rows, counts and order for a sample of real queries on both paths before you switch. In our benchmark every answer matched.