Horton

Re: Should HashSetOp go away

David Rowley <dgrowleyml@gmail.com>

View thread
From: David Rowley <dgrowleyml@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jeff Janes <jeff.janes@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-10-26T22:14:32Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Change "long" numGroups fields to be Cardinality (i.e., double).

  2. Improve planner's estimates of tuple hash table sizes.

  3. Use BumpContext contexts in TupleHashTables, and do some code cleanup.

  4. Convert SetOp to read its inputs as outerPlan and innerPlan.

  5. Use more efficient hashtable for execGrouping.c to speed up hash aggregation.

On Mon, 27 Oct 2025 at 07:00, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Jeff Janes <jeff.janes@gmail.com> writes:
> > I was thinking of ways to improve the memory usage (or at least its
> > estimation) but decided maybe it would be better if HashSetOp went away
> > entirely.  As far as I can tell HashSetOp has nothing to recommend it other
> > than the fact that it already exists. If we instead used an elaboration on
> > Hash Anti Join, then it would automatically get spilling to disk, parallel
> > operations, better estimation, and the benefits of whatever micro
> > optimizations people lavish on the highly used HashJoin machinery but not
> > the obscure, little-used HashSetOp.
>
> This seems like a pretty bad solution.  It would imply exporting the
> complexities of duplicate-counting for EXCEPT ALL and INTERSECT ALL
> modes into the hash-join logic.  We don't need that extra complexity
> there (it's more than enough of a mess already), and we don't need
> whatever performance hit ordinary hash joins would take.

If Hash Joins did support IS NOT DISTINCT FROM clauses, then at least
the non-ALL cases could be done with Hash Semi Join and Hash Anti Join
for INTERSECT and EXCEPT, respectively, followed by a HashAgg. I doubt
it would be any faster for the general case, but at least it would
allow those setop queries to run when the inputs don't fit in memory.
It's not ideal though, as when the planner underestimates, Hashed
Setops could still blow up.

David