"=?ISO-8859-1?B?QXJtb3I=?=" <yupengst...@qq.com> writes: > Because PG does not pushdown qual to the none of the subquery. And I > check the source code, find some comments in > src/backend/optimizer/path/allpaths.c, which says "If the subquery > contains EXCEPT or EXCEPT ALL set ops we cannot push quals into it, because > that could change the results". > However, for this case, I think we can pushdown qual to the left most > subquery of EXCEPT, just like other database does.
That is not an adequate argument for such a change being okay. Postgres, with its extensible set of datatypes, has to be much more careful about the semantic soundness of optimizations than some other DBs do. The existing behavior here dates to commit 0201dac1c319599a, which was inspired by this thread: https://www.postgresql.org/message-id/flat/46C15C39FEB2C44BA555E356FBCD6FA48879A6%40m0114.s-mxs.net (BTW, several of the concrete examples discussed in that thread no longer apply because of later changes. But the problem still exists.) We had convinced ourselves that pushing down a qual into UNION and INTERSECT cases is okay even if the qual can distinguish rows that the setop comparisons see as equal, because you would get results consistent with the setop having chosen some legitimate set of representative row(s) for each group of "duplicate" rows. However that did not seem to apply to EXCEPT, or at least I wasn't convinced enough to risk it. I'm still not, without a worked-out argument as to why it's okay. In particular I'd like to see a proof that establishes (a) why it is or is not okay to push into the right-hand side of EXCEPT, and (b) whether ALL does or does not make a difference. Now you could argue that we threw all these fine points out the window with respect to window functions in commit d222585a9f7a18f2, so maybe it's okay to do it with respect to EXCEPT as well. But that would lead to deciding it's okay to push into both sides of EXCEPT, which is still not what this patch does. Anyway I'm not very pleased by the idea that we'd hold EXCEPT to a weaker semantic standard than UNION and INTERSECT. regards, tom lane -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers