05-hybrid-vector-search.sql

-- Pattern 05 — Hybrid search: trigram + embedding in one query
-- ============================================================================
--
-- Problem: pattern 04 showed trigram's blind spot — "Session Artist 01" vs
-- "Session Artist 02" score 0.800 (template similarity) while a true duplicate
-- scored 0.700. Character overlap can't tell renamed-same-thing from
-- different-things-with-similar-names. Embeddings can; but embeddings alone
-- miss exact-string signals (typos barely move a vector, initials confuse it).
-- Blend both.
--
-- pgvector operators: `<=>` is cosine DISTANCE (0 = identical direction), so
-- similarity = 1 - distance. The HNSW index (schema.sql) accelerates
-- ORDER BY embedding <=> query — which means the candidate-generation step
-- must ORDER BY raw distance, and the blend must happen OUTSIDE, over a small
-- candidate set. Ordering directly by the blended score would forfeit the
-- index and scan every row.
-- ============================================================================

-- The incoming record to resolve: a misspelled Cassette Ghost, with the
-- near-identical embedding a real model would produce for the same entity.
\set query_name 'Cassete Ghost'

WITH query AS (
    SELECT pseudo_embedding('Cassette Ghost', 0.01) AS qvec
),
-- Step 1: candidate generation, index-accelerated, over-fetched (top 20).
vector_candidates AS (
    SELECT n.id, n.name,
           1 - (n.name_embedding <=> q.qvec) AS embedding_sim
    FROM nodes n, query q
    ORDER BY n.name_embedding <=> q.qvec      -- HNSW-indexed KNN
    LIMIT 20
)
-- Step 2: blend with trigram over just those 20 rows.
-- 0.6/0.4 embedding-leaning is a reasonable start when embeddings are
-- model-produced; tune on labeled pairs from your own review queue.
SELECT name,
       round(embedding_sim::numeric, 3)                     AS emb,
       round(similarity(name, :'query_name')::numeric, 3)   AS trgm,
       round((0.6 * embedding_sim
            + 0.4 * similarity(name, :'query_name'))::numeric, 3) AS blended
FROM vector_candidates
ORDER BY blended DESC
LIMIT 5;

-- Expected: "Cassette Ghost" and "Casette Ghost" clear the field — both have
-- near-1.0 embedding similarity AND high trigram overlap, so they blend to
-- ~0.9+. The template-similar "Session Artist NN" rows that polluted pattern
-- 04's ranking score near-zero embedding similarity here and drop away.

-- ----------------------------------------------------------------------------
-- Proof the index is doing the work — run:
--   EXPLAIN (ANALYZE, COSTS OFF)
--   SELECT id FROM nodes ORDER BY name_embedding <=> (SELECT qvec FROM ...) LIMIT 20;
--
-- and look for:
--   Index Scan using idx_nodes_embedding_hnsw on nodes
--
-- Two honesty notes about this demo:
--   * On this 53-row seed the planner correctly prefers a seq scan — an index
--     can't beat reading 53 rows. We SET LOCAL enable_seqscan = off purely to
--     display the plan shape you'd get organically at real cardinality.
--   * At real cardinality, if you STILL see Seq Scan + Sort, the usual causes
--     are a NULL-heavy embedding column, a mismatched operator class (l2 vs
--     cosine), or ordering by an expression of the distance rather than the
--     bare distance.
-- ----------------------------------------------------------------------------
BEGIN;
SET LOCAL enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT n.id
FROM nodes n
ORDER BY n.name_embedding <=> (SELECT pseudo_embedding('Cassette Ghost', 0.01))
LIMIT 20;
ROLLBACK;