Thread

GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)

Haowu Ge <gehaowu@bitmoe.com> — 2025-11-17T08:02:21Z

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:
# 1. Create a base table 'materials' to store product information.
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);
Log, please refer to the attachment for more logs:
GroupAggregate (cost=0.00..1097.39 rows=251 width=64) (actual time=3.983..3.985 rows=2.00 loops=1)
 Group Key: materials.material_id
 Group Key: ()
 Buffers: shared hit=468
 -> Seq Scan on materials (cost=0.00..1093.00 rows=250 width=48) (actual time=0.018..3.970 rows=1.00 loops=1)
 Filter: ((material_id)::text = 'PI'::text)
 Rows Removed by Filter: 49999
 Buffers: shared hit=468
Planning Time: 0.117 ms
Execution Time: 4.029 ms
Thanks & Best Regards
_________________________________________________________________________________ 
Haowu Ge (BG5FRG) | Homepage: https://www.gehaowu.com <https://www.gehaowu.com > | PGP:7A06 1F6E DF09 D8A8