PostgreSQL 19一个非常期待的版本，尤其在聚合计算方面的“超级”改进

❝

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共3300人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群近400 9群 200+，开10群PolarDB专业学习群100+)

最近看到一篇说PostgreSQL的帖子关于PostgreSQL 19在聚合方面的优化，主要在PG19引入了一个优化器层面的重大的突破，对于GROUP by + join查询，优化器可以先聚合，再JOIN，而不是过去延续很多年的先JOIN在聚合。

并提出者是划时代的优化方式，不需要优化参数，不需要HINT，不需要参数，不需要改写SQL。 原文 https://www.cybertec-postgresql.com/en/super-fast-aggregations-in-postgresql-19/

t_product
→ GROUP BY (category_id, color_id)
→ 只剩下几行
→ 再 JOIN t_category / t_color

他的意思是，数据库查询引擎操作中，将数据聚合后，再进行JOIN的操作。

这里我们拿PG18.1做验证，验证PG18.1并未使用这项技术。

test=# 
test=# DROP TABLE IF EXISTS t_product;
NOTICE:  table "t_product" does not exist, skipping
DROP TABLE
test=# DROP TABLE IF EXISTS t_category;
NOTICE:  table "t_category" does not exist, skipping
DROP TABLE
test=# DROP TABLE IF EXISTS t_color;
NOTICE:  table "t_color" does not exist, skipping
DROP TABLE
test=# 
test=# CREATE TABLE t_category (
test(#     category_id   int PRIMARY KEY,
test(#     category_name text
test(# );
2, 'Car'),
    (3, 'Bike');

CREATE TABLE t_color (
    color_id   int PRIMARY KEY,
    color_name text
);

INSERT INTO t_color VALUES
    (0, 'Red'),
    (1, 'Green'),
    (2, 'Yellow'),
    (3, 'Blue');

CREATE TABLE t_product (
    category_id int REFERCREATE TABLE
test=# 
test=# INSERT INTO t_category VALUES
test-#     (0, 'Shoes'),
test-#     (1, 'Shirts'),
test-#     (2, 'Car'),
test-#     (3, 'Bike');
INSERT 0 4
test=# 
test=# CREATE TABLE t_color (
test(#     color_id   int PRIMARY KEY,
test(#     color_name text
test(# );
CREATE TABLE
test=# 
test=# INSERT INTO t_color VALUES
test-#     (0, 'Red'),
test-#     (1, 'Green'),
test-#     (2, 'Yellow'),
test-#     (3, 'Blue');
INSERT 0 4
test=# 
test=# CREATE TABLE t_product (
test(#     category_id int REFERENCES t_category(category_id),
test(#     color_id    int REFERENCES t_color(color_id),
test(#     payload     text
test(# );
FFERS)
SELECT
    c.category_name,
    col.color_name,
    count(*)
FROM
    t_product p
JOIN t_category c ON p.category_id = c.category_id
JOIN t_color col   ON p.color_id = col.color_id
GROUP BY
    1, 2;

CREATE TABLE
test=# 
test=# 
test=# INSERT INTO t_product
test-# SELECT
test-#     id % 4,
test-#     (id * random())::int % 4,
test-#     md5(id::text)
test-# FROM generate_series(1, 200000) AS id;
INSERT 0 200000
test=# 
test=# EXPLAIN (ANALYZE, BUFFERS)
test-# SELECT
test-#     c.category_name,
test-#     col.color_name,
test-#     count(*)
test-# FROM
test-#     t_product p
test-# JOIN t_category c ON p.category_id = c.category_id
test-# JOIN t_color col   ON p.color_id = col.color_id
test-# GROUP BY
test-#     1, 2;

                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=7056.19..7456.19 rows=40000 width=72) (actual time=5820.558..5820.783 rows=16.00 loops=1)
   Group Key: c.category_name, col.color_name
   Batches: 1  Memory Usage: 1057kB
   Buffers: shared hit=1872
   ->  Hash Join  (cost=77.15..5373.19 rows=224400 width=64) (actual time=0.161..4771.507 rows=200000.00 loops=1)
         Hash Cond: (p.color_id = col.color_id)
         Buffers: shared hit=1872
         ->  Hash Join  (cost=38.58..4743.59 rows=224400 width=36) (actual time=0.082..2854.191 rows=200000.00 loops=1)
               Hash Cond: (p.category_id = c.category_id)
               Buffers: shared hit=1871
               ->  Seq Scan on t_product p  (cost=0.00..4114.00 rows=224400 width=8) (actual time=0.011..934.061 rows=200000.00 loops=1)
                     Buffers: shared hit=1870
               ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.054..0.068 rows=4.00 loops=1)
                     Buckets: 2048  Batches: 1  Memory Usage: 17kB
                     Buffers: shared hit=1
                     ->  Seq Scan on t_category c  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.008..0.031 rows=4.00 loops=1)
                           Buffers: shared hit=1
         ->  Hash  (cost=22.70..22.70 rows=1270 width=36) (actual time=0.062..0.075 rows=4.00 loops=1)
               Buckets: 2048  Batches: 1  Memory Usage: 17kB
               Buffers: shared hit=1
               ->  Seq Scan on t_color col  (cost=0.00..22.70 rows=1270 width=36) (actual time=0.015..0.037 rows=4.00 loops=1)
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=88
 Planning Time: 0.452 ms
 Execution Time: 5820.996 ms
(26 rows)

PG19的执行计划

PgSQL
                                              QUERY PLAN                                                
------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=13167.09..13170.53 rows=16 width=18)
   Group Key: c1.category_name, c2.color_name
   ->  Gather Merge  (cost=13167.09..13170.17 rows=27 width=18)
        Workers Planned: 1
        ->  Sort  (cost=12167.08..12167.12 rows=16 width=18)
             Sort Key: c1.category_name, c2.color_name
             ->  Partial HashAggregate  (cost=12166.60..12166.76 rows=16 width=18)
                  Group Key: c1.category_name, c2.color_name
                  ->  Hash Join  (cost=2.49..8637.19 rows=470588 width=10)
                       Hash Cond: (p.color_id = c2.color_id)
                       ->  Parallel Seq Scan on t_product p  (cost=0.00..3046.47 rows=117647 width=4)
                       ->  Hash  (cost=2.29..2.29 rows=16 width=14)
                            ->  Nested Loop  (cost=0.00..2.29 rows=16 width=14)
                                 ->  Seq Scan on t_category c1  (cost=0.00..1.04 rows=4 width=5)
                                 ->  Materialize  (cost=0.00..1.06 rows=4 width=9)
                                      ->  Seq Scan on t_color c2  (cost=0.00..1.04 rows=4 width=9)
(16 rows)

我们可以对比上面的两个执行的计划来去看看PG19在聚合查询中优化的点。

文章中也指出，优化后，PG19在执行语句只需要16.8毫秒，而老的方式需要95.3毫秒。

所以PG得最近PG18 PG19(未到生产)的PG升级是值得让人期待的。

同时注意文章中也提到了CUBD无法使用这个优化，原因是CUBE需要多个维度的优化，不能在一个层进行一次聚合，必须在JOIN后再进行统一聚合计算。

MongoDB “升级项目” 大型连续剧(3)-- 自动校对代码与注意事项

MongoDB “升级项目” 大型连续剧(2)-- 到底谁是"der"

MongoDB “升级项目”  大型连续剧(1)-- 可“生”可不升

MongoDB  大俗大雅，上来问分片真三俗 -- 4 分什么分

MongoDB 大俗大雅，高端知识讲“庸俗” --3 奇葩数据更新方法

MongoDB 学习建模与设计思路--统计数据更新案例

MongoDB  大俗大雅，高端的知识讲“通俗” -- 2 嵌套和引用

MongoDB  大俗大雅，高端的知识讲“低俗” -- 1 什么叫多模

MongoDB 合作考试报销活动 贴附属，MongoDB基础知识速通

MongoDB 年底活动，免费考试名额 7个公众号获得

MongoDB 使用网上妙招，直接DOWN机---清理表碎片导致的灾祸 (送书活动结束)

MongoDB 2023年度纽约 MongoDB 年度大会话题 -- MongoDB 数据模式与建模