Skip to content

Instantly share code, notes, and snippets.

@mgartner

mgartner/agg.sql

Last active December 11, 2025 20:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save mgartner/8521822c9ee7854a8946b1236db6d02d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save mgartner/8521822c9ee7854a8946b1236db6d02d to your computer and use it in GitHub Desktop.
Download ZIP
trying out the Postgres 19 optimization described in https://www.cybertec-postgresql.com/en/super-fast-aggregations-in-postgresql-19/
Raw
agg.sql
CREATE TABLE categories (
id INT PRIMARY KEY,
name TEXT
);
INSERT INTO categories VALUES
(0, 'Shoes'),
(1, 'Shirts'),
(2, 'Car'),
(3, 'Bike');
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT,
category_id INT REFERENCES categories(id)
);
INSERT INTO products (id, name, category_id)
SELECT
i,
'Product ' || i,
(random() * 3)::INT -- valid category_id: 0–3
FROM generate_series(1, 100000) AS g(i);
ANALYZE categories;
ANALYZE products;
-- warm up
SELECT cat.name, count(*)
FROM products AS p, categories AS cat
WHERE p.category_id = cat.id
GROUP BY 1;
EXPLAIN ANALYZE
SELECT cat.name, count(*)
FROM products AS p, categories AS cat
WHERE p.category_id = cat.id
GROUP BY 1;
-- execution time: 34ms
EXPLAIN ANALYZE
SELECT c.name, count
FROM (
SELECT category_id, count(*) AS count FROM products
GROUP BY 1
) AS p
INNER HASH JOIN categories c ON p.category_id = c.id;
-- execution time: 28ms
Raw
agg_3_tables.sql
CREATE TABLE categories (
id INT PRIMARY KEY,
name TEXT
);
INSERT INTO categories VALUES
(0, 'Shoes'),
(1, 'Shirts'),
(2, 'Car'),
(3, 'Bike');
CREATE TABLE colors (
id INT PRIMARY KEY,
name TEXT
);
INSERT INTO colors VALUES
(0, 'Red'),
(1, 'Green'),
(2, 'Yellow'),
(3, 'Blue');
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT,
category_id INT REFERENCES categories(id),
color_id INT REFERENCES colors(id)
);
INSERT INTO products (id, name, category_id, color_id)
SELECT
i,
'Product ' || i,
(random() * 3)::INT, -- valid category_id: 0–3
(random() * 3)::INT -- valid color_id: 0–3
FROM generate_series(1, 200000) AS g(i);
ANALYZE categories;
ANALYZE colors;
ANALYZE products;
-- warm up
SELECT cat.name, col.name, count(*)
FROM products AS p, categories AS cat, colors AS col
WHERE p.color_id = col.id
AND p.category_id = cat.id
GROUP BY 1, 2;
-- When grouping-by TEXT columns, we observe a 26.4% speed-up.
EXPLAIN ANALYZE
SELECT cat.name, col.name, count(*)
FROM products AS p, categories AS cat, colors AS col
WHERE p.color_id = col.id
AND p.category_id = cat.id
GROUP BY 1, 2;
-- execution time: 67ms
EXPLAIN ANALYZE
SELECT c.cat_name, c.col_name, count
FROM (
SELECT color_id, category_id, count(*) AS count FROM products
GROUP BY 1, 2
) AS p
INNER STRAIGHT JOIN (
SELECT cat.id AS cat_id, cat.name AS cat_name, col.id AS col_id, col.name AS col_name
FROM categories AS cat, colors AS col
) AS c ON p.color_id = c.col_id
AND p.category_id = c.cat_id;
-- execution time: 53ms
-- When grouping-by INT columns, we observe a 7.5% speed-up.
EXPLAIN ANALYZE
SELECT cat.id, col.id, count(*)
FROM products AS p, categories AS cat, colors AS col
WHERE p.color_id = col.id
AND p.category_id = cat.id
GROUP BY 1, 2;
-- execution time: 57ms
EXPLAIN ANALYZE
SELECT c.cat_id, c.col_id, count
FROM (
SELECT color_id, category_id, count(*) AS count FROM products
GROUP BY 1, 2
) AS p
INNER STRAIGHT JOIN (
SELECT cat.id AS cat_id, cat.name AS cat_name, col.id AS col_id, col.name AS col_name
FROM categories AS cat, colors AS col
) AS c ON p.color_id = c.col_id
AND p.category_id = c.cat_id;
-- execution time: 53ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment