How do you combine vector similarity search with SQL filters (hybrid search) in pgvector?

-- Filtered vector search: find similar documents in a specific category
SELECT id, content, embedding <-> '[...]' AS distance
FROM documents
WHERE category = 'technical'          -- metadata filter
  AND created_at > NOW() - INTERVAL '30 days'  -- date filter
ORDER BY distance
LIMIT 5;

-- Filter by user ownership:
SELECT id, title, embedding <=> '[...]' AS sim_dist
FROM articles
WHERE user_id = 42
  AND is_published = TRUE
ORDER BY sim_dist
LIMIT 10;

-- JOIN with another table:
SELECT d.id, d.content, d.embedding <-> '[...]' AS distance,
       c.name AS category_name
FROM documents d
JOIN categories c ON d.category_id = c.id
WHERE c.name IN ('AI', 'Machine Learning')
ORDER BY distance
LIMIT 5;

-- Distance threshold (only return results within a distance)
SELECT id, content, embedding <-> '[...]' AS distance
FROM documents
WHERE embedding <-> '[...]' < 0.5   -- only close vectors
ORDER BY distance
LIMIT 20;

-- NOTE: ANN indexes (HNSW/IVFFlat) may have reduced recall with filters
-- Workaround: increase ef_search / probes before the query
SET hnsw.ef_search = 100;
SELECT id FROM documents
WHERE category = 'technical'
ORDER BY embedding <-> '[...]' LIMIT 5;
RESET hnsw.ef_search;