我很笨--学习PG Vector 1个小时我懂得了索引 基本原理--要不你也试试！！ （系列 2 ）

SELECT *
FROM items
WHERE category = 'news'
ORDER BY embedding <-> '[...]'
LIMIT 10;

                      ^
test=# select * from pg_extension ;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14482 | plpgsql |       10 |           11 | f              | 1.0        |           | 
 16715 | vector  |       10 |         2200 | t              | 0.8.1      |           | 
(2 rows)

test=# CREATE TABLE items (
    id        BIGSERIAL PRIMARY KEY,
    category  TEXT,
    user_id   INT,
    created_at TIMESTAMP,
    embedding vector(3)
);

INSERT INTO items (category, user_id, created_at, embedding) VALUES
('news',   1, now() - interval '1 day', '[0.1, 0.2, 0.3]'),
('news',   2, now() - interval '2 day', '[0.2, 0.1, 0.4]'),
('news',   1, now() - interval '3 day', '[0.9, 0.8, 0.7]'),
('sports', 1, now() - interval '1 day', '[0.1, 0.2, 0.25]'),
('sports', 2, now() - interval '2 day', '[0.8, 0.7, 0.6]');

test=# explain (verbose) SELECT
test-#     id,
test-#     category,
test-#     embedding,
test-#     embedding <-> '[0.1,0.2,0.3]' AS distance
test-# FROM items
test-# ORDER BY embedding <-> '[0.1,0.2,0.3]'
test-# LIMIT 5;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit  (cost=30.96..30.97 rows=5 width=80)
   Output: id, category, embedding, ((embedding <-> '[0.1,0.2,0.3]'::vector))
   ->  Sort  (cost=30.96..32.76 rows=720 width=80)
         Output: id, category, embedding, ((embedding <-> '[0.1,0.2,0.3]'::vector))
         Sort Key: ((items.embedding <-> '[0.1,0.2,0.3]'::vector))
         ->  Seq Scan on public.items  (cost=0.00..19.00 rows=720 width=80)
               Output: id, category, embedding, (embedding <-> '[0.1,0.2,0.3]'::vector)
(7 rows)

test=# 



test=# explain (verbose) SELECT
test-#     id,
test-#     category,
test-#     embedding,
test-#     embedding <-> '[0.1,0.2,0.3]' AS distance
test-# FROM items
test-# WHERE category = 'news'
test-# ORDER BY embedding <-> '[0.1,0.2,0.3]'
test-# LIMIT 3;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit  (cost=19.05..19.06 rows=3 width=80)
   Output: id, category, embedding, ((embedding <-> '[0.1,0.2,0.3]'::vector))
   ->  Sort  (cost=19.05..19.06 rows=4 width=80)
         Output: id, category, embedding, ((embedding <-> '[0.1,0.2,0.3]'::vector))
         Sort Key: ((items.embedding <-> '[0.1,0.2,0.3]'::vector))
         ->  Seq Scan on public.items  (cost=0.00..19.01 rows=4 width=80)
               Output: id, category, embedding, (embedding <-> '[0.1,0.2,0.3]'::vector)
               Filter: (items.category = 'news'::text)
(8 rows)

CREATE INDEX idx_items_embedding_ivf
ON items
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 10);

lists (簇中心)
 ├─ list 1 → 一堆向量
 ├─ list 2 → 一堆向量
 └─ list N → 一堆向量

<-> vector_l2_ops  欧式距离  寻找几何空间中的最近的点
<#> vector_ip_ops（内积） 相似度打分
<=> vector_cosine_ops    方向接近 余弦距离  

CREATE INDEX idx_items_embedding_ivf
ON items
USING ivfflat (embedding vector_l2_ops)
WITH (lists = 300);

SET ivfflat.probes = 5;

IVF 索引
 ├─ list 1   → 333 vectors
 ├─ list 2   → 332 vectors
 ├─ ...
 └─ list 300 → 335 vectors