PostgreSQL分区表中简单聚合查询为何远慢于批量行查询？

通常数据库聚合操作比将所有数据返回至应用端计算更快，但在AWS Aurora PostgreSQL的分区表场景下，仅单列AVG(volume)的简单聚合查询首次执行耗时15秒，而同WHERE子句的批量行查询（返回所有行）速度快一个数量级（约1秒）。首次执行聚合查询后数据被缓存，再次执行该查询的速度与批量行查询相当，仅首次运行存在速度差异。

测试查询语句 #

批量行查询 #

SELECT * FROM Sales
WHERE company = 191 
AND sale_date BETWEEN '10-01-2023' AND '10-31-2023'

聚合查询 #

SELECT AVG(volume) FROM Sales
WHERE company = 191 
AND sale_date BETWEEN '10-01-2023' AND '10-31-2023'

表结构与数据库环境 #

数据库为运行在AWS Aurora上的PostgreSQL，Sales表结构如下：

CREATE TABLE Sales (
    sale_date Timestamp(0) WITH TIME ZONE NOT NULL,
    company INTEGER NOT NULL,
    volume  SMALLINT NOT NULL
) PARTITION BY RANGE(sent_on);
CREATE INDEX ON Sales USING BTREE(company,sale_date);

执行计划分析 #

聚合查询执行计划 #

Aggregate (cost=109512.77..109512.78 rows=1 width=32)
Bitmap Heap Scan on sales_202310 sales  (cost=835.01..109440.13 rows=29054 width=30)
  Recheck Cond: ((company = 191) AND (sale_date >= '2023-10-01 00:00:00-04'::timestamp with time zone) AND (sale_date <= '2023-10-31 00:00:00-04'::timestamp with time zone))
  ->  Bitmap Index Scan on sales_202310_company_sale_date_idx  (cost=0.00..827.75 rows=29054 width=0)
        Index Cond: ((company = 191) AND (sale_date >= '2023-10-01 00:00:00-04'::timestamp with time zone) AND (sale_date <= '2023-10-31 00:00:00-04'::timestamp with time zone))

批量行查询执行计划 #

批量行查询的执行计划与聚合查询完全一致（成本也相同），仅缺少首行的Aggregate步骤。

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