基于 PostgreSQL + pgvector 的智能客服向量检索系统

CREATE EXTENSION IF NOT EXISTS vector;

SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';

DROP TABLE IF EXISTS chat_messages CASCADE;   -- 耗时5.7ms
DROP TABLE IF EXISTS chat_sessions CASCADE;   -- 耗时2.7ms
DROP TABLE IF EXISTS text_vectors CASCADE;    -- 耗时3.7ms
DROP TABLE IF EXISTS image_vectors CASCADE;   -- 耗时3.4ms

CREATE TABLE image_vectors (
    id              BIGSERIAL PRIMARY KEY,
    image_id        VARCHAR(64) NOT NULL UNIQUE,
    image_url       TEXT NOT NULL,
    thumbnail_url   TEXT,
    embedding       vector(768) NOT NULL,     -- 使用大模型生成 输出 768 维
    category        VARCHAR(100),
    tags            TEXT[],
    source          VARCHAR(50) DEFAULT 'upload',
    metadata        JSONB DEFAULT '{}',
    status          SMALLINT DEFAULT 1,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE image_vectors IS '图片向量表 - 智能客服图片检索';
COMMENT ON COLUMN image_vectors.embedding IS '使用大模型生成的768维图片向量';

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'image_vectors'
ORDER BY ordinal_position;

CREATE TABLE text_vectors (
    id              BIGSERIAL PRIMARY KEY,
    doc_id          VARCHAR(64) NOT NULL UNIQUE,
    title           TEXT,
    content         TEXT NOT NULL,
    content_type    VARCHAR(20) DEFAULT 'faq',
    embedding       vector(1024) NOT NULL,    -- 大模型 输出 1024 维
    category        VARCHAR(100),
    tags            TEXT[],
    source          VARCHAR(100),
    metadata        JSONB DEFAULT '{}',
    priority        SMALLINT DEFAULT 0,
    status          SMALLINT DEFAULT 1,
    expire_at       TIMESTAMPTZ,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE text_vectors IS '文本向量表 - 智能客服知识库检索';
COMMENT ON COLUMN text_vectors.embedding IS '大模型生成的1024维文本向量';

CREATE TABLE chat_sessions (
    id              BIGSERIAL PRIMARY KEY,
    session_id      VARCHAR(64) NOT NULL UNIQUE,
    user_id         VARCHAR(64) NOT NULL,
    channel         VARCHAR(20) DEFAULT 'web',
    status          VARCHAR(20) DEFAULT 'active',
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    closed_at       TIMESTAMPTZ
);

CREATE TABLE chat_messages (
    id              BIGSERIAL PRIMARY KEY,
    session_id      VARCHAR(64) NOT NULL,
    role            VARCHAR(10) NOT NULL,
    content_type    VARCHAR(20) DEFAULT 'text',
    content         TEXT NOT NULL,
    image_url       TEXT,
    embedding       vector(1024),             -- 1024维向量用于相似问题推荐
    matched_doc_ids TEXT[],
    confidence      REAL,
    feedback        SMALLINT,
    created_at      TIMESTAMPTZ DEFAULT NOW()
);

COMMENT ON TABLE chat_messages IS '对话消息表 - 含向量字段用于相似问题推荐';

SELECT table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')
ORDER BY table_name;

-- 图片分类索引（部分索引，仅 status=1 的记录）
CREATE INDEX idx_image_vectors_category ON image_vectors (category) WHERE status = 1;

-- 图片标签 GIN 索引
CREATE INDEX idx_image_vectors_tags ON image_vectors USING GIN (tags) WHERE status = 1;

-- 图片元数据 JSONB GIN 索引
CREATE INDEX idx_image_vectors_metadata ON image_vectors USING GIN (metadata);

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('image_vectors', 'text_vectors', 'chat_sessions', 'chat_messages')
ORDER BY tablename, indexname;

INSERT INTO text_vectors (doc_id, title, content, content_type, embedding, category, tags, priority)
VALUES (
    'faq-001',
    '如何重置密码',
    '您可以通过以下步骤重置密码：1. 单击登录页面的"忘记密码"链接；2. 输入注册邮箱；
     3. 查收重置邮件；4. 单击邮件中的链接设置新密码。',
    'faq',
    (SELECT array_agg(random())::vector(1024) FROM generate_series(1, 1024)),
    '账号管理',
    ARRAY['账号管理', 'faq'],
    2
);

SELECT doc_id, title, category, priority, status,
       LEFT(content, 40) || '...' AS content_preview
FROM text_vectors
ORDER BY priority DESC, doc_id;

INSERT INTO image_vectors (image_id, image_url, embedding, category, tags, metadata)
VALUES (
    'img-001',
    'https://cdn.example.com/products/phone_x1.jpg',
    (SELECT array_agg(random())::vector(768) FROM generate_series(1, 768)),
    '产品图片',
    ARRAY['产品图片', 'smart_cs'],
    '{"width":800,"height":600,"format":"jpg"}'::jsonb
);

INSERT INTO chat_sessions (session_id, user_id, channel, status)
VALUES
    ('sess-001', 'user-1001', 'web', 'active'),
    ('sess-002', 'user-1002', 'wechat', 'active'),
    ('sess-003', 'user-1003', 'app', 'closed');

ANALYZE image_vectors;    -- 耗时2.8ms
ANALYZE text_vectors;     -- 耗时2.1ms
ANALYZE chat_sessions;    -- 耗时1.8ms
ANALYZE chat_messages;    -- 耗时1.9ms

SELECT
    'image_vectors' AS table_name, count(*) AS row_count FROM image_vectors
UNION ALL SELECT
    'text_vectors', count(*) FROM text_vectors
UNION ALL SELECT
    'chat_sessions', count(*) FROM chat_sessions
UNION ALL SELECT
    'chat_messages', count(*) FROM chat_messages
ORDER BY table_name;

CREATE INDEX idx_image_vectors_embedding_cosine
ON image_vectors
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

CREATE INDEX idx_text_vectors_embedding_cosine
ON text_vectors
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

CREATE INDEX idx_chat_messages_embedding_cosine
ON chat_messages
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SELECT
    indexname AS index_name,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%embedding_cosine'
ORDER BY indexname;

-- 设置搜索候选列表大小（值越大检索越精确，推荐100 ~ 200）
SET hnsw.ef_search = 100;

-- 开启迭代扫描模式（解决 WHERE 过滤时结果不足的问题）
SET hnsw.iterative_scan = relaxed_order;

SHOW hnsw.ef_search;

# pgvector HNSW 搜索参数
hnsw.ef_search = 100
hnsw.iterative_scan = relaxed_order

SELECT
    iv.image_id,
    iv.image_url,
    iv.category,
    iv.tags,
    1 - (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001'))
        AS similarity
FROM image_vectors iv
WHERE iv.status = 1
ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-001')
LIMIT 5;

SELECT
    iv.image_id,
    iv.image_url,
    iv.category,
    1 - (iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-004'))
        AS similarity
FROM image_vectors iv
WHERE iv.status = 1
  AND iv.category = '产品图片'
ORDER BY iv.embedding <=> (SELECT embedding FROM image_vectors WHERE image_id = 'img-004')
LIMIT 5;

SELECT
    tv.doc_id,
    tv.title,
    tv.category,
    tv.priority,
    1 - (tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-001'))
        AS similarity
FROM text_vectors tv
WHERE tv.status = 1
  AND (tv.expire_at IS NULL OR tv.expire_at > NOW())
ORDER BY tv.embedding <=> (SELECT embedding FROM text_vectors WHERE doc_id = 'faq-001')
LIMIT 5;