pg.ddx.io  pgvector/pgvector GitHub issues and pull requests  
help / color / mirror / Atom feed
From: OmkarPalika (@OmkarPalika) <noreply+OmkarPalika@github.com>
To: pgvector/pgvector <noreply+pgvector-pgvector@github.com>
Subject: Re: [pgvector/pgvector] issue #761: Enhancement: multicolumn vector index design
Date: Tue, 18 Aug 2026 09:42:01 +0000
Message-ID: <gh-pgvector-pgvector-761-c5326420332@github.com> (raw)
In-Reply-To: <gh-pgvector-pgvector-761@github.com>
References: <gh-pgvector-pgvector-761@github.com>

@IgnazioDS asked what happens when the filter column has high cardinality but low
correlation with the vector clusters, and @ankane asked for numbers with the table
and index setup. I measured that case recently, so here it is.

**Setup:** PostgreSQL 17, pgvector 0.8.6, 1M SIFT-128 vectors, `vector_l2_ops`,
HNSW `m=16, ef_construction=64`, `LIMIT 10`, 100 held-out queries per cell. Ground
truth is an exact scan in the same database with `enable_indexscan` and
`enable_bitmapscan` off, so there is no separate ground-truth pipeline to disagree
with. Every measurement asserts its plan with EXPLAIN before timing.

Three filter layouts at identical 0.1% selectivity:

- `bucket` — uniform random, independent of the embedding. The uniformly-distributed
  tenant_id case.
- `bucket_corr` — rows ranked by distance to an anchor, so the filtered set is one
  compact ball.
- `bucket_multi` — ranked within each of 8 clusters, so the filtered set is 8
  separated balls.

recall@10, with rows actually returned out of the 10 requested in parentheses.
"recipe" is `iterative_scan=relaxed_order, max_scan_tuples=100000,
scan_mem_multiplier=4`:

| layout | query | default | recipe |
|---|---|---|---|
| uniform | near | 0.003 (0.03) | 1.000 |
| uniform | far | 0.002 (0.02) | 1.000 |
| correlated | near | 0.468 (4.76) | 0.948 |
| correlated | far | 0.000 (0) | 0.000 (0) |
| multicluster | near | 0.228 (2.29) | 0.997 |
| multicluster | far | 0.000 (0) | 0.659 (9.11) |

Directly to the uniform question: it is the **worst** case for stock pgvector, 0.003
recall, 0.03 rows returned out of 10. But it is also the case iterative_scan fully
repairs, back to 1.000. The graph walk and the filtered set are independent, so
widening the scan budget keeps finding fresh matches.

The case that does not repair is the opposite one. A single compact region, query
outside it: 0.000 both stock and tuned. Not low recall, zero rows, no error. The scan
budget gets spent inside a region that contains no matches. EXPLAIN on one of those:
`Rows Removed by Filter: 101373`, the whole `max_scan_tuples` budget spent without a
single row surviving the predicate.

So the uniform benchmark that everyone runs is misleading in both directions. It
understates stock pgvector on ordinary in-tenant search, 0.003 against 0.468 for the
correlated case, and it completely hides the zero-row failure.

Two more things that may be useful here:

`ef_search` does not substitute for iterative_scan. With relaxed_order on, ef=40 gives
0.847 and ef=400 gives 0.865, at 99ms and 106ms. The knobs that matter are
`max_scan_tuples` as a gate and `scan_mem_multiplier` as a dial, and raising one
without the other does nothing.

The recipe is not free. At concurrency 32 it costs 2.2x memory per query and drops
throughput from 130 to 39 qps, p95 from 375ms to 1463ms.

@jiahuijiang asked about a workaround. For a filter that is a plain scalar column, a
btree on it and letting the planner do bitmap-heap plus exact sort beat every HNSW
configuration I tried, on both correctness and latency. The same query that returns
0 rows in 683ms through HNSW returns the correct 10 in 3.6ms that way. It stops
scaling once the filter matches more than roughly 1,000 to 2,000 rows at 1M, and
`CLUSTER` on the filter column pushes that to 8,000 to 9,000 by making the matching
rows physically contiguous.

That is not an argument against a multicolumn index, it is an argument that the
current cost model cannot see the failure coming. A `SELECT 1 ... WHERE filter LIMIT 1`
existence probe returns in 0.175ms in exactly the zero-row case. The rows are there
and cheap to find, they are just unreachable through the graph, and nothing in the
plan or the output signals it.

Everything above reproduces with `docker compose up` and two commands:
https://github.com/OmkarPalika/pgvector-filtered-recall


view thread (18+ messages)  latest in thread

Message-ID: <gh-pgvector-pgvector-761-c5326420332@github.com>
Permalink:  ../gh-pgvector-pgvector-761-c5326420332@github.com/

 · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox