البحث المتجهي باستخدام pgvector

ما فائدة المتجهات؟

تمكين pgvector

CREATE EXTENSION vector;

عمود المتجه

CREATE TABLE docs (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  embedding vector(1536)
);

-- 1536 is OpenAI text-embedding-3-small dimension; varies by model.

إدراج التضمينات

INSERT INTO docs (title, body, embedding)
VALUES ('SQL Course', 'Learn SQL', '[0.013, -0.024, ...]');

عوامل المسافة

البحث عن أقرب جار

SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM docs
ORDER BY embedding <=> $1
LIMIT 10;

فهرس IVFFlat

CREATE INDEX docs_embedding_idx
  ON docs USING IVFFLAT (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Tune lists by row count: rule of thumb sqrt(N).
-- Set probes per query for accuracy:
SET ivfflat.probes = 10;

فهرس HNSW ‏(PG 16+)

CREATE INDEX docs_embedding_hnsw
  ON docs USING HNSW (embedding vector_cosine_ops);

SET hnsw.ef_search = 100;

البحث بالمتجهات مع التصفية

SELECT id, title
FROM docs
WHERE language = 'en'
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY embedding <=> $1
LIMIT 10;
-- For best perf, partial index on the filter.

نمط RAG

-- 1. Embed user query
-- 2. SELECT top-N docs by cosine
-- 3. Send docs + query to LLM
-- 4. Return LLM's answer to user

البحث الهجين

SELECT id, title,
  0.6 * (1 - (embedding <=> $1)) + 0.4 * ts_rank(search_doc, $2) AS score
FROM docs
WHERE embedding <=> $1 < 0.7
   OR search_doc @@ $2
ORDER BY score DESC LIMIT 10;

اختيار عدد الأبعاد

خلاصة

تحقق سريع