From: Haowu Ge <gehaowu@bitmoe.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re：GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)
Date: Fri, 05 Dec 2025 10:17:36 +0800
Message-ID: <bc40b611-960b-4a98-a0ea-becd72d558d9.gehaowu@bitmoe.com> (raw)
In-Reply-To: <CAMbWs48J51_SJ5nyA5P=d0mtkwvXf3rS=nOC7HL5+hjrbPZuSw@mail.gmail.com>
References: <62af586c-c270-44f3-9c5e-02c81d537e3d.gehaowu@bitmoe.com>
<CAMbWs48=zSwZEbqX4-A1jtJqWuPxu8xbkQbu+CdtHg5anM9RVg@mail.gmail.com>
<CAMbWs48J51_SJ5nyA5P=d0mtkwvXf3rS=nOC7HL5+hjrbPZuSw@mail.gmail.com>
Hello everyone,
is this patch intended to fix the bug where GROUP BY ROLLUP causes a full table scan?
I tried applying the patch myself, compiled and installed it,
but it doesn’t seem to resolve the issue of GROUP BY ROLLUP leading to a full table scan.
Thank you.
Thanks & Best Regards
_________________________________________________________________________________
Haowu Ge (BG5FRG) | Homepage: https://www.gehaowu.com <https://www.gehaowu.com > | PGP:7A06 1F6E DF09 D8A8
------------------------------------------------------------------
发件人：Richard Guo <guofenglinux@gmail.com>
发送时间：2025年11月19日(周三) 18:07
收件人："葛豪武"<gehaowu@bitmoe.com>
抄 送："pgsql-bugs"<pgsql-bugs@lists.postgresql.org>
主 题：Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)
On Tue, Nov 18, 2025 at 5:27 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Alternatively, we could teach match_index_to_operand to unwrap PHV
> expressions when matching them to indexes. This would also allow the
> WHERE clause to match indexes for non-Var expressions, which is an
> improvement over the behavior prior to v18. But I haven't fully
> thought through whether this approach is safe in all cases,
> particularly when outer joins are involved.
I believe this approach is safe, because a PlaceHolderVar appearing in
a relation-scan-level expression is effectively a no-op. To play it
safe, we could verify that the PlaceHolderVar is not marked nullable
and that its syntactic scope matches this index, and only strip such
PlaceHolderVars (though it's unclear if this is really necessary).
Attached is a patch that implements this approach. With this patch,
the reported query can use an index scan again. Additionally, it
enables planner to explore index scans in other cases that were
previously not possible, even before v18. For example:
create table t (a int);
insert into t select i from generate_series(1,1000)i;
create index on t (coalesce(a, 1));
analyze t;
explain (costs off)
select * from t t1 left join
(select coalesce(a, 1) as c, * from t t2) s on true
where t1.a > s.c;
QUERY PLAN
-----------------------------------------------
Nested Loop
-> Seq Scan on t t1
-> Index Scan using t_coalesce_idx on t t2
Index Cond: (COALESCE(a, 1) < t1.a)
(4 rows)
The expression coalesce(a, 1) is wrapped in a PlaceHolderVar, and
previously it was not possible to match it to the index expression.
- Richard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: gehaowu@bitmoe.com, guofenglinux@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Re：GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)
In-Reply-To: <bc40b611-960b-4a98-a0ea-becd72d558d9.gehaowu@bitmoe.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox