Re: Inefficient query plan for SELECT ... EXCEPT ... - Mailing list pgsql-general

From Tom Lane
Subject Re: Inefficient query plan for SELECT ... EXCEPT ...
Date
Msg-id 2664450.1698799927@sss.pgh.pa.us
Whole thread Raw
In response to Re: Inefficient query plan for SELECT ... EXCEPT ...  (David Rowley <dgrowleyml@gmail.com>)
List pgsql-general
David Rowley <dgrowleyml@gmail.com> writes:
> It would be possible to have some sort of MergeExcept operator and
> have the planner consider that. Unfortunately, since the upper planner
> was changed a few years ago to have it consider paths the same as the
> join planner does, nobody has yet come back to the union planner to
> properly pathify that.  I do have a WIP patch to do this work, but I
> wasn't planning on improving EXCEPT, only UNION. Making it work for
> EXCEPT and INTERSECT would require a new executor operator.

Yeah.  We're moderately good about UNION ALL, but UNION/INTERSECT/EXCEPT
are an area that nobody has ever gotten around to optimizing: the two
sub-queries will be planned independently and then fed to a simplistic
set-operation node.  Maybe that'll get better someday but don't hold
your breath.  In the meantime, try to recast an EXCEPT query as an
antijoin.

            regards, tom lane



pgsql-general by date:

Previous
From: Alan Evans
Date:
Subject: Feature/Suggestion: libpq/psql support for libsecret (formerly gnome-keyring)
Next
From: veem v
Date:
Subject: Re: Question on Aurora postgres