Re: propose to pushdown qual into EXCEPT

"=?ISO-8859-1?B?QXJtb3I=?=" <yupengstone(at)qq(dot)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