Hello everyone,
Recently, after upgrading my database from PostgreSQL 16 to 18, I encountered an issue: when performing a GROUP BY ROLLUP on a view, the query planner resorts to a full table scan instead of using the index on the underlying table. This severely impacts performance.
----------
:-( The task lasted for 20 seconds, and in the end, it ran for 3.6 hours, Under the same environment and query instructions, pg16 before the upgrade did not have this issue
Planning:
Buffers: shared hit=1829 read=67
I/O Timings: shared read=309.026
Planning Time: 344.548 ms
Execution Time: 12999763.259 ms
----------
Below is a step-by-step reproduction of the issue:
CREATE TABLE materials (
material_id VARCHAR PRIMARY KEY,
description TEXT NOT NULL,
unit_price NUMERIC(10, 2) NOT NULL
);
# 2. Create an explicit index on 'material_id'.
CREATE INDEX idx_material_id ON materials(material_id);
# 3. Create a simple view that mirrors the base table structure.
CREATE VIEW materials_view AS
SELECT material_id, description, unit_price FROM materials;
# 4. Insert test data:
INSERT INTO materials (material_id, description, unit_price)
VALUES ('PI', 'Example Product', 99.99);
INSERT INTO materials (material_id, description, unit_price)
SELECT
'MAT' || generate_series(1,49999)::TEXT,
'Description of product ' || generate_series(1,49999)::TEXT,
(random() * 1000 + 1)::NUMERIC(10,2);
# 5. Query the base table to compute the average unit price for material 'PI',using a simple GROUP BY (no rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials
WHERE material_id = 'PI'
GROUP BY material_id;
# 6. Query the base table to compute the average unit price for material 'PI',using a simple GROUP BY (rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);
# 7. Query the view table to compute the average unit price for material 'PI',using a simple GROUP BY (no rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY
material_id;
# 8. Query the view table to compute the average unit price for material 'PI',using a simple GROUP BY (rollup)
EXPLAIN ANALYZE
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);
