On Wed, 1 Nov 2023 at 11:41, Dimitrios Apostolou <jimis@gmx.net> wrote:
> I'm wondering why the planner doesn't see that the left table is very small and follow a different path.
> From an abstract computer science POV, I would
>
> 1. sort the left table (the right one is already indexed)
> 2. "merge" the two tables, by walking them in-order in parallel and excluding the matches
> 3. stop when the left table is exhausted, which would happen very early.
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.
> Is this worth a bug report? I can file one if the issue is not known.
No. It's just a missing optimisation. We know about it.
> In the meantime I have replaced the query with a LEFT OUTER JOIN which
> performs much better, and I believe is equivalent. I find it less readable
> than the query in question though. Plus, I have a bunch of SELECT-EXCEPT
> queries (with smaller right-side tables) in my application that I would
> hate to change them all to the ugliest equivalent. Under what conditions
> would the above query plan perform well?
It'll be best if you just use NOT EXISTS. You should be able to form
the LEFT JOINS to make use of an Anti-Join. If you don't want to
rewrite your queries then you'll just be at the mercy of the current
planner's ability to plan EXCEPT queries, unfortunately. There won't
be any bug fixes to improve this. It may, however, be improved in some
future version of PostgreSQL.
David