On Wed, 1 Nov 2023 at 11:41, Dimitrios Apostolou <jimis@xxxxxxx> 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
