PostgreSQL 查询慢？是不是忘了优化 GROUP BY、ORDER BY 和窗口函数？

kknone
323 阅读16分钟

GROUP BY的原理与优化

GROUP BY的基本概念

GROUP BY是PostgreSQL中用于分组聚合的核心子句，它将表中具有相同值的行归为一组，然后对每组计算聚合函数（如sum、avg、count）。例如，我们有一张记录商品销售的test1表：

CREATE TABLE test1 (x TEXT, y INT);
INSERT INTO test1 VALUES ('a', 3), ('c', 2), ('b', 5), ('a', 1);

若要计算每个x对应的y之和，可使用：

SELECT x, sum(y) FROM test1 GROUP BY x;

结果会按x分组，返回每组的求和结果：

 x | sum
---+-----
 a |   4
 b |   5
 c |   2

关键规则：SELECT列表中的列要么在GROUP BY中（分组键），要么被聚合函数包裹（否则会因“非分组列无法确定唯一值”报错）。

功能依赖与GROUP BY简化

PostgreSQL支持功能依赖（Functional Dependency）优化：若GROUP BY的列是表的主键或唯一约束，则其他依赖于该列的列（即主键能唯一确定的列）无需加入GROUP BY。例如，products表的product_id是主键，name和price依赖于product_id：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL
);
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    units INT NOT NULL,
    sale_date DATE NOT NULL
);

查询每个产品的总销售额时，无需将name和price加入GROUP BY：

SELECT 
    p.product_id, 
    p.name,  -- 依赖于product_id，无需GROUP BY
    sum(s.units * p.price) AS total_sales
FROM products p
LEFT JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id;  -- 仅需分组主键

这一优化减少了分组的复杂度，因为PostgreSQL知道主键能唯一确定其他列的值，无需额外分组检查。

GROUPING SETS、CUBE与ROLLUP的高效聚合

当需要生成多个分组的聚合结果时（如同时按“品牌”“尺寸”“总合计”分组），多次查询会重复扫描数据，而GROUPING SETS、CUBE、ROLLUP能一次性生成多组聚合，大幅提升效率。

以items_sold表为例：

CREATE TABLE items_sold (brand TEXT, size TEXT, sales INT);
INSERT INTO items_sold VALUES 
('Foo', 'L', 10), ('Foo', 'M', 20), ('Bar', 'M', 15), ('Bar', 'L', 5);

若要同时按brand、size和“总合计”分组，使用GROUPING SETS：

SELECT brand, size, sum(sales) 
FROM items_sold 
GROUP BY GROUPING SETS ((brand), (size), ());  -- 三组分组

结果会返回三组聚合：

 brand | size | sum
-------+------+-----
 Foo   |      |  30  -- 按brand分组
 Bar   |      |  20
       | L    |  15  -- 按size分组
       | M    |  35
       |      |  50  -- 总合计（空分组）
  • CUBE(a, b)：生成a、b、a+b、空分组的所有组合（即“立方体”聚合）；
  • ROLLUP(a, b)：生成a+b、a、空分组的层级聚合（如“省份+城市”“省份”“全国”）。

这些扩展避免了多次全表扫描，是处理多维度分析的高效工具。

HAVING与WHERE的区别

HAVING用于过滤分组后的结果，而WHERE用于过滤原始行。例如：

-- 过滤“sum(y) > 3”的分组
SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;

-- 过滤“x < 'c'”的原始行，再分组
SELECT x, sum(y) FROM test1 WHERE x < 'c' GROUP BY x;

注意：HAVING可以使用聚合函数，WHERE不能（WHERE过滤的是未分组的行，聚合函数尚未计算）。

ORDER BY的优化策略

索引与排序的关系

ORDER BY的性能核心是是否能利用索引避免排序。PostgreSQL的索引是有序的（如B-tree索引），若ORDER BY的列顺序与索引完全一致（包括ASC/DESC），则可直接通过索引获取有序数据，跳过Sort操作。

例如，orders表的order_date列有索引：

CREATE INDEX idx_orders_order_date ON orders(order_date DESC);

查询最新订单时，执行计划会使用Index Scan而非Seq Scan + Sort：

SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

执行计划示例：

Limit  (cost=0.29..1.04 rows=10 width=44)
  ->  Index Scan using idx_orders_order_date on orders  (cost=0.29..74.29 rows=1000 width=44)

若ORDER BY的列没有索引，PostgreSQL会进行内存排序（in-memory sort），若数据量超过work_mem（默认4MB），则会写入临时文件（外部排序），性能骤降。

Top-N查询的优化

Top-N查询（如“取最新10条数据”）是ORDER BY的常见场景，PostgreSQL会使用Top-N Heapsort优化：只需维护一个大小为N的堆（如10），遍历数据时不断替换堆中最小的元素，无需排序整个结果集。

例如，查询销量最高的5个产品：

SELECT product_id, sum(units) AS total_units
FROM sales
GROUP BY product_id
ORDER BY total_units DESC
LIMIT 5;

若sum(units)无法用索引，Top-N Heapsort仍比全排序高效——因为堆的大小远小于总数据量。

内存与外部排序

work_mem参数控制PostgreSQL用于排序、哈希等操作的内存上限。若排序数据量超过work_mem，会触发外部排序（将数据分成多个块，每个块内存排序后写入临时文件，最后合并块），性能下降明显。

优化方法：

  1. 临时调整work_mem（会话级别，不影响全局）： 
    SET work_mem = '64MB';  -- 将排序内存提升至64MB
  2. 创建合适的索引：避免排序（推荐）。
  3. 减少排序数据量：使用WHERE过滤不必要的行，或LIMIT限制结果数。

窗口函数OVER()的效率提升

窗口函数的执行时机

窗口函数（如row_number()、sum() OVER()）用于计算每行的“窗口内”聚合（如累计销售额、排名），其执行顺序在GROUP BY之后、SELECT之前：

FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY

例如，计算每个产品的累计销售额：

SELECT 
    s.product_id,
    s.sale_date,
    s.units * p.price AS daily_sales,
    sum(s.units * p.price) OVER (
        PARTITION BY s.product_id  -- 按产品分区
        ORDER BY s.sale_date       -- 按日期排序
    ) AS running_total
FROM sales s
JOIN products p ON s.product_id = p.product_id;

PARTITION BY将数据分成多个“窗口”（如每个产品一组），ORDER BY定义窗口内的行顺序，sum() OVER()计算窗口内的累计和。

窗口定义的索引优化

窗口函数的性能取决于窗口内数据的有序性。若PARTITION BY和ORDER BY的列有复合索引，PostgreSQL可快速分区并排序，避免额外的Sort操作。

例如，为sales表创建product_id + sale_date的复合索引：

CREATE INDEX idx_sales_product_date ON sales(product_id, sale_date);

上述累计销售额查询的执行计划会跳过排序，直接使用索引获取有序数据：

WindowAgg  (cost=0.56..1.71 rows=100 width=56)
  ->  Index Scan using idx_sales_product_date on sales s  (cost=0.29..1.21 rows=100 width=28)
        Join Filter: (s.product_id = p.product_id)

窗口复用与合并

若多个窗口函数使用完全相同的PARTITION BY和ORDER BY，可通过WINDOW子句定义窗口并复用，减少重复计算。

例如，同时计算累计销售额和排名：

SELECT 
    product_id,
    sale_date,
    daily_sales,
    sum(daily_sales) OVER w AS running_total,  -- 复用窗口w
    row_number() OVER w AS rank               -- 复用窗口w
FROM (
    SELECT 
        s.product_id,
        s.sale_date,
        s.units * p.price AS daily_sales
    FROM sales s
    JOIN products p ON s.product_id = p.product_id
) AS subquery
WINDOW w AS (PARTITION BY product_id ORDER BY sale_date);  -- 定义窗口w

WINDOW子句将窗口逻辑集中定义，PostgreSQL只需计算一次窗口，提升效率。

Frame Clause的选择

窗口函数的Frame Clause（如ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）定义窗口内的行范围，不同类型的Frame性能差异显著：

  • ROWS Frame：基于行位置（如“前所有行到当前行”），计算最快（直接按顺序累加）。
  • RANGE Frame：基于值范围（如“所有sale_date ≤ 当前行的行”），需比较值，性能较慢。

例如，累计销售额应使用ROWS Frame（默认即ROWS UNBOUNDED PRECEDING）：

sum(daily_sales) OVER (PARTITION BY product_id ORDER BY sale_date)  -- 默认ROWS

若需按值范围计算（如“最近7天的销售额”），则需使用RANGE Frame，但需注意性能：

sum(daily_sales) OVER (
    PARTITION BY product_id 
    ORDER BY sale_date 
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
)

课后Quiz

问题1：为什么GROUP BY主键列时，不需要将其他依赖列加入GROUP BY子句？

答案：因为主键列具有功能依赖性——主键的值唯一确定了其他列的值（如product_id确定name和price）。PostgreSQL支持这一优化，允许SELECT列表中包含依赖列，无需将它们加入GROUP BY，减少分组的复杂度和计算量。

问题2：如何优化包含ORDER BY和LIMIT的Top-N查询？

答案：

  1. 创建匹配的索引：使ORDER BY的列顺序与索引完全一致（包括ASC/DESC），直接通过索引获取有序数据。
  2. 利用Top-N Heapsort：LIMIT的行数越小，HeapSort的优势越明显（无需排序全表）。
  3. 避免表达式排序：若ORDER BY使用表达式（如LOWER(name)），需为表达式创建索引（如CREATE INDEX idx_name_lower ON users(LOWER(name))）。

问题3：窗口函数的PARTITION BY和ORDER BY如何影响性能？

答案：

  • PARTITION BY的列若有索引，可快速将数据分成不同窗口，避免额外的分组操作。
  • ORDER BY的列若有索引，可直接获取窗口内的有序数据，跳过Sort操作。
  • 复合索引（PARTITION BY列 + ORDER BY列）能最大化窗口函数的性能，因为无需任何额外排序或分组。

常见报错解决方案

报错1：ERROR: column "table.column" must appear in the GROUP BY clause or be used in an aggregate function

原因：SELECT列表中的列既不在GROUP BY中，也未被聚合函数包裹（违反分组规则）。
解决方法：

  1. 将列加入GROUP BY（如GROUP BY x, y）。
  2. 对列使用聚合函数（如sum(y)）。
  3. 利用功能依赖（若列依赖于GROUP BY的主键，无需加入）。
    预防建议：分组时尽量只包含必要的列，优先使用主键作为分组键。

报错2：ERROR: window function requires an OVER clause

原因：使用了窗口函数（如row_number()）但未指定OVER子句（窗口函数必须通过OVER定义窗口）。
解决方法：为窗口函数添加OVER子句，指定PARTITION BY和ORDER BY（如row_number() OVER (PARTITION BY product_id ORDER BY sale_date)）。
预防建议：编写窗口函数时，确保每个函数都有对应的OVER子句。

报错3：ERROR: could not sort because work_mem exceeded

原因：排序数据量超过work_mem，触发外部排序（写入临时文件）。
解决方法：

  1. 临时调整work_mem：SET work_mem = '64MB'（会话级别，不影响全局）。
  2. 创建索引：避免排序（优先选择）。
  3. 减少排序数据量：使用WHERE过滤或LIMIT限制结果数。
    预防建议：根据查询需求合理调整work_mem，避免不必要的排序。

参考链接

参考链接：www.postgresql.org/docs/17/que…
参考链接：www.postgresql.org/docs/17/que…
参考链接：www.postgresql.org/docs/17/ind…
参考链接：www.postgresql.org/docs/17/sql…
参考链接：www.postgresql.org/docs/17/run…

往期文章归档
免费好用的热门在线工具
avatar
文章
阅读
粉丝