AIyaodianzhushou/database/optimize.sql

-- ============================================================
-- 数据库优化迁移脚本
-- 目的：
--   1. 将 drug_chunks.ivfflat 索引升级为 HNSW 索引（检索质量更高）
--   2. 清理冗余的 embedding(JSON) 列（实际检索用的是 vec pgvector 列）
--   3. 为 conversations.created_at 和 messages.created_at 添加索引（按时间排序查询）
--   4. 为 messages.feedback 添加索引（反馈查询过滤）
--   5. 为 conversations.created_at 添加降序索引（对话历史分页）
--   6. 为 messages.sources 创建 GIN 索引（来源检索）
-- 注意：此脚本可重复执行（IF NOT EXISTS / IF EXISTS）
-- ============================================================

BEGIN;

-- ------------------------------------------------------------
-- 1. 向量索引升级：IVFFlat → HNSW
--    HNSW 在召回率和查询延迟上都优于 IVFFlat，尤其适合中小规模数据集。
--    lists=10 的 IVFFlat 在数据量增长后检索质量会严重下降。
-- ------------------------------------------------------------
DROP INDEX IF EXISTS public.idx_drug_chunks_vec;
-- HNSW 参数：m=16（连接数），ef_construction=64（构建时搜索宽度）
CREATE INDEX IF NOT EXISTS idx_drug_chunks_vec
    ON public.drug_chunks USING hnsw (vec vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- ------------------------------------------------------------
-- 2. 清理冗余 embedding(JSON) 列
--    drug_chunks 表同时有 embedding(JSON) 和 vec(pgvector) 两份向量存储，
--    embedding 列完全冗余，从未被 Java/Python 后端使用。
--    先删除可能依赖该列的索引（实际无），再删除列。
-- ------------------------------------------------------------
ALTER TABLE public.drug_chunks DROP COLUMN IF EXISTS embedding;

-- ------------------------------------------------------------
-- 3. 时间排序索引（解决按时间排序查询的全表扫描问题）
-- ------------------------------------------------------------
-- 对话历史分页：ORDER BY created_at DESC
CREATE INDEX IF NOT EXISTS ix_conversations_created_at
    ON public.conversations USING btree (created_at DESC);

-- 消息列表按时间升序：ORDER BY created_at ASC
CREATE INDEX IF NOT EXISTS ix_messages_created_at
    ON public.messages USING btree (created_at ASC);

-- ------------------------------------------------------------
-- 4. 反馈查询索引（管理后台反馈列表过滤）
-- ------------------------------------------------------------
CREATE INDEX IF NOT EXISTS ix_messages_feedback
    ON public.messages USING btree (feedback)
    WHERE feedback IS NOT NULL;

-- ------------------------------------------------------------
-- 5. messages.sources GIN 索引（来源检索）
--    messages.sources 当前是 JSON 类型，先转为 JSONB 再建 GIN 索引
--    （JSON 不支持 GIN 索引，JSONB 支持）
-- ------------------------------------------------------------
DO $$
BEGIN
    -- 检查列类型是否为 json（需要转为 jsonb）
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'messages' AND column_name = 'sources'
          AND data_type = 'json'
    ) THEN
        ALTER TABLE public.messages ALTER COLUMN sources TYPE jsonb USING sources::jsonb;
        RAISE NOTICE '已将 messages.sources 从 json 转为 jsonb';
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS ix_messages_sources_gin
    ON public.messages USING gin (sources);

-- ------------------------------------------------------------
-- 6. user_progress.last_study_at 索引（按最近学习时间排序）
-- ------------------------------------------------------------
CREATE INDEX IF NOT EXISTS ix_user_progress_last_study_at
    ON public.user_progress USING btree (last_study_at DESC)
    WHERE last_study_at IS NOT NULL;

COMMIT;

-- ============================================================
-- 验证脚本（执行后可查询以下语句确认优化结果）
-- ============================================================
-- 查看向量索引类型
-- SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'drug_chunks';
--
-- 确认 embedding 列已删除
-- SELECT column_name FROM information_schema.columns WHERE table_name = 'drug_chunks';
--
-- 查看所有新增索引
-- SELECT indexname, indexdef FROM pg_indexes
-- WHERE indexname IN (
--   'ix_conversations_created_at',
--   'ix_messages_created_at',
--   'ix_messages_feedback',
--   'ix_messages_sources_gin',
--   'ix_user_progress_last_study_at'
-- );