PostgreSQL按产品/美国州销售百分位数查询优化求助

我正在对1500万行的venue_product_depletions_locations表，按产品/美国州分组计算销售百分位数。当查询包含大量dma地点（IN子句含大量值）时，查询速度极慢：

• 完整查询耗时约21秒
• 移除percentile_cont相关计算后，耗时降至约7.5秒
  查询计划显示存在一个开销极高的外部排序操作，目前找不到优化方向。

查询语句 #

SELECT 'us_state' AS type, us_state AS type_id, product, COUNT(*) AS count, MIN(depletions) AS min, MAX(depletions) AS max, SUM(depletions) AS sum, MIN(start_date) AS start_date, MAX(end_date) AS end_date, 
  percentile_cont(0.2) WITHIN GROUP (ORDER BY depletions) AS p20,
  percentile_cont(0.4) WITHIN GROUP (ORDER BY depletions) AS p40,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY depletions) AS p50,
  percentile_cont(0.6) WITHIN GROUP (ORDER BY depletions) AS p60,
  percentile_cont(0.8) WITHIN GROUP (ORDER BY depletions) AS p80 
FROM "venue_product_depletions_locations" 
WHERE "account_id" = 6352 AND "dma" IN ('862','716','532','551','521','622','752','575','604','881','576','555','801','825','569','641','506','588','531','619','855','636','790','623','523','567','512','519','807','819','581','508','600','751','839','691','518','658','693','630','510','635','789','669','661','709','770','673','705','736','500','644','544','866','507','821','520','556','686','592','89','522','560','557','676','573','529','515','516','570','602','679','634','540','541','678','534','828','671','613','649','771','640','757','659','550','633','698','528','504','804','625','548','559','637','535','618','527','611','517','617','670','561','571','813','65','765','509','514','566','753','538','650','820','662','546','539','803','563','582','810','501','616','773','758','609','524','530','584','702','533','656','505','511','547') AND (us_state IS NOT NULL) 
GROUP BY product, us_state

已创建索引 #

• account_id + dma 组合索引
• product + us_state 组合索引
• product + us_state + depletions 组合索引

查询计划（EXPLAIN ANALYZE, BUFFERS） #

GroupAggregate  (cost=1110518.81..1330560.79 rows=44443 width=137) (actual time=17829.623..26151.760 rows=5799 loops=1)
  Group Key: product, us_state
  Buffers: shared hit=3279444, temp read=44945 written=44950
  ->  Sort  (cost=1110518.81..1126164.67 rows=6258343 width=41) (actual time=17829.559..21253.068 rows=6220661 loops=1)
        Sort Key: product, us_state
        Sort Method: external merge  Disk: 359560kB
        Buffers: shared hit=3279444, temp read=44945 written=44950
        ->  Index Scan using account_id_dma_index on venue_product_depletions_locations  (cost=0.43..291274.59 rows=6258343 width=41) (actual time=0.207..4629.167 rows=6220661 loops=1)
              Index Cond: ((account_id = 6352) AND ((dma)::text = ANY ('{862,716,532,551,521,622,752,575,604,881,576,555,801,825,569,641,506,588,531,619,855,636,790,623,523,567,512,519,807,819,581,508,600,751,839,691,518,658,693,630,510,635,789,669,661,709,770,673,705,736,500,644,544,866,507,821,520,556,686,"592'',89",522,560,557,676,573,529,515,516,570,602,679,634,540,541,678,534,828,671,613,649,771,640,757,659,550,633,698,528,504,804,625,548,559,637,535,618,527,611,517,617,670,561,571,"813'',65",765,509,514,566,753,538,650,820,662,546,539,803,563,582,810,501,616,773,758,609,524,530,584,702,533,656,505,511,547}'::text[])))
              Filter: (us_state IS NOT NULL)
              Rows Removed by Filter: 10187
              Buffers: shared hit=3279444
Planning Time: 0.870 ms
Execution Time: 26200.913 ms

优化建议 #

1. 创建覆盖式复合索引，消除排序开销 #

当前查询最大的性能瓶颈是基于product、us_state的外部磁盘合并排序，耗时占总执行时间的50%以上。可创建一个包含过滤条件、分组键、查询字段的复合索引，让数据库直接按分组顺序读取数据，完全跳过排序步骤：

-- PostgreSQL 11+ 支持INCLUDE子句，减少索引体积
CREATE INDEX idx_account_dma_product_us_state_covering ON venue_product_depletions_locations (account_id, dma, product, us_state) INCLUDE (depletions, start_date, end_date);

-- 低版本PostgreSQL用全字段索引
CREATE INDEX idx_account_dma_product_us_state_full ON venue_product_depletions_locations (account_id, dma, product, us_state, depletions, start_date, end_date);

该索引优先匹配WHERE条件的account_id和dma，随后按分组键product、us_state排序，最后包含查询所需的所有字段，实现索引-only scan，彻底避免磁盘排序和表数据读取。

2. 优化百分位数计算逻辑 #

percentile_cont需要对每个分组内的depletions排序并执行线性插值，是主要性能消耗点：

• 若业务允许，改用percentile_disc替代：该函数计算离散百分位数，无需插值，性能显著优于percentile_cont
• 确保使用PostgreSQL 12+版本：该版本对分组聚合场景下的百分位数计算做了针对性优化

3. 调高work_mem，避免磁盘排序 #

当前排序使用了外部磁盘合并，说明work_mem配置不足以在内存中完成排序。可临时调高当前会话的work_mem：

SET work_mem = '512MB';

若该查询为高频执行，可在postgresql.conf中适度调高全局work_mem（需根据服务器内存总量调整，避免内存溢出）。

4. 替换IN子句为临时表JOIN #

大量值的IN子句会增加查询解析和匹配开销，可将目标dma列表存入临时表，改用JOIN方式过滤：

CREATE TEMP TABLE target_dmas (dma text PRIMARY KEY);
INSERT INTO target_dmas VALUES ('862'), ('716'), -- 所有目标dma值
                               ('532'), ('551'), ...;

SELECT 'us_state' AS type, v.us_state AS type_id, v.product, COUNT(*) AS count, 
       MIN(v.depletions) AS min, MAX(v.depletions) AS max, SUM(v.depletions) AS sum,
       MIN(v.start_date) AS start_date, MAX(v.end_date) AS end_date,
       percentile_cont(0.2) WITHIN GROUP (ORDER BY v.depletions) AS p20,
       percentile_cont(0.4) WITHIN GROUP (ORDER BY v.depletions) AS p40,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY v.depletions) AS p50,
       percentile_cont(0.6) WITHIN GROUP (ORDER BY v.depletions) AS p60,
       percentile_cont(0.8) WITHIN GROUP (ORDER BY v.depletions) AS p80 
FROM "venue_product_depletions_locations" v
JOIN target_dmas t ON v.dma = t.dma
WHERE v.account_id = 6352 AND v.us_state IS NOT NULL
GROUP BY v.product, v.us_state;

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