Re: Inefficient query plan for SELECT ... EXCEPT ...

David Rowley <dgrowleyml(at)gmail(dot)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