Allow pushdown of HAVING clauses with grouping sets

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Allow pushdown of HAVING clauses with grouping sets
Date: 2024-09-11 03:43:47
Message-ID: CAMbWs4-NpzPgtKU=hgnvyn+J-GanxQCjrUi7piNzZ=upiCV=2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In some cases, we may want to transfer a HAVING clause into WHERE in
hopes of eliminating tuples before aggregation instead of after.

Previously, we couldn't do this if there were any nonempty grouping
sets, because we didn't have a way to tell if the HAVING clause
referenced any columns that were nullable by the grouping sets, and
moving such a clause into WHERE could potentially change the results.

Now, with expressions marked nullable by grouping sets with the RT
index of the RTE_GROUP RTE, it is much easier to identify those
clauses that reference any nullable-by-grouping-sets columns: we just
need to check if the RT index of the RTE_GROUP RTE is present in the
clause. For other HAVING clauses, they can be safely pushed down.

I'm not sure how common it is in practice to have a HAVING clause
where all referenced columns are present in all the grouping sets.
But it seems to me that this optimization doesn't cost too much. Not
implementing it seems like leaving money on the table.

Any thoughts?

Thanks
Richard

Attachment Content-Type Size
v1-0001-Allow-pushdown-of-HAVING-clauses-with-grouping-sets.patch application/octet-stream 5.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message shveta malik 2024-09-11 04:18:25 Re: Conflict detection for update_deleted in logical replication
Previous Message Peter Smith 2024-09-11 03:24:33 Re: Pgoutput not capturing the generated columns