PostgreSQL中pgvector向量相似度查询性能不稳定问题排查及优化

问题背景 #

在检索增强生成（RAG）场景中，将PostgreSQL向量查询封装为数据库函数后，出现性能低下、波动且不可预测的问题，需排查原因并优化。

代码实现 #

数据库函数 #

CREATE OR REPLACE FUNCTION public.match_document_chunks (embedding vector, match_threshold double precision, match_count integer, num_probes integer)
RETURNS TABLE (id integer, processed_document_id integer, content text, similarity double precision)
LANGUAGE plpgsql
AS $function$
#variable_conflict use_variable
BEGIN
EXECUTE format('SET LOCAL ivfflat.probes = %s', num_probes);
RETURN query
SELECT
    processed_document_chunks.id,
    processed_document_chunks.processed_document_id,
    processed_document_chunks.content,
    (processed_document_chunks.embedding <#> embedding) * -1 as similarity
    FROM
        processed_document_chunks
    WHERE (processed_document_chunks.embedding <#> embedding) * -1 > match_threshold
    ORDER BY
        processed_document_chunks.embedding <#> embedding
    LIMIT match_count;
END;
$function$

表结构 #

CREATE TABLE public.processed_document_chunks(
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    content text not null,
    embedding vector(1536) not null,
    page integer not null,
    chunk_index integer not null,
    processed_document_id integer REFERENCES public.processed_documents(id) ON DELETE CASCADE
);

索引创建 #

DO $$
DECLARE 
  index_name TEXT;
  numRows INT;

BEGIN
-- Delete old embedding indices first
FOR index_name IN
    SELECT indexname FROM pg_indexes WHERE indexname LIKE '%processed_document_chunks_embedding_idx%'
LOOP
    EXECUTE 'DROP INDEX IF EXISTS ' || index_name;
END LOOP;

-- Generate new embedding indices
SELECT ROUND(COUNT(*) / 1000) INTO numRows FROM processed_document_chunks;

EXECUTE 'CREATE INDEX ON processed_document_chunks USING ivfflat (embedding vector_l2_ops) WITH (lists = ' || numRows || ')';
EXECUTE 'CREATE INDEX ON processed_document_chunks USING ivfflat (embedding vector_ip_ops) WITH (lists = ' || numRows || ')';
EXECUTE 'CREATE INDEX ON processed_document_chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = ' || numRows || ')';
END $$;

架构信息 #

• PostgreSQL 15.1（Ubuntu 15.1-1.pgdg20.04+1），aarch64架构，64位
• 托管于Supabase欧盟法兰克福AWS节点，实例类型为t4g.small

使用方式 #

将用户查询转换为1536维向量后传入函数，参数固定为：

• embedding：1536维向量
• match_threshold：0.85
• match_count：128
• num_probes：12（按pgvector文档公式sqrt(num_rows / 1000)计算）

测试发现 #

• 连续两次相同查询，第二次平均比第一次快63%（N=30）
• 连续两次不同查询，性能无显著差异（N=30）
• 查询返回结果数量与响应时间无相关性
• 随机向量查询平均耗时约4.7秒，性能波动大（N=200）
• 移除WHERE子句后，平均响应时间降至约0.6秒

问题解答 #

1. 移除WHERE子句是否改变查询逻辑？ #

会。原WHERE子句的作用是过滤出相似度大于0.85的结果，再从中取前128条；移除后，会直接返回全表中与目标向量最相似的前128条，无论其相似度是否达标，逻辑完全不同。

2. 带WHERE子句的查询性能为何不可预测？ #

核心原因是云实例资源不足（t4g.small的CPU/内存规格较低），加上ivfflat索引的特性：

• 第一次查询时，数据未被缓存，需要从磁盘读取并计算相似度，耗时较长；第二次相同查询命中缓存，速度大幅提升
• 小实例的资源（CPU、内存）容易出现波动，导致向量计算、索引扫描的性能不稳定
• WHERE子句需要对筛选出的结果额外计算相似度并判断阈值，增加了CPU计算量，放大了资源波动的影响

3. 不带WHERE子句的查询为何更快且性能稳定？ #

pgvector的ivfflat索引可以直接基于向量距离排序，快速取出前N条数据，不需要额外的过滤逻辑：

• 执行路径更简单，索引优化更直接，避免了WHERE子句带来的额外计算
• 排序取前N的逻辑更容易被缓存，重复查询的性能波动小
• 减少了CPU的计算负载，在资源有限的实例上表现更稳定

4. 如何让查询性能变得稳定？ #

• 升级实例配置：更换为更高规格的实例（如t4g.medium或以上），解决资源不足的核心问题
• 优化ivfflat参数：调整lists数量（可根据测试调整，不一定严格按rows/1000），同时匹配num_probes参数，平衡精度与性能
• 简化函数逻辑：将PL/pgSQL函数改为SQL函数，避免EXECUTE SET LOCAL带来的额外开销；或者将ivfflat.probes设置为会话级参数，无需每次函数调用都执行
• 预热缓存：定期执行常用查询，将热点数据加载到内存中
• 改用hnsw索引：pgvector 0.5+支持hnsw索引，相比ivfflat，其性能更稳定，适合高维向量和大数据量场景

5. 还有哪些优化查询性能的建议？ #

• 避免重复计算：在查询中通过子查询或CTE提前计算相似度，避免WHERE和SELECT中重复执行(embedding <#> $1) * -1
• 调整num_probes：根据实际测试调整该参数，不是只依赖公式，找到精度与性能的平衡点
• 定期分析表：执行ANALYZE processed_document_chunks;，让PostgreSQL生成更准确的查询计划
• 监控资源使用：跟踪数据库的CPU、内存、磁盘IO指标，及时发现资源瓶颈
• 限制结果集：如果业务允许，适当降低match_count，减少数据处理量

6. 若表数据量翻倍，查询性能会如何变化？该查询的复杂度是多少？ #

• ivfflat索引：复杂度约为O((n/lists) * probes)。如果数据量翻倍但lists数量未同步调整，索引扫描的范围会变大，性能会明显下降；若同步翻倍lists数量，性能波动会保持在原有水平，但绝对耗时可能略有上升。
• hnsw索引：复杂度为O(log n)，数据量翻倍后性能下降幅度远小于ivfflat，更适合大数据量场景。
  总体来说，若使用ivfflat且未调整参数，数据量翻倍后查询耗时会显著增加；若改用hnsw或调整ivfflat参数，性能下降会更平缓。

补充说明与解决方案 #

通过explain(analyze, verbose, buffers, settings)获取执行计划后，确认性能不稳定的核心原因是云服务器实例配置不足。测试Supabase不同计算实例后，升级实例规格即可解决性能波动问题。

内容的提问来源于stack exchange，提问作者1awuesterose