In LanceDB, what does prefilter=True on a search's where clause change?

middleshould knowfreq 50%

answer

Order of operations, filter versus search
Post-filtering throws away the top-k
Selective filters can return almost nothing
Scalar indexes make the predicate cheap
Do not trust the default across versions

basics

~20 s

With prefilter=True the SQL predicate is applied before the nearest-neighbour search, so the search only considers matching rows and you get a full limit of results. Post-filtering instead applies the predicate to rows the vector search already returned, which can leave you with far fewer.

solid answer

~40 s

.where("category = 'docs' AND price < 100", prefilter=True) narrows the candidate set first, then searches for neighbours within it. Post-filtering does the opposite: it takes the approximate top-k the index produced and discards the rows that fail the predicate, so a selective filter can shrink ten results to two or zero — the classic "my filtered search returns nothing" bug. Prefiltering avoids that but changes the cost model: the engine must evaluate the predicate over the table, and a highly selective filter can push the query toward scanning the surviving rows rather than using the vector index. Building a scalar index on the filtered column with create_scalar_index is what keeps that evaluation cheap. Pass prefilter explicitly rather than relying on the default, which has not been the same across client versions.

`.where("category = 'docs' AND price < 100", prefilter=True)` narrows the candidate set first, then searches for neighbours within it. Post-filtering does the opposite: it takes the approximate top-k the index produced and discards the rows that fail the predicate, so a selective filter can shrink ten results to two or zero — the classic "my filtered search returns nothing" bug. Prefiltering avoids that but changes the cost model: the engine must evaluate the predicate over the table, and a highly selective filter can push the query toward scanning the surviving rows rather than using the vector index. Building a scalar index on the filtered column with `create_scalar_index` is what keeps that evaluation cheap. Pass `prefilter` explicitly rather than relying on the default, which has not been the same across client versions.

code

python · 9 lines

python

# make the filtered column cheap to evaluate
tbl.create_scalar_index("category")

rows = (
    tbl.search(query_vec)
    .where("category = 'docs' AND price < 100", prefilter=True)
    .limit(10)
    .to_pandas()
)

go deeper

for a junior

Know that where() takes a SQL-style predicate string and that prefilter=True makes the filter run before the vector search rather than after it.

for a middle

Explain why post-filtering under-fills the result set: the top-k is chosen over the whole table first, so a selective predicate deletes most of it. Know that prefiltering costs predicate evaluation in exchange.

for a senior

Choose per query based on measured selectivity, add scalar indexes on hot filter columns, and treat short filtered result sets as a correctness bug to investigate rather than a quirk to work around with a bigger limit.

for a principal

Standardize it. Tenant-scoped or permission-scoped filters must be prefiltered as a rule, since a post-filtered tenant query silently degrades to near-empty results, and make the predicate-construction path safe against injected input across all services.

The two orderings

Every vector database that supports metadata filtering has to decide when the predicate runs relative to the approximate search, and LanceDB exposes that decision directly on the query.

Post-filter (the naive ordering): run the ANN search for k neighbours, then drop rows failing the predicate. Cheap, because the predicate touches only k rows. Its flaw is arithmetic: if 5% of your table matches the filter, then of ten unfiltered neighbours you expect about half a row to survive. You asked for ten and you get zero or one, with no error and no signal that anything went wrong.

Pre-filter: evaluate the predicate first, then search for neighbours only among the rows that passed. You get a full k results whenever k matching rows exist, and they are the nearest matching rows — which is what the caller actually meant.

What prefilter=True buys

The headline benefit is correct result counts under selective filters. It also improves quality in a subtler way: post-filtering makes the effective recall of a filtered query terrible, because the index spent its whole probe budget on candidates it then threw away. Pre-filtering aims that budget at the rows you care about.

This matters most for exactly the queries applications actually issue — "nearest documents belonging to this tenant", "similar products in stock under this price". Those filters are selective by construction, and they are the ones post-filtering handles worst.

What it costs

Pre-filtering is not free. The predicate has to be evaluated against the table before or during the search, which means reading the filtered columns. Lance's columnar layout makes that far cheaper than reading whole rows, but it is still work proportional to the table rather than to k.

There is also an interaction with the vector index. When a filter is very selective, the surviving set may be small enough that an exhaustive scan over it beats going through the IVF partitions — which is good news, since a scan over a small set is both fast and exact. When a filter is weakly selective, the predicate evaluation is mostly wasted effort on rows that would have qualified anyway. The pathological middle is a filter that removes most rows but still leaves a large absolute number: you pay full predicate evaluation and still need the index.

Making the predicate cheap

The lever is a scalar index. create_scalar_index on a column used in filters lets the engine resolve the predicate from an index structure instead of scanning that column's values. For low-cardinality categorical columns and range predicates on numerics this is the difference between a filter that is effectively free and one that dominates the query. Any column that appears in a where clause on a hot path deserves one; columns that never appear in filters should not have one, since each index is storage and maintenance.

Filter syntax

The predicate is a SQL-style string over the table's scalar columns — comparisons, AND/OR/NOT, IN, IS NULL, string equality with single-quoted literals. It is not a JSON operator document. Because it is a string, interpolating user input directly is an injection hazard in the ordinary sense: build predicates from validated values, not from raw request bodies.

Version behaviour

Whether prefilter defaults to true or false has changed across releases of the client. That makes it a genuinely dangerous default to depend on: an upgrade can silently flip a query from returning ten results to returning two, or from a cheap post-filter to an expensive predicate evaluation, with no code change on your side. Pass the argument explicitly in application code and the ambiguity disappears. It also documents intent for the next reader, which matters because the two orderings differ in results, not just in speed.

Choosing per query

A weak filter — one that keeps most of the table — is fine post-filtered, and cheaper. A selective filter should almost always be pre-filtered. If you cannot characterize the selectivity, prefer pre-filtering and pay the cost: silently returning too few results is a correctness bug, while a slower query is a performance one, and the first is far harder to notice in production.

## The two orderings Every vector database that supports metadata filtering has to decide when the predicate runs relative to the approximate search, and LanceDB exposes that decision directly on the query. **Post-filter** (the naive ordering): run the ANN search for k neighbours, then drop rows failing the predicate. Cheap, because the predicate touches only k rows. Its flaw is arithmetic: if 5% of your table matches the filter, then of ten unfiltered neighbours you expect about half a row to survive. You asked for ten and you get zero or one, with no error and no signal that anything went wrong. **Pre-filter**: evaluate the predicate first, then search for neighbours only among the rows that passed. You get a full k results whenever k matching rows exist, and they are the nearest matching rows — which is what the caller actually meant. ## What prefilter=True buys The headline benefit is correct result counts under selective filters. It also improves quality in a subtler way: post-filtering makes the effective recall of a filtered query terrible, because the index spent its whole probe budget on candidates it then threw away. Pre-filtering aims that budget at the rows you care about. This matters most for exactly the queries applications actually issue — "nearest documents belonging to this tenant", "similar products in stock under this price". Those filters are selective by construction, and they are the ones post-filtering handles worst. ## What it costs Pre-filtering is not free. The predicate has to be evaluated against the table before or during the search, which means reading the filtered columns. Lance's columnar layout makes that far cheaper than reading whole rows, but it is still work proportional to the table rather than to k. There is also an interaction with the vector index. When a filter is very selective, the surviving set may be small enough that an exhaustive scan over it beats going through the IVF partitions — which is good news, since a scan over a small set is both fast and exact. When a filter is weakly selective, the predicate evaluation is mostly wasted effort on rows that would have qualified anyway. The pathological middle is a filter that removes most rows but still leaves a large absolute number: you pay full predicate evaluation and still need the index. ## Making the predicate cheap The lever is a scalar index. `create_scalar_index` on a column used in filters lets the engine resolve the predicate from an index structure instead of scanning that column's values. For low-cardinality categorical columns and range predicates on numerics this is the difference between a filter that is effectively free and one that dominates the query. Any column that appears in a `where` clause on a hot path deserves one; columns that never appear in filters should not have one, since each index is storage and maintenance. ## Filter syntax The predicate is a SQL-style string over the table's scalar columns — comparisons, `AND`/`OR`/`NOT`, `IN`, `IS NULL`, string equality with single-quoted literals. It is not a JSON operator document. Because it is a string, interpolating user input directly is an injection hazard in the ordinary sense: build predicates from validated values, not from raw request bodies. ## Version behaviour Whether `prefilter` defaults to true or false has changed across releases of the client. That makes it a genuinely dangerous default to depend on: an upgrade can silently flip a query from returning ten results to returning two, or from a cheap post-filter to an expensive predicate evaluation, with no code change on your side. Pass the argument explicitly in application code and the ambiguity disappears. It also documents intent for the next reader, which matters because the two orderings differ in results, not just in speed. ## Choosing per query A weak filter — one that keeps most of the table — is fine post-filtered, and cheaper. A selective filter should almost always be pre-filtered. If you cannot characterize the selectivity, prefer pre-filtering and pay the cost: silently returning too few results is a correctness bug, while a slower query is a performance one, and the first is far harder to notice in production.

A filtered search asks for ten results and returns two. What is the most likely cause?
Post-filtering. The vector search returned its ten approximate neighbours over the whole table, then the predicate discarded eight of them, so the shortfall is the filter's selectivity showing through. Setting prefilter=True makes the search consider only matching rows, so you get ten whenever ten matching rows exist. The tell-tale sign is that the shortfall gets worse as the filter gets more selective.
When is post-filtering still the better choice?
When the predicate keeps most of the table. If 90% of rows match, post-filtering discards roughly one result in ten, which a slightly larger limit absorbs, and you avoid evaluating the predicate over the whole column. It is also reasonable for filters on columns with no scalar index where the evaluation would be a full column scan, provided you have verified the selectivity is genuinely low.
What does create_scalar_index change about a prefiltered query?
It gives the engine an index structure for resolving the predicate instead of reading and testing every value in that column. For categorical equality and numeric ranges on a hot path this turns filter evaluation from a cost proportional to the table into something close to free, which is what makes aggressive prefiltering practical at scale. Index only the columns that actually appear in filters.

saying these in an interview costs you the question

Assuming filters always run before the vector search
Blaming the embedding model for short filtered result sets
Relying on the prefilter default instead of passing it
Filtering on columns with no scalar index on hot paths
Interpolating raw user input into the SQL predicate string

In LanceDB, what does prefilter=True on a search's where clause change?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The two orderings

What prefilter=True buys

What it costs

Making the predicate cheap

Filter syntax

Version behaviour

Choosing per query

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions7 questions

▾related topics2 topics

The two orderings

What prefilter=True buys

What it costs

Making the predicate cheap

Filter syntax

Version behaviour

Choosing per query

answer

go deeper

context