Hybrid: WHERE Filters + Vector Ranking

~22 min · pgvector, hybrid, sql

Level 0Scout

0 XP0/41 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The shape of a real production query

You almost never want "top-10 nearest across the whole table." You want "top-10 nearest from THIS tenant, in THESE projects, updated since X, where the user has read permission." pgvector lets you write that as one SQL statement and lean on Postgres for the structured filtering.

Index-aware filtering

The HNSW/IVFFlat index returns approximate nearest neighbors first; WHERE filters are applied on the candidate set. If your filter is very selective, ask Postgres to filter first and then rank — usually achieved with a CTE or a partial index. Measure with EXPLAIN ANALYZE.

The 'iterative' pattern

Sometimes the index returns 10 candidates but only 3 pass the filter. The fix is to over-fetch and re-filter: ask for 100 nearest, filter to your conditions, take top-10 of survivors. Tune the over-fetch factor on real data.

Code

Hybrid query with structured filter·sql

WITH candidates AS (
    SELECT id, source, chunk_index, text, metadata,
           1 - (embedding <=> $1) AS similarity
    FROM   chunks
    WHERE  metadata->>'tenant'    = $2
      AND  metadata->>'doc_type'  = ANY($3::text[])
      AND  updated_at >= now() - interval '90 days'
    ORDER BY embedding <=> $1
    LIMIT 100                       -- over-fetch for the filter
)
SELECT *
FROM   candidates
WHERE  similarity >= 0.30           -- minimum quality floor
ORDER  BY similarity DESC
LIMIT  10;

Same query from Python·python

with conn.cursor() as cur:
    cur.execute(open('hybrid_query.sql').read(), (query_vec, 'cwkpippa', ['markdown', 'code']))
    rows = cur.fetchall()

for row in rows:
    print(f'{row[5]:.3f}  {row[1]}#{row[2]}  → {row[3][:80]}')

External links

Exercise

Write a hybrid query for your data: a vector ranking plus 2 structured filters of your choice. Run EXPLAIN ANALYZE. Confirm the index is used. Vary the over-fetch limit (LIMIT 30, 100, 300) and note where survivor count stabilizes.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousHNSW vs IVFFlat: Picking the Index Next →Migrations, Re-embedding, and Zero Downtime

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.