Horton

Re: Remove useless GROUP BY columns considering unique index

jian he <jian.universality@gmail.com>

View thread View on postgr.es
From: jian he <jian.universality@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Zhang Mingli <zmlpostgres@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-11-27T06:51:07Z
Lists: pgsql-hackers

Attachments

On Fri, Nov 22, 2024 at 9:24 PM jian he <jian.universality@gmail.com> wrote:
>
> overall i come up with the attached patch.
in v2:

create table t1 (a int, b int not null, c int, unique(b, c));
explain(costs off) select count(*) from t1 group by b,c,a;
      QUERY PLAN
----------------------
 HashAggregate
   Group Key: b
   ->  Seq Scan on t1

so the v2 implementation is wrong.
I overlooked cases like: only part of the unique-key is not-null.
In that situation, we cannot remove useless groupby columns.

v3 attached. in v3:
      QUERY PLAN
----------------------
 HashAggregate
   Group Key: b, c, a
   ->  Seq Scan on t1

Commits

  1. Detect redundant GROUP BY columns using UNIQUE indexes

  2. Defer remove_useless_groupby_columns() work until query_planner()