Thread

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

Richard Guo <guofenglinux@gmail.com> — 2025-12-18T08:56:08Z

On Mon, Dec 15, 2025 at 10:23 AM Haowu Ge <gehaowu@bitmoe.com> wrote:
> On Thu, Dec 12, 2025 at 9:28 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > This seems to be a separate issue.  The query against the view chooses
> > different indexes than the query against the underlying table.  This
> > isn't the index capability issue you initially reported, but rather a
> > cost estimation issue.

> After testing, your patch can resolve the issue with the sample case I raised earlier.
> Thank you.
>
> However, there is another problem (I thought my example was sufficient, but it actually wasn’t).
> In my actual scenario, my view includes join conditions,
> and the new patch still does not achieve the same index behavior as in PostgreSQL 16.11.

This happens because the v3 patch is conservative: it only strips a
PHV if its syntactic scope (phrels) strictly matches the index
relation.  In query:

select 1 from (select t1.a as x from t t1, t t2) where x = 1 group by rollup(x);

The syntactic scope of PHV(t1.a) includes both {t1, t2}.  As a result,
the check fails when matching against t1's index, which prevents the
PHV from being stripped and the index from being used.

I considered checking PlaceHolderInfo.ph_eval_at instead, which would
be more accurate.  However, finding the corresponding PlaceHolderInfo
requires access to root, and passing root into match_index_to_operand
would break ABI compatibility, which we want to avoid.

Upon closer look, it seems that we do not need the syntactic scope
check at all, as long as we ensure that phnullingrels is empty.  In
this case, the PHV is either a no-op (from a reduced outer join) or
used for identity separation (grouping sets).  In either case, the PHV
is transparent regarding the value, so it should not prevent the
underlying expression from matching the index.

Any thoughts?

- Richard