Horton
Menu

Thread

  1. Why not do distinct before SetOp

    ma lz <ma100@hotmail.com> — 2024-11-04T09:52:12Z

    some sql like ' select a from t1 intersect select a from t1 '

if t1 has large number rows but has few distinct rows

select distinct a from t1 intersect select distinct a from t1;    ― this is faster than origin sql

can postgres do this optimize during plan-queries?

  2. Re: Why not do distinct before SetOp

    David Rowley <dgrowleyml@gmail.com> — 2024-11-04T11:09:35Z

    On Mon, 4 Nov 2024 at 22:52, ma lz <ma100@hotmail.com> wrote:
>
> some sql like ' select a from t1 intersect select a from t1 '
>
> if t1 has large number rows but has few distinct rows
>
> select distinct a from t1 intersect select distinct a from t1;    — this is faster than origin sql
>
> can postgres do this optimize during plan-queries?

No, the planner does not attempt that optimisation. INTERSECT really
isn't very well optimised.

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.
There's a fair bit of work to do and it's likely not been done as
INTERSECT isn't used that commonly.

There was a bit of work done in PG17 to teach the query planner some
new tricks around UNION. I think UNION is a much more commonly used
setop than INTERSECT, so you might have to wait a while. For now, it's
best to adjust your query.

David

  3. Re: Why not do distinct before SetOp

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-04T15:18:04Z

    David Rowley <dgrowleyml@gmail.com> writes:
> On Mon, 4 Nov 2024 at 22:52, ma lz <ma100@hotmail.com> wrote:
>> select distinct a from t1 intersect select distinct a from t1;    — this is faster than origin sql

> No, the planner does not attempt that optimisation. INTERSECT really
> isn't very well optimised.

It's not really obvious to me why adding DISTINCT would make it
faster.  Seems like having two layers of plan nodes checking for
duplicate rows ought to be a loss.  Maybe we need to do some
micro-optimization in or near LookupTupleHashEntry.

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?
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.

> 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.

			regards, tom lane

  4. Re: Why not do distinct before SetOp

    David Rowley <dgrowleyml@gmail.com> — 2024-11-05T22:11:51Z

    On Tue, 5 Nov 2024 at 04:18, Tom Lane <tgl@sss.pgh.pa.us> 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

  5. Re: Why not do distinct before SetOp

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-05T23:09:41Z

    David Rowley <dgrowleyml@gmail.com> writes:
> On Tue, 5 Nov 2024 at 04:18, Tom Lane <tgl@sss.pgh.pa.us> 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.

Yeah.  We'd still need it, and besides which it seems like a fairly
small project, unlike the other thing which could take multiple
years to get to an acceptable state.

Of course, I might be overestimating the performance benefit we'd get.
But I'm tempted to give it a try.

			regards, tom lane

  6. Re: Why not do distinct before SetOp

    David Rowley <dgrowleyml@gmail.com> — 2024-11-05T23:38:00Z

    On Wed, 6 Nov 2024 at 12:09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Of course, I might be overestimating the performance benefit we'd get.
> But I'm tempted to give it a try.

I'm glad. I'm curious to see if you're right about the projection
overhead of the flags. If you're right, it seems like a not too
difficult optimisation to get in.

I like the idea as it also gets rid of the flag cruft from
prepunion.c, which saves having to add flags conditionally if
INTERCEPT/EXCEPT were ever made to use joins.

David