Horton

Re: Remove useless GROUP BY columns considering unique index

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>, Zhang Mingli <zmlpostgres@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-11-29T09:20:09Z
Lists: pgsql-hackers 
On Fri, 29 Nov 2024 at 20:04, jian he <jian.universality@gmail.com> wrote:
> I found that unique expression index can also be used for groupby
> column removal.
> so I implemented it, aslo added two tests on it.

> what do you think?

I think it's likely just not common enough to be worthwhile, plus,
unfortunately, I don't think this optimisation is possible with what
we have today. Also, if it were possible you'd need to check the GROUP
BY expressions match the indexed expressions. You've not done that.

The reason I don't think is possible is that we have no infrastructure
that allows us to tag functions or operators so that non-null input(s)
mean non-null outputs.  We only have strict, which means null input
means null output. That's the opposite of what we'd need. It might
only be possible with a NULLS NOT DISTINCT index.

David

Commits

  1. Detect redundant GROUP BY columns using UNIQUE indexes

  2. Defer remove_useless_groupby_columns() work until query_planner()