public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Tom Lane <[email protected]>
Cc: ma lz <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Why not do distinct before SetOp
Date: Wed, 6 Nov 2024 11:11:51 +1300
Message-ID: <CAApHDvod=mg8xKbBTFTu7HPWzLk+UhHVdXXwQEj9eNh5buVaiQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <TYCPR01MB63514B9C70EF2A80F0D43394F2512@TYCPR01MB6351.jpnprd01.prod.outlook.com>
	<CAApHDvqdDwEXxhZLTwsHkWnvpvVSYT2OXSzfxRrs2p5xudr9fw@mail.gmail.com>
	<[email protected]>

On Tue, 5 Nov 2024 at 04:18, Tom Lane <[email protected]> wrote:
> A different idea that occurred to me while looking at this is:
> why have we got all this machinery to add and check a flag
> column, rather than arranging things so that the two input
> relations are "outer" and "inner" children of the SetOp?

I've no idea why it's not like that. The current design is quite
strange and feels dated. It might be worth making that change as even
if we gave joins better support for IS NOT DISTINCT FROM and made
INTERSECT use INNER JOIN instead and EXCEPT use anti join, we'd still
need nodeSetOp.c for INTERSECT ALL and EXCEPT ALL.

> It's possible some of the performance difference reported here
> is due to having to pass more tuples through the SubqueryScan
> node (with its projection to add the flag) and Append node,
> but we could remove those steps entirely.

Seems plausible.

> > If we did want to improve this area, I think the first thing we'd want
> > to do is use standard join types rather than HashSetOp Intersect to
> > implement INTERSECT (without ALL).  To do that efficiently, we'd need
> > to do a bit more work on the standard join types to have them
> > efficiently support IS NOT DISTINCT FROM clauses as the join keys.
>
> Maybe.  It'd be a big project, but we do get complaints every so
> often about IS NOT DISTINCT FROM predicates not being efficient,
> so the benefits would be wider than just INTERSECT.

Yeah, I agree. I think that's step 1 towards making INTERSECT (without
ALL) and EXCEPT (without ALL) better and it would probably make a few
other people happy who use IS NOT DISTINCT FROM in their join
conditions.

David

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Why not do distinct before SetOp
  In-Reply-To: <CAApHDvod=mg8xKbBTFTu7HPWzLk+UhHVdXXwQEj9eNh5buVaiQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox