带窗口函数的查询优化

还有一个很重要的细节我们还没聊过——带窗口函数的查询性能。毕竟，再优雅的查询如果不优化，也能慢得像只乌龟。今天我们就来搞定这个问题！

窗口函数超级灵活又强大。但灵活性既是礼物，也是性能的潜在威胁。PostgreSQL可不是“魔法”驱动的，它处理数据还是要消耗资源的。要是你把窗口函数用在巨大的表上，你的查询就像原地跑马拉松一样慢。

优化能帮你：

• 加速处理大数据量的查询。
• 减轻数据库的压力。
• 让你的查询对服务器更友好（还有你的同事，如果他们也在用这库！）。

来吧，咱们深入聊聊，怎么让你的查询飞起来，像赛车场上的赛车一样快。

窗口函数的基本工作原理

在我们开始优化之前，得先搞清楚到底是什么让查询变慢。PostgreSQL处理窗口函数的流程大致是这样的：

1. 如果OVER()里有ORDER BY，先对数据排序。
2. 在指定的窗口范围或分组内处理每一行。
3. 为每一行返回结果。

现在想象一下，我们有个sales表，里面有一千万行。如果你的查询没加任何过滤条件，PostgreSQL就得处理这每一行。这已经不是马拉松了，简直是无尽的跑步机。

怎么加速窗口函数？

1. 用索引加速排序

大多数窗口函数会在OVER()里用ORDER BY来控制行的顺序。这意味着PostgreSQL在执行窗口函数前得先排序。

如果你在ORDER BY用到的列（或多列）上建了索引，PostgreSQL排序速度会快很多。

例子

CREATE INDEX idx_sales_date ON sales (sale_date);

现在，如果你写个按sale_date排序的查询，索引就能派上用场：

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

如果没有sale_date的索引，每次执行查询都要做一次很费劲的排序——PostgreSQL会疯狂地找最快的排序方式。

2. 用WHERE做过滤

缩小数据量是优化的关键。如果你不需要处理全部一千万行，只要最近一年的数据——那就用WHERE把范围缩小！

例子

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

这就像你用筛子过滤脏水，只留下有用的信息。

3. 选对窗口frame

用窗口函数做聚合，比如SUM()时，选对窗口frame很重要。如果你用默认frame（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），PostgreSQL会把当前行之前的所有行都算进来。对于大表来说，这很可能不高效。

例子：用ROWS

如果你只需要当前行前面几行，最好明确写出来：

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

这样PostgreSQL每行只处理三行（前两行+当前行），比默认处理上百行高效多了。

4. 减少窗口函数数量

每个窗口函数PostgreSQL都是单独处理的。如果你用了好几个窗口函数，PostgreSQL可能会为每个都单独排序，结果就慢了。但如果窗口参数（比如PARTITION BY和ORDER BY）一样，PostgreSQL能更高效地处理。

例子：用同一个窗口优化

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

这两个函数（SUM()和ROW_NUMBER()）用的是同一个窗口。PostgreSQL只需要排序一次——很赞吧。

5. 表分区

如果你的表太大，可以考虑物理上把它拆成更小的部分。PostgreSQL支持分区表，让数据分布在不同的分区里。这样处理速度会快很多。

创建分区表的例子

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

然后按年份建几个分区：

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

现在，如果你用WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'，PostgreSQL只会查对应的分区。

关于表分区的更多内容，等课程快结束时你就会学到啦 :P

6. 避免多余数据（SELECT只选你需要的）

只选窗口函数和结果需要的列。如果你的窗口函数只用到product_id、sale_date和amount，就别把客户的生物数据全带上。

“节省型”查询例子

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

数据越少，PostgreSQL的活儿就越轻松。

7. 用物化（MATERIALIZED VIEW）

如果你经常做同样的窗口函数计算，可以把结果存到物化视图里。Materialized View把数据存到磁盘，避免每次都重复跑复杂查询。

创建物化视图的例子

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

现在你可以直接查数据：

SELECT * FROM sales_running_total WHERE product_id = 10;

8. 用EXPLAIN和EXPLAIN ANALYZE做查询计划分析

和SQL的其他部分一样，你可以用EXPLAIN或EXPLAIN ANALYZE看看PostgreSQL是怎么执行你的查询的——哪里是瓶颈一目了然。

查询分析例子

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

这个工具会告诉你PostgreSQL在哪儿花了最多时间，你就能对症下药优化瓶颈。

窗口函数是数据分析的利器，但用的时候要小心。想要速度？用索引、加过滤、别忘分区，也别害羞用物化视图。PostgreSQL喜欢你用心设计的查询！