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

On Tue, Dec 9, 2025 at 5:54 PM Haowu Ge <gehaowu(at)bitmoe(dot)com> wrote:
> I built it via dpkg-buildpackage,
> but I'm unable to generate a proper installation package with the v2 patch

Hmm, I don't have much insight into the installation issue. I tested
the v2 patch on both v18 and master, and it fixes your query.

EXPLAIN (COSTS OFF)
SELECT material_id, AVG(unit_price) as avg_price
FROM materials_view
WHERE material_id = 'PI'
GROUP BY ROLLUP(material_id);
QUERY PLAN
--------------------------------------------------------
GroupAggregate
Group Key: materials.material_id
Group Key: ()
-> Index Scan using materials_pkey on materials
Index Cond: ((material_id)::text = 'PI'::text)
(5 rows)

Regarding back-patching, I believe this issue exists before v18, but
it seems that the change in v18 made it common enough to notice,
especially in queries with grouping sets. Given the lack of reports
for versions prior to v18, I'm inclined to back-patch this only to
v18.

Any thoughts?

- Richard