PostgreSQL产品属性过滤性能优化咨询：JSONB与分表抉择

数据库简化设计 #

CREATE TABLE products
(
    id varchar,
    version bigint,
    profile varchar,
    PRIMARY KEY(id, profile, version)
);

CREATE TABLE product_attributes
(
    productId varchar,
    key varchar,
    value varchar,
    profile varchar,
    version bigint,
    PRIMARY KEY(productId, key, value, profile, version)
);

INSERT INTO products (id, version) VALUES ('prod_123', 1);

INSERT INTO product_attributes (productId, key, value, version) 
VALUES ('prod_123', 'country', 'US', 1);
INSERT INTO product_attributes (productId, key, value, version) 
VALUES ('prod_123', 'country', 'MX', 1);
INSERT INTO product_attributes (productId, key, value, version) 
VALUES ('prod_123', 'country', 'ES', 1);
INSERT INTO product_attributes (productId, key, value, version) 
VALUES ('prod_123', 'currency', 'USD', 1);
INSERT INTO product_attributes (productId, key, value, version) 
VALUES ('prod_123', 'startDate', '2023-01-01', 1);

业务背景 #

数据库存储海量数据，每个产品对应20-30个版本，但业务仅需查询最新版本数据。部分属性为集合类型，原分表设计便于在应用层抽象为Map[String, Set[String]]，且新增属性时无需修改表结构。

原查询性能问题 #

最初使用关联查询获取最新版本产品及属性，性能极差：

SELECT e.*
FROM products as e
JOIN (SELECT id, profile, MAX(version) AS version 
      FROM products  
      GROUP BY id, profile) AS vs ON e.id = vs.id 
                                  AND e.profile = vs.profile 
                                  AND e.version = vs.version
JOIN products_attributes oav ON e.id = oav.productId 
                             AND e.version = oav.version;

JSONB迁移后的效果 #

将所有属性迁移至products表的jsonb字段后，批量查询性能显著提升，示例JSON结构如下：

{
  "endDate": [
    "2024-02-20T21:00:00.000Z"
  ],
  "countries": [
    "US", "MX","ES"
  ],
  "type": [
    "RETAIL"
  ],
  "startDate": [
    "2024-02-13T08:00:00.000Z"
  ],
  "categories": [
    "ELECTRONICS"
  ],
  "currency": [
    "USD"
  ],
  "status": [
    "ACTIVE"
  ]
}

当前困境：属性过滤需求 #

需要实现服务端属性过滤，例如筛选国家包含[US, ES, IT] 或 起始日期晚于指定日期的产品，但面临两难：

• JSONB字段过滤复杂度高，性能难保障
• 回退到原分表设计会降低批量查询性能

现有索引配置 #

products_pk(id, profile, version)
products_version_key(version desc)
products_id_profile_version_index(id asc, profile asc, version desc)

product_attributes_pk(product_id, key, value, profile, version)
product_attributes_id_profile_version(product_id asc, profile asc, version desc)

两种查询执行计划对比 #

以下是两种获取最新版本产品数据的查询及其执行计划，第一种查询性能更优：

查询1：MAX聚合关联查询 #

explain(analyze, verbose, buffers, settings)
SELECT e.*
FROM products as e
JOIN (SELECT id, profile, MAX(version) AS version FROM products  GROUP BY id, profile) as vs
    ON e.id = vs.id and e.profile = vs.profile and e.version = vs.version
JOIN product_attribute_values oav on e.id = oav.product_id and e.version = oav.version and e.profile = oav.profile;

执行计划：

Nested Loop  (cost=1.25..59210.34 rows=11 width=885) (actual time=0.051..587.036 rows=125034 loops=1)
"  Output: e.id, e.name, e.description, e.discount_id, e.legacy, e.author, e.datetime, e.profile, e.version, e.deleted, e.start_date, e.end_date, e.type, e.status, e.attributes"
  Inner Unique: true
  Join Filter: (((e.id)::text = (products.id)::text) AND ((e.profile)::text = (products.profile)::text) AND (e.version = (max(products.version))))
  Buffers: shared hit=607499
  ->  Nested Loop  (cost=0.84..59209.24 rows=1 width=100) (actual time=0.038..204.454 rows=125034 loops=1)
"        Output: products.id, products.profile, (max(products.version)), oav.product_id, oav.version, oav.profile"
        Buffers: shared hit=107363
        ->  GroupAggregate  (cost=0.41..23863.19 rows=10721 width=50) (actual time=0.016..39.909 rows=12007 loops=1)
"              Output: products.id, products.profile, max(products.version)"
"              Group Key: products.id, products.profile"
              Buffers: shared hit=23986
              ->  Index Only Scan using products_id_profile_version_index on public.products  (cost=0.41..23453.43 rows=40339 width=50) (actual time=0.007..28.559 rows=42433 loops=1)
"                    Output: products.id, products.profile, products.version"
                    Heap Fetches: 8381
                    Buffers: shared hit=23986
        ->  Index Only Scan using product_attribute_values_product_id_profile_version_index on public.product_attribute_values oav  (cost=0.42..3.29 rows=1 width=50) (actual time=0.008..0.011 rows=10 loops=12007)
"              Output: oav.product_id, oav.profile, oav.version"
              Index Cond: ((oav.product_id = (products.id)::text) AND (oav.profile = (products.profile)::text) AND (oav.version = (max(products.version))))
              Heap Fetches: 41197
              Buffers: shared hit=83377
  ->  Index Scan using products_version_key on public.products e  (cost=0.41..1.09 rows=1 width=885) (actual time=0.002..0.002 rows=1 loops=125034)
"        Output: e.id, e.name, e.description, e.discount_id, e.legacy, e.author, e.datetime, e.profile, e.version, e.deleted, e.start_date, e.end_date, e.type, e.status, e.attributes"
        Index Cond: (e.version = oav.version)
        Filter: (((e.id)::text = (oav.product_id)::text) AND ((e.profile)::text = (oav.profile)::text))
        Buffers: shared hit=500136
"Settings: maintenance_io_concurrency = '1', effective_cache_size = '21585496kB', search_path = 'public'"
Query Identifier: -2874336356913203089
Planning:
  Buffers: shared hit=46
Planning Time: 1.284 ms
Execution Time: 594.111 ms

查询2：ROW_NUMBER窗口函数关联查询 #

explain(analyze, verbose, buffers, settings)
SELECT e.*
FROM products as e
JOIN (SELECT id, profile, deleted, version, row_number() OVER (PARTITION BY id, profile ORDER BY version DESC) as rn
  FROM products) as vs
    ON e.id = vs.id and e.profile = vs.profile and e.version = vs.version
JOIN product_attribute_values oav on e.id = oav.product_id and e.version = oav.version and e.profile = oav.profile;

执行计划：

Gather  (cost=1001.25..53915.82 rows=201 width=885) (actual time=0.466..849.478 rows=460724 loops=1)
"  Output: e.id, e.name, e.description, e.discount_id, e.legacy, e.author, e.datetime, e.profile, e.version, e.deleted, e.start_date, e.end_date, e.type, e.status, e.attributes"
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=2082508
  ->  Nested Loop  (cost=1.25..52895.72 rows=84 width=885) (actual time=0.105..788.148 rows=153575 loops=3)
"        Output: e.id, e.name, e.description, e.discount_id, e.legacy, e.author, e.datetime, e.profile, e.version, e.deleted, e.start_date, e.end_date, e.type, e.status, e.attributes"
        Inner Unique: true
        Join Filter: (((e.id)::text = (products.id)::text) AND ((e.profile)::text = (products.profile)::text) AND (e.version = products.version))
        Buffers: shared hit=2082508
        Worker 0:  actual time=0.138..797.222 rows=156639 loops=1
          Buffers: shared hit=708216
        Worker 1:  actual time=0.127..787.080 rows=152762 loops=1
          Buffers: shared hit=690788
        ->  Nested Loop  (cost=0.84..52837.28 rows=53 width=100) (actual time=0.083..194.444 rows=153575 loops=3)
"              Output: products.id, products.profile, products.version, oav.product_id, oav.version, oav.profile"
              Buffers: shared hit=239610
              Worker 0:  actual time=0.114..199.550 rows=156639 loops=1
                Buffers: shared hit=81659
              Worker 1:  actual time=0.097..193.384 rows=152762 loops=1
                Buffers: shared hit=79739
              ->  Parallel Index Only Scan using products_id_profile_version_index on public.products  (cost=0.41..23218.12 rows=16808 width=59) (actual time=0.032..13.041 rows=14144 loops=3)
"                    Output: products.id, products.profile, NULL::boolean, products.version, NULL::bigint"
                    Heap Fetches: 8381
                    Buffers: shared hit=23986
                    Worker 0:  actual time=0.041..13.810 rows=14433 loops=1
                      Buffers: shared hit=8087
                    Worker 1:  actual time=0.045..12.751 rows=14153 loops=1
                      Buffers: shared hit=7931
              ->  Index Only Scan using product_attribute_values_product_id_profile_version_index on public.product_attribute_values oav  (cost=0.42..1.74 rows=1 width=50) (actual time=0.008..0.010 rows=11 loops=42433)
"                    Output: oav.product_id, oav.profile, oav.version"
                    Index Cond: ((oav.product_id = (products.id)::text) AND (oav.profile = (products.profile)::text) AND (oav.version = products.version))
                    Heap Fetches: 61992
                    Buffers: shared hit=215624
                    Worker 0:  actual time=0.008..0.010 rows=11 loops=14433
                      Buffers: shared hit=73572
                    Worker 1:  actual time=0.008..0.010 rows=11 loops=14153
                      Buffers: shared hit=71808
        ->  Index Scan using products_version_key on public.products e  (cost=0.41..1.09 rows=1 width=885) (actual time=0.003..0.003 rows=1 loops=460724)
"              Output: e.id, e.name, e.description, e.discount_id, e.legacy, e.author, e.datetime, e.profile, e.version, e.deleted, e.start_date, e.end_date, e.type, e.status, e.attributes"
              Index Cond: (e.version = oav.version)
              Filter: (((e.id)::text = (oav.product_id)::text) AND ((e.profile)::text = (oav.profile)::text))
              Buffers: shared hit=1842898
              Worker 0:  actual time=0.003..0.003 rows=1 loops=156639
                Buffers: shared hit=626557
              Worker 1:  actual time=0.003..0.003 rows=1 loops=152762
                Buffers: shared hit=611049
"Settings: maintenance_io_concurrency = '1', effective_cache_size = '21585496kB', search_path = 'public'"
Query Identifier: 49276727828285631
Planning:
  Buffers: shared hit=78
Planning Time: 1.757 ms
Execution Time: 874.783 ms

优化方案建议 #

1. 基于JSONB的优化方案 #

如果保留JSONB结构，针对高频过滤字段创建GIN索引或表达式索引：

• 针对集合类型字段（如countries）创建GIN索引：

  CREATE INDEX products_countries_gin ON products USING GIN ((attributes -> 'countries'));
  

• 针对日期类型字段（如startDate）创建表达式索引，先提取数组首元素并转为日期类型：

  CREATE INDEX products_startdate_idx ON products (( (attributes -> 'startDate' ->> 0)::timestamp ));
  

  过滤查询示例：

  SELECT * FROM products
  JOIN (SELECT id, profile, MAX(version) AS version FROM products GROUP BY id, profile) vs
    ON products.id = vs.id AND products.profile = vs.profile AND products.version = vs.version
  WHERE 
    (attributes -> 'countries') ?| array['US', 'ES', 'IT']
    OR (attributes -> 'startDate' ->> 0)::timestamp > '2023-06-01';
  

2. 分表+物化视图方案 #

如果回退分表设计，创建物化视图预聚合最新版本的产品及其属性：

CREATE MATERIALIZED VIEW latest_product_attributes AS
SELECT 
  p.id, p.profile, p.version,
  jsonb_object_agg(pa.key, jsonb_agg(pa.value)) AS attributes
FROM products p
JOIN (SELECT id, profile, MAX(version) AS version FROM products GROUP BY id, profile) vs
  ON p.id = vs.id AND p.profile = vs.profile AND p.version = vs.version
JOIN product_attributes pa ON p.id = pa.productId AND p.version = pa.version AND p.profile = pa.profile
GROUP BY p.id, p.profile, p.version;

-- 为物化视图创建索引
CREATE INDEX lpa_id_profile_idx ON latest_product_attributes(id, profile);
CREATE INDEX lpa_countries_gin ON latest_product_attributes USING GIN ((attributes -> 'countries'));
CREATE INDEX lpa_startdate_idx ON latest_product_attributes (( (attributes -> 'startDate' ->> 0)::timestamp ));

定期刷新物化视图（如业务低峰期），查询直接基于物化视图，兼顾批量查询与过滤性能。

3. 增量更新的物化视图（PostgreSQL 12+） #

使用REFRESH MATERIALIZED VIEW CONCURRENTLY搭配唯一索引，实现无锁刷新：

CREATE UNIQUE INDEX lpa_unique