Thread

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

Richard Guo <guofenglinux@gmail.com> — 2025-12-28T04:20:58Z

I've been back and forth on whether to back-patch these two patches to
v18, given that they can lead to plan changes.

To recap: in v18, we fixed a correctness issue regarding grouping sets
by wrapping Var expressions in PHVs.  While necessary, it had the side
effect of making these expressions opaque to the planner: the planner
can no longer match these expressions to indexes or look up their
statistics.  As reported here, this causes a severe performance
regression in v18 compared to previous versions.

The proposed fix is to teach the planner to look through these PHVs
when matching indexes or looking up statistics.  While this fixes the
regression, it can lead to plan changes, which we want to avoid in
back branches.

However, I'm still inclined to back-patch this to v18 (and no
further), because:

1. The fix only affects queries involving PHVs in indexable or
statistical contexts.  The regression tests show that only one
existing query is affected, and in a positive way.  So the impact is
pretty scoped.

2. For the affected queries, the current behavior is arguably broken:
the planner is forced to ignore valid indexes and fall back to default
selectivity estimates.  I think the fix is an improvement.

3. If we do not back-patch, this severe performance regression will
remain in v18 forever.

Having said that, back-patching a change that affects query plans
still keeps me up at night.  Any thoughts?

- Richard