Skip to content

pgvector directory filter indexes ​

pgvector directory filtering combines tenant predicates with normalized URI scopes. In a million-row benchmark, the ordinary uri index did not serve these scope predicates: a directory matching about a hundred rows still required scanning the tenant's candidates first. A matching composite B-tree expression index substantially reduced narrow and empty-directory scans. Scopes covering an entire tenant saw limited improvement.

This guide records the candidate index and measured evidence. The backend does not automatically create this expression index. It was tested only on isolated benchmark collections and removed with their tables. See PostgreSQL + pgvector for setup and capability boundaries.

How directory filtering works ​

PathScope("uri", "viking://resources/g00/d000", depth) on a path field becomes a /resources/g00/d000 scope, compiled to parameterized SQL alongside account_id, level, ACL and other predicates. level=0/1/2 represents the L0 abstract, L1 overview and L2 content respectively. Level and path depth are independent predicates.

depthScope
Less than 0The node and all descendants
Equal to 0The node's exact path
Greater than 0The node and descendants with at most depth relative path segments

The query applies this SQL expression to stored uri values:

sql
CASE
    WHEN uri IS NULL THEN NULL
    WHEN btrim(uri) = ''
      OR rtrim(btrim(uri), '/') = '' THEN '/'
    ELSE rtrim(btrim(uri), '/')
END

Below, N(uri) abbreviates that expression; it is not a new column or database function. An unbounded scope can be summarized as:

text
account_id = :account_id
AND (N(uri) = :path OR N(uri) LIKE :child_pattern ESCAPE '\')
AND level = :level

For /resources/g00/d000, the child pattern is /resources/g00/d000/%. Literal %, _ and \ in the path are escaped first; the final / preserves the directory boundary. Positive-depth queries also count relative segments in matching paths.

See _sql_normalized_path and _build_path_scope_clause. pgvector reuses this filter compiler.

Why the ordinary URI index was insufficient ​

An ordinary B-tree keys the original uri; these directory predicates compare a CASE/btrim/rtrim expression instead. The observed plan used the account_id index, then evaluated the path condition on the retrieved tenant rows.

In the million-row table, a directory matched 100 rows, but the tenant index produced 100,000 candidates. The parallel Bitmap Heap Scan removed about 99,900 rows through path filtering. Interleaved tenants and work_mem=4MB also resulted in a lossy bitmap and additional index-condition rechecks. Filter-only count(*) had a p50 of 141.11 ms, while flat Top-10 took 141.42 ms: scanning and filtering dominated this case, rather than computing distances for the final 100 rows.

For level=0/1, a BitmapAnd over the existing level and account_id indexes reduced candidates to 1,000 rows in this fixture. level=2 represented 98% of the data and was insufficiently selective to solve the scan problem. Benefits depend on layer proportions and tenant layout.

Composite expression index ​

Use account_id first, followed by the query's normalized URI expression with text_pattern_ops:

sql
-- Replace public."context" with the actual collection table,
-- and choose a distinct index name for each table.
CREATE INDEX context_account_uri_scope_idx
ON public."context" (
    account_id,
    (CASE
        WHEN uri IS NULL THEN NULL
        WHEN btrim(uri) = ''
          OR rtrim(btrim(uri), '/') = '' THEN '/'
        ELSE rtrim(btrim(uri), '/')
    END) text_pattern_ops
);

ANALYZE public."context";

account_id restricts the tenant. The expression key matches the query, and text_pattern_ops supports these prefix LIKE ranges and equality. The index can establish that an empty directory has no candidates without walking the whole tenant. PostgreSQL still chooses plans according to statistics and cost.

The index preserves path, tenant and ACL semantics. It does not separately optimize the relative-segment calculation for positive depth. It is a scalar B-tree; vector distances still use flat or HNSW retrieval. Flat queries set enable_indexscan=off to preserve exact search, but Bitmap Scan remains available and can use this index.

For the same million-row query matching 100 rows, key plan nodes became:

text
Bitmap Heap Scan
  Actual Rows: 100
  Exact Heap Blocks: 77
  Rows Removed by Filter: 0
  BitmapOr
    Bitmap Index Scan on the scope index: 2 rows (exact path)
    Bitmap Index Scan on the scope index: 98 rows (child prefix)

The 2 exact-path rows are L0/L1, and the 98 descendants are L2 files. This excerpt omits full predicates and distance sorting. Adding level=2 restricts candidates to that layer.

Benchmark method ​

Tests ran on 2026-11-13, using code revision eaf44199aacfb601f059202bd8d04246e5248f2e. The real collection adapter.query path queried PostgreSQL. Timings include the pool, SQL and result reads, but exclude HTTP, embedding generation and the complete recursive retrieval workflow.

ItemSetting
HostmacOS arm64, 18 logical CPUs, 48 GiB RAM
DatabasePostgreSQL 17.6, pgvector 0.8.7, local loopback
PostgreSQLshared_buffers=128MB, work_mem=4MB, max_parallel_workers_per_gather=2, lc_collate=C
Poolmin_size=1, max_size=4
VectorsRandom unit vectors, 128 dimensions, cosine distance; a separate 20,000-row, 4096-dimensional flat test
Size100,000-row baseline and 1,000,000-row main test
Million-row layout10 interleaved tenants, 10 groups per tenant, 100 directories per group, 100 rows per directory
LayersOne L0 and one L1 row per directory, 98 L2 rows; half of the L2 files are under sub
QueriesTenant a0, Top-10; scope, depth and level tested separately
SamplesFive warmups per condition; 100 samples for the 100k flat baseline and level supplement, 200 for other conditions
ConcurrencyClosed-loop client threads, 1 or 4

The million-row fixture contains 10,000 (account_id, directory path) combinations. Binary COPY loaded synthetic data; this was not a production traffic or write-throughput benchmark. Current and expression-index phases ran sequentially, without controlling the OS page cache. The 20 query vectors use the first data batch's random seed (base seed 20261113), corresponding to its first 20 rows; 2 belong to tenant a0. Recall describes only this small set of corpus-derived queries.

There were 98 conditions and 14,800 measured requests. Condition summaries are available in the main CSV and level supplement CSV. The CSVs retain conditions, samples, matches, latency percentiles, QPS and measured recall; they do not contain per-request result IDs or full plans.

Million rows: OV level predicates ​

All results below are single-client flat Top-10 in ms. The level supplement rebuilt a million-row fixture with the same distribution and measured 100 requests per condition.

LevelScopeMatchesCurrent p50Current p99Expression-index p50Expression-index p99
2Directory subtree98144.28197.600.480.95
2Empty directory0139.95174.440.380.82
2Tenant root98,000145.01252.04143.62209.90
0Directory subtree17.299.180.601.21
1Directory subtree16.989.530.500.93

The L2 directory's p50 improved by about 300 times. The tenant root still processes about a hundred thousand rows, so path-index benefits are limited. L0/L1 already benefit from layer indexes, but the path index narrows their candidates further.

Million rows: path depth and concurrency ​

These queries have no additional level predicate: the directory includes 2 summaries and 98 files. All values are single-client flat Top-10 in ms.

ScopeMatchesCurrent p50Current p99Expression-index p50Expression-index p99
Tenant root100,000155.16214.50139.60192.18
Group subtree10,000140.67167.6011.7712.45
Directory subtree100141.42181.790.460.65
depth=151143.07176.390.550.92
depth=02132.26162.810.340.49
Empty directory0137.67168.530.360.65

For the directory with concurrency 4, QPS increased from 13.6 to 3,524.9, and p99 fell from 456.80 ms to 10.11 ms. These results include pool waits and creation/release of extra connections; they are not pure SQL throughput over four permanently retained connections.

100k rows: HNSW post-filtering ​

HNSW used m=16, ef_construction=64, ef_search=100, iterative_scan=strict_order, and the database's default scan limit. Single-client recall used flat results from the same data and 20 query vectors as ground truth, with denominator min(10, matches).

ScopeMatchesFlat p50 msHNSW-configured p50 msMean Recall@10
Tenant root10,00012.433.4152.5%
Directory subtree10010.7518.9367.0%
Empty directory010.5429.81Not applicable

These three scopes used HNSW Index Scan. A directory plan filtered out 6,155 candidates before returning 10 rows; the empty-directory plan filtered 19,916. HNSW configuration does not force every query through ANN: depth=0/1 used scalar Bitmap Scan plus exact sorting, so their 100% recall cannot be attributed to HNSW.

The test did not evaluate expression indexes combined with HNSW or tune HNSW parameters. It does not establish that HNSW is generally unsuitable for directory filtering, or that returning Top-10 guarantees high recall.

The 4096-dimensional boundary ​

The 4096-dimensional test used only 20,000 rows and flat retrieval. These single-client queries had no level predicate:

ScopeMatchesCurrent p50 msExpression-index p50 ms
Tenant root2,00013.1314.13
Directory subtree1005.404.19
Empty directory04.773.44

URI scans, vector processing and TOAST costs differ from the million-row 128-dimensional case. The adapter's vector HNSW limit is 2000 dimensions, so 4096-dimensional retrieval uses flat. Do not extrapolate 128-dimensional speedups or ANN results to a million-row, 4096-dimensional workload.

Validation and rollout ​

Add the candidate index and run ANALYZE on representative data, then prefix actual adapter SQL with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). Record matches, scanned/rechecked rows, heap blocks, p50/p95/p99, throughput and ANN recall. Narrow scopes should be able to use the scope index to reduce candidates; tenant-wide scopes may continue using the account index or other scan paths.

An index name does not validate its definition. Check collection fields and the expression before deployment, rather than hiding a conflicting definition with IF NOT EXISTS. For online builds, evaluate CREATE INDEX CONCURRENTLY, which runs outside transaction blocks, and verify that the resulting index is valid. The benchmark used ordinary CREATE INDEX after data loading. Additional disk usage and write-maintenance cost were not separately measured.

Example rollback for the manual index:

sql
DROP INDEX public.context_account_uri_scope_idx;

Future automation can share the SQL expression builder and handle new collections, existing-collection migrations and physical index-definition checks separately. Validate trailing slashes, empty paths, LIKE metacharacters, depth, ACL predicates, directory skew, concurrent writes and different memory settings. Choosing exact or ANN retrieval by candidate size is a future option; the current backend has no automatic switching policy.

This was a warmed, local synthetic benchmark with limited p99 samples. It did not cover realistic ACL complexity, the full service path, sustained loads, cold caches, clustered tenant rows or partitioning. Results establish an index direction, not production latency or capacity guarantees.

Open source under the AGPL-3.0 License. Font licenses