Postgres 向量内联与分离表性能实测对比

在 Postgres 中向量存储：内联表 vs. 独立表——量化 Join 开销

测试语义搜索、带过滤条件的查询以及多表连接在 AlloyDB 中的表现，衡量向量数据解耦后的真实性能成本。

如果你正在使用向量嵌入，可能已经知道嵌入模型在不断演进，需要时不时地用新版本刷新嵌入。在上一篇文章中，我们讨论了嵌入刷新时表中出现的膨胀问题、TOAST 段以及相关索引。作为将嵌入与数据存储在同一张表中的替代方案，我提出了另一种布局——将嵌入放在专门的表中。在这篇文章中，我将展示与将嵌入存储在同一张表中相比，这种方式可能带来的性能影响。

为了测试查询，我需要带有真实嵌入的样本数据。之前文章中已经解释过原因——使用 ANN 索引时可能会对结果产生明显影响。我准备了一个包含 30k 行样本产品的数据集，基于产品描述构建嵌入，并将其填充到专门的嵌入表中。在所有测试中，我使用的是 AlloyDB Omni 数据库。它自带 AI 集成，开箱即用，这帮助我构建嵌入、向量索引，并通过将搜索短语转换为向量变量来生成搜索嵌入。

以下是测试中使用的表：

-- ecomm.products 表

demodb=# \d ecomm.products
                              Table "ecomm.products"
         Column | Type | Collation | Nullable | Default
------------------------+------------------------+-----------+----------+---------
 id | bigint | | not null |
 cost | numeric | | |
 category | character varying(255) | | |
 name | character varying(255) | | |
 brand | character varying(255) | | |
 retail_price | numeric | | |
 department | character varying(255) | | |
 sku | character varying(255) | | |
 distribution_center_id | bigint | | |
 product_description | text | | |
 product_image_uri | text | | |
 embedding | vector(768) | | |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "fk_products_distribution_center_23" btree (distribution_center_id)
    "idx_products_brand" btree (brand)
    "idx_products_category" btree (category)
    "idx_products_retail_price" btree (retail_price)
    "idx_products_sku" btree (sku)
    "idx_products_vector" hnsw (embedding vector_cosine_ops)
Foreign-key constraints:
    "fk_products_distribution_center" FOREIGN KEY (distribution_center_id) REFERENCES ecomm.distribution_centers(id)
Referenced by:
    TABLE "ecomm.inventory_items" CONSTRAINT "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
    TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
    TABLE "ecomm.product_reviews" CONSTRAINT "fk_reviews_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id) ON DELETE CASCADE

-- ecomm.product_embeddings 表
demodb=# \d ecomm.product_embeddings
             Table "ecomm.product_embeddings"
   Column | Type | Collation | Nullable | Default
------------+-------------+-----------+----------+---------
 product_id | bigint | | not null |
 embedding | vector(768) | | not null |
Indexes:
    "product_embeddings_pkey" PRIMARY KEY, btree (product_id)
    "idx_product_embeddings_vector" hnsw (embedding vector_cosine_ops)

-- ecomm.inventory_items 表 
demodb=# \d ecomm.inventory_items
                                 Table "ecomm.inventory_items"
             Column | Type | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+---------
 id | bigint | | not null |
 product_id | bigint | | |
 created_at | timestamp without time zone | | |
 sold_at | timestamp without time zone | | |
 cost | numeric | | |
 product_category | character varying(255) | | |
 product_name | character varying(255) | | |
 product_brand | character varying(255) | | |
 product_retail_price | numeric | | |
 product_department | character varying(255) | | |
 product_sku | character varying(255) | | |
 product_distribution_center_id | bigint | | |
Indexes:
    "inventory_items_pkey" PRIMARY KEY, btree (id)
    "fk_inventory_items_distribution_center_8" btree (product_distribution_center_id)
    "fk_inventory_items_product_7" btree (product_id)
Foreign-key constraints:
    "fk_inventory_items_distribution_center" FOREIGN KEY (product_distribution_center_id) REFERENCES ecomm.distribution_centers(id)
    "fk_inventory_items_product" FOREIGN KEY (product_id) REFERENCES ecomm.products(id)
Referenced by:
    TABLE "ecomm.order_items" CONSTRAINT "fk_order_items_inventory_item" FOREIGN KEY (inventory_item_id) REFERENCES ecomm.inventory_items(id)

-- ecomm.product_reviews 表
demodb=# \d ecomm.product_reviews
                                Table "ecomm.product_reviews"
   Column | Type | Collation | Nullable | Default
-------------+--------------------------+-----------+----------+------------------------------
 id | bigint | | not null | generated always as identity
 user_id | bigint | | not null |
 product_id | bigint | | not null |
 rating | integer | | not null |
 review_text | text | | not null |
 created_at | timestamp with time zone | | not null | CURRENT_TIMESTAMP
Indexes:
    "product_reviews_pkey" PRIMARY KEY, btree (id)
    "idx_product_r

ecomm.products 和 ecomm.product_embeddings 通过主键关联，两张表都在嵌入向量上构建了 HNSW 索引。

测试产品语义搜索

我从一个简单的场景开始测试——仅使用 products 表进行余弦语义搜索，然后将其与 products 和 product_embeddings 表的连接进行比较。对于搜索，我将短语 'lightweight waterproof high quality jacket' 转换为向量嵌入，并将其作为 test_vec 变量传入。这样消除了模型响应时间不可预测的问题，使我能够更精确地比较查询执行时间。

-- 在 psql 中设置 test_vec 变量
SELECT (google_ml.embedding(
    model_id => 'text-embedding-005',
    content => 'lightweight waterproof high quality jacket'
)::public.vector(768))::text AS test_vec \gset

在同一个 psql 会话中，我运行了搜索并使用 EXPLAIN ANALYZE 查看执行计划和耗时。每个测试都重复多次。

-- 嵌入存储在 ecomm.products 中
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price
FROM ecomm.products p
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

以下是"内联"嵌入向量搜索的执行计划：

 Limit (cost=1213.90..1240.56 rows=10 width=86) (actual time=1.112..1.200 rows=10.00 loops=1)
   Buffers: shared hit=792
   -> Index Scan using idx_products_vector on products p (cost=1213.90..78826.40 rows=29120 width=86) (actual time=1.110..1.197 rows=10.00 loops=1)
         Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
         Index Searches: 1
         Buffers: shared hit=792
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.107 ms
 Execution Time: 1.224 ms

从执行计划可以看出，查询使用 HNSW 索引来搜索嵌入并按相似度排序。返回结果的总响应时间约为 2.1 ms。响应时间大于执行计划中的纯执行时间，因为它还增加了规划、网络时间以及客户端软件显示结果的开销。

然后我将嵌入放到 ecomm.product_embeddings 表中，并进行了相同的搜索：

-- 嵌入存储在 ecomm.product_embeddings 中
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

以下是使用主键连接 products 和 product_embeddings 表的执行计划：

 Limit (cost=219.82..250.39 rows=10 width=86) (actual time=1.188..1.351 rows=10.00 loops=1)
   Buffers: shared hit=852
   -> Nested Loop (cost=219.82..89223.27 rows=29120 width=86) (actual time=1.187..1.348 rows=10.00 loops=1)
         Buffers: shared hit=852
         -> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.126..1.141 rows=10.00 loops=1)
               Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
               Index Searches: 1
               Buffers: shared hit=752
         -> Index Scan using products_pkey on products p (cost=0.29..1.01 rows=1 width=78) (actual time=0.007..0.007 rows=1.00 loops=10)
               Index Cond: (id = pe.product_id)
               Index Searches: 10
               Buffers: shared hit=50
 Planning:
   Buffers: shared hit=21
 Planning Time: 0.274 ms
 Execution Time: 1.381 ms

带过滤条件的语义搜索

实际生产中很少见到只用一个搜索条件的查询。大多数情况下还会涉及其他参数。我通过添加 category 和 retail_price 列的过滤条件进行了测试：

-- With two filters and embeddings in ecomm.products
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price,
    p.category
FROM ecomm.products p
WHERE p.category = 'Outerwear & Coats'
  AND p.retail_price BETWEEN 30 AND 150
ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

执行计划发生了变化，结果中加入了过滤条件：

 Limit (cost=1213.90..1760.50 rows=10 width=97) (actual time=6.353..6.522 rows=10.00 loops=1)
   Buffers: shared hit=817
   -> Index Scan using idx_products_vector on products p (cost=1213.90..78829.95 rows=1420 width=97) (actual time=6.342..6.509 rows=10.00 loops=1)
         Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
         Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))
         Rows Removed by Filter: 7
         Index Searches: 1
         Buffers: shared hit=799
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.157 ms
 Execution Time: 1.308 ms

响应时间始终在 2.2 ms 左右——与不加过滤条件时几乎相同。

然后我用分离表的嵌入向量执行了相同的查询：

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    p.id,
    p.name,
    p.brand,
    p.retail_price,
    p.category
FROM ecomm.product_embeddings pe
JOIN ecomm.products p ON p.id = pe.product_id
WHERE p.category = 'Outerwear & Coats'
  AND p.retail_price BETWEEN 30 AND 150
ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
LIMIT 10;

执行计划中多了一个过滤步骤：

 Limit (cost=219.82..1346.89 rows=10 width=97) (actual time=1.167..1.370 rows=10.00 loops=1)
   Buffers: shared hit=894
   -> Nested Loop (cost=219.82..89370.85 rows=791 width=97) (actual time=1.166..1.367 rows=10.00 loops=1)
         Buffers: shared hit=894
         -> Index Scan using idx_product_embeddings_vector on product_embeddings pe (cost=219.53..59613.60 rows=29120 width=26) (actual time=1.110..1.153 rows=17.00 loops=1)
               Order By: (embedding <=> '[-0.01255631,...,-0.008388235]'::vector(768))
               Index Searches: 1
               Buffers: shared hit=759
         -> Index Scan using products_pkey on products p (cost=0.29..1.02 rows=1 width=89) (actual time=0.006..0.006 rows=0.59 loops=17)
               Index Cond: (id = pe.product_id)
               Filter: ((retail_price >= '30'::numeric) AND (retail_price <= '150'::numeric) AND ((category)::text = 'Outerwear & Coats'::text))
               Rows Removed by Filter: 0
               Index Searches: 17
               Buffers: shared hit=85
 Planning:
   Buffers: shared hit=21
 Planning Time: 0.347 ms
 Execution Time: 1.406 ms

该查询的响应时间始终在 2.3 到 2.4 ms 之间。同样比嵌入向量在同一张表的查询慢，但差异并不显著。

如果你的数据模式将业务信息的不同部分和属性存储在不同的表中，你需要通过多表连接来获取所需的数据。在测试中我使用了与 ecomm.product_reviews 的连接以及产品评分的聚合：

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    top.id,
    top.name,
    top.brand,
    top.retail_price,
    top.category,
    top.dist,
    COUNT(pr.id) AS review_count,
    COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating
FROM (
    SELECT 
        p.id,
        p.name,
        p.brand,
        p.retail_price,
        p.category,
        (p.embedding <=> :'test_vec'::public.vector(768)) AS dist
    FROM ecomm.products p
    WHERE p.category = 'Outerwear & Coats'
      AND p.retail_price BETWEEN 30 AND 150
    ORDER BY (p.embedding <=> :'test_vec'::public.vector(768))
    LIMIT 10
) top
LEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id
GROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist
ORDER BY top.dist;

该查询始终在 2.6–2.7 ms 内返回结果。然后我修改了查询，使用存储在 ecomm.product_embeddings 表中的嵌入向量：

EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    top.id,
    top.name,
    top.brand,
    top.retail_price,
    top.category,
    top.dist,
    COUNT(pr.id) AS review_count,
    COALESCE(ROUND(AVG(pr.rating), 2), 0) AS avg_rating
FROM (
    SELECT 
        p.id,
        p.name,
        p.brand,
        p.retail_price,
        p.category,
        (pe.embedding <=> :'test_vec'::public.vector(768)) AS dist
    FROM ecomm.product_embeddings pe
    JOIN ecomm.products p ON p.id = pe.product_id
    WHERE p.category = 'Outerwear & Coats'
      AND p.retail_price BETWEEN 30 AND 150
    ORDER BY (pe.embedding <=> :'test_vec'::public.vector(768))
    LIMIT 10
) top
LEFT JOIN ecomm.product_reviews pr ON pr.product_id = top.id
GROUP BY top.id, top.name, top.brand, top.retail_price, top.category, top.dist
ORDER BY top.dist;

差异同样是约 0.2 ms。第二个查询在 2.8–2.9 ms 内返回结果。我略去了这种情况下各自的执行计划——它们更长，在短博客文章中更难阅读，但这里有一张对比图：

对于第二个查询，我额外做了一个 product_embeddings 和 products 表之间的连接（这是预期的），但除此之外它展示了相似的执行路径。

以下是测试结果的汇总表：

+--------------------------+-----------------------+-----------------------+---------------------+
| Test Scenario | Inline Embeddings | Dedicated Table | Overhead / Delta |
+--------------------------+-----------------------+-----------------------+---------------------+
| 1. Pure Semantic Search | Execution: ~1.22 ms | Execution: ~1.38 ms | +0.16 ms (Exec) |
| (LIMIT 10) | Total: ~2.10 ms | Total: ~2.20 ms | +0.10 ms (Total) |
+--------------------------+-----------------------+-----------------------+---------------------+
| 2. Filtered Search | Execution: ~1.31 ms | Execution: ~1.41 ms | +0.10 ms (Exec) |
| (Category + Price) | Total: ~2.20 ms | Total: 2.3 - 2.4 ms | +0.10 - 0.20 ms |
+--------------------------+-----------------------+-----------------------+---------------------+
| 3. Multi-Table Join | | | |
| (Reviews Aggregation) | Total: 2.6 - 2.7 ms | Total: 2.8 - 2.9 ms | +0.20 ms (Total) |
+--------------------------+-----------------------+-----------------------+---------------------+

对比嵌入向量与源数据同表存储和独立专用表存储的查询性能，可以看出性能差异相对较小。在大多数情况下响应时间不超过 0.2 ms。当然，这可能因表结构、数据和具体查询而异。

我建议测试并考虑使用独立嵌入表的布局。这可能在未来的模型维护和模型评估中帮助你——你只需添加另一张表来存放未来的模型，然后使用新生成的嵌入向量运行查询。良好的 schema 设计下，性能开销可以降至最低。根据我的经验，其他因素（如模型响应时间）引入的响应时间变化远比基于主键的嵌入表连接要大得多。