Thread

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

Richard Guo <guofenglinux@gmail.com> — 2025-12-10T02:01:49Z

On Tue, Dec 9, 2025 at 5:54 PM Haowu Ge <gehaowu@bitmoe.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