PostgreSQL组合查询性能优化：解决慢查询问题

测试数据 #

DROP TABLE IF EXISTS expectation;
DROP TABLE IF EXISTS actual;

CREATE TABLE expectation (
  set_id int NOT NULL,
  value int NOT NULL
);
INSERT INTO expectation (set_id, value) 
  SELECT floor(random() * 1000)::int AS set_id, floor(random() * 1000)::int AS value FROM generate_series(1, 2000);

CREATE TABLE actual (
  user_id int NOT NULL,
  value int NOT NULL
);
INSERT INTO actual (user_id, value) 
  SELECT floor(random() * 200000)::int AS user_id, floor(random() * 1000)::int AS value FROM generate_series(1, 1000000);

表结构说明 #

expectation表存储一组value及其对应的set_id，一个set_id可对应多个value：

# SELECT * FROM "expectation" ORDER BY "set_id" LIMIT 10;
 set_id | value 
--------+-------
      0 |   641
      1 |   560
      2 |   872
      3 |    56
      3 |   608
      4 |   652
      5 |   439
      5 |   145
      6 |   510
      6 |   515

actual表存储用户对应的value集合，一个user_id也可对应多个value：

# SELECT * FROM "actual" ORDER BY "user_id" LIMIT 10;
 user_id | value 
---------+-------
       0 |   128
       0 |   177
       0 |   591
       0 |   219
       0 |   785
       0 |   837
       0 |   782
       1 |   502
       1 |   521
       1 |   210

需求与问题 #

需要找出所有用户及其匹配的set_id，匹配条件为用户拥有该set_id对应的所有value（可拥有更多value）。

我写的查询语句能返回正确结果，但耗时过长，测试数据下约18秒：

# WITH
  expected AS (SELECT set_id, array_agg(value) as values FROM expectation GROUP BY set_id),
  gotten AS (SELECT user_id, array_agg(value) as values FROM actual GROUP BY user_id)
SELECT user_id, array_agg(set_id) FROM gotten
INNER JOIN expected ON expected.values <@ gotten.values
GROUP BY user_id LIMIT 10;
 user_id |        array_agg         
---------+-------------------------
       0 | {525}
       1 | {175,840}
       2 | {336}
       3 | {98,260}
       7 | {416}
       8 | {2,251,261,352,682,808}
       9 | {971}
      10 | {163,485}
      11 | {793}
      12 | {157,332,539,582,617}
(10 rows)
Time: 18960.143 ms (00:18.960)

已尝试方案 #

• 因为聚合逻辑限制，加LIMIT没法缩短查询耗时；
• 物化索引视图可能有用，但业务中数据变更频繁，暂时不适用；
• 查询计划看起来合理，但数组包含（Join Filter: ((array_agg(expectation.value)) <@ (array_agg(actual.value)))）这一步很慢，没找到更高效的校验方式。

查询计划如下：

GroupAggregate  (cost=127896.00..2339483.85 rows=200 width=36) (actual time=502.126..23381.752 rows=139712 loops=1)
  Group Key: actual.user_id
  ->  Nested Loop  (cost=127896.00..2335820.38 rows=732194 width=8) (actual time=501.614..23332.035 rows=277930 loops=1)
        Join Filter: ((array_agg(expectation.value)) <@ (array_agg(actual.value)))
        Rows Removed by Join Filter: 171755568
        ->  GroupAggregate  (cost=127757.34..137371.07 rows=169098 width=36) (actual time=500.499..762.447 rows=198653 loops=1)
              Group Key: actual.user_id
              ->  Sort  (cost=127757.34..130257.34 rows=1000000 width=8) (actual time=329.909..476.859 rows=1000000 loops=1)
                    Sort Key: actual.user_id
                    Sort Method: external merge  Disk: 17696kB
                    ->  Seq Scan on actual  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.014..41.334 rows=1000000 loops=1)
        ->  Materialize  (cost=138.66..177.47 rows=866 width=36) (actual time=0.000..0.019 rows=866 loops=198653)
              ->  GroupAggregate  (cost=138.66..164.48 rows=866 width=36) (actual time=0.551..1.164 rows=866 loops=1)
                    Group Key: expectation.set_id
                    ->  Sort  (cost=138.66..143.66 rows=2000 width=8) (actual time=0.538..0.652 rows=2000 loops=1)
                          Sort Key: expectation.set_id
                          Sort Method: quicksort  Memory: 142kB
                          ->  Seq Scan on expectation  (cost=0.00..29.00 rows=2000 width=8) (actual time=0.020..0.146 rows=2000 loops=1)
Planning Time: 0.243 ms
JIT:
  Functions: 17
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.831 ms, Inlining 43.440 ms, Optimization 61.965 ms, Emission 64.892 ms, Total 172.129 ms
Execution Time: 23406.950 ms

优化方案 #

1. 重构查询逻辑，避免全量嵌套循环 #

原方案的核心问题是：将每个用户的value数组和每个set_id的value数组做包含校验，产生了1.7亿次无效过滤，这是性能瓶颈。可以换用统计匹配覆盖率的思路：

WITH set_value_counts AS (
  -- 预计算每个set_id对应的value总数
  SELECT set_id, COUNT(DISTINCT value) AS total_values
  FROM expectation
  GROUP BY set_id
)
SELECT 
  a.user_id,
  array_agg(DISTINCT e.set_id) AS matched_set_ids
FROM actual a
JOIN expectation e ON a.value = e.value
GROUP BY a.user_id, e.set_id
-- 校验用户拥有的当前set_id下的value数是否等于该set_id的总value数
HAVING COUNT(DISTINCT a.value) = (SELECT total_values FROM set_value_counts WHERE set_id = e.set_id)
GROUP BY a.user_id
LIMIT 10;

2. 添加复合索引加速关联与分组 #

给两个表添加针对性的复合索引，大幅提升关联和分组的效率：

-- 加速expectation表的分组和关联
CREATE INDEX idx_expectation_set_value ON expectation(set_id, value);
-- 加速actual表的分组和关联
CREATE INDEX idx_actual_user_value ON actual(user_id, value);

3. 优化数组方案（如果坚持使用） #

如果一定要保留数组包含的逻辑，可以通过预生成临时表并添加GIN索引来加速：

-- 预生成set_id对应的value数组，并创建GIN索引
CREATE TEMPORARY TABLE expected_temp AS
SELECT set_id, array_agg(value) AS values 
FROM expectation 
GROUP BY set_id;
CREATE INDEX idx_expected_values_gin ON expected_temp USING GIN(values);

-- 利用GIN索引加速数组包含判断
SELECT 
  a.user_id,
  array_agg(e.set_id) AS matched_set_ids
FROM (
  SELECT user_id, array_agg(value) AS values 
  FROM actual 
  GROUP BY user_id
) a
JOIN expected_temp e ON e.values <@ a.values
GROUP BY a.user_id
LIMIT 10;

4. 关闭JIT优化（可选） #

从查询计划看JIT耗时约172ms，虽然占比不高，但如果你的服务器CPU资源紧张，可以尝试关闭JIT：

SET jit = off;

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