Thread

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

Haowu Ge <gehaowu@bitmoe.com> — 2025-12-09T08:54:35Z

Hello Richard,
I'm running PostgreSQL using the PGDG Docker image. 
I built it via dpkg-buildpackage, 
but I'm unable to generate a proper installation package with the v2 patch (I'll provide specific details shortly).
If I directly replace the binary in the Docker container with ./build/tmp_install/usr/lib/postgresql/18/bin/postgres, the container fails to start properly.
---
apt-get source "postgresql-18"
cd postgresql-*
patch -p1 < ~/v2-0001-Strip-PlaceHolderVars-from-index-operands.patch
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_resetwal'
make -C pg_rewind checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_rewind'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_rewind'
make -C pg_test_fsync checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_test_fsync'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_test_fsync'
make -C pg_test_timing checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_test_timing'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_test_timing'
make -C pg_upgrade checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_upgrade'
for extra in contrib/test_decoding; do make -C '../../..'/$extra DESTDIR='/root/CNPROJ/pg18.1/postgresql-18-18.1/build'/tmp_install install || exit; done
make[4]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/contrib/test_decoding'
/bin/mkdir -p '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/tmp_install/usr/lib/postgresql/18/lib'
/usr/bin/install -c -m 755 test_decoding.so '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/tmp_install/usr/lib/postgresql/18/lib/'
/bin/mkdir -p '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/tmp_install/usr/lib/postgresql/18/lib/bitcode/test_decoding'
/bin/mkdir -p '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/tmp_install/usr/lib/postgresql/18/lib/bitcode'/test_decoding/
/usr/bin/install -c -m 644 test_decoding.bc '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/tmp_install/usr/lib/postgresql/18/lib/bitcode'/test_decoding/./
cd '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/tmp_install/usr/lib/postgresql/18/lib/bitcode' && /usr/lib/llvm-19/bin/llvm-lto -thinlto -thinlto-action=thinlink -o test_decoding.index.bc test_decoding/test_decoding.bc
make[4]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/contrib/test_decoding'
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_upgrade'
make -C pg_verifybackup checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_verifybackup'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_verifybackup'
make -C pg_waldump checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_waldump'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_waldump'
make -C pg_walsummary checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_walsummary'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pg_walsummary'
make -C pgbench checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pgbench'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/pgbench'
make -C psql checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/psql'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/psql'
make -C scripts checkprep
make[3]: Entering directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/scripts'
make[3]: Nothing to be done for 'checkprep'.
make[3]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin/scripts'
make[2]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build/src/bin'
make[1]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1/build'
make[1]: *** [/usr/share/postgresql-common/server/postgresql.mk:280: override_dh_auto_test-arch] Error 1
make[1]: Leaving directory '/root/CNPROJ/pg18.1/postgresql-18-18.1'
make: *** [/usr/share/postgresql-common/server/postgresql.mk:173: binary] Error 2
dpkg-buildpackage: error: debian/rules binary subprocess returned exit status 2
root@debian:~/CNPROJ/pg18.1/postgresql-18-18.1# find . -name postgres
./build/src/backend/postgres
./build/tmp_install/usr/lib/postgresql/18/lib/bitcode/postgres
./build/tmp_install/usr/lib/postgresql/18/bin/postgres
root@debian:~/CNPROJ/pg18.1/postgresql-18-18.1# du -h ./build/tmp_install/usr/lib/postgresql/18/bin/postgres
50M ./build/tmp_install/usr/lib/postgresql/18/bin/postgres
root@debian:~/CNPROJ/pg18.1/postgresql-18-18.1#
---
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年12月9日(周二) 14:38
收件人："葛豪武"<gehaowu@bitmoe.com>
抄　送："pgsql-bugs"<pgsql-bugs@lists.postgresql.org>; Tom Lane<tgl@sss.pgh.pa.us>
主　题：Re: GROUP BY ROLLUP queries on views trigger full table scans (index usage not optimized)
On Mon, Dec 8, 2025 at 3:10 PM Richard Guo <guofenglinux@gmail.com> wrote:
> As a viable alternative, maybe we can use a lightweight, read-only
> expression walker on the operand tree first to detect the presence of
> any PHVs. We only trigger the expensive deep mutation if this
> preliminary check confirms that it is actually necessary.
Here is an updated patch along these lines.
Haowu, could you please verify if it fixes your query?
- Richard