I wrote:
> tl;dr: I now think what the patch proposes to do is legit. There's a heck
> of a lot of work to be done on the comments it's falsified, though.
Hmm, wait a minute. I mentioned before that what nodeSetOp.c actually
returns is N copies of the same representative tuple. That in itself
doesn't break the proposed optimization, or at least so I argued ---
but the real question is which representative tuple does it pick?
The answer, as noted in the file header comment, is
* Note that SetOp does no qual checking nor projection. The delivered* output tuples are just copies of the
first-to-arrivetuple in each* input group.
In HASHED mode, the first-to-arrive tuple must be from the lefthand
input, which would mean that it's passed the pushed-down qual, so
all is well. (If no LHS tuples exist in a given group, then EXCEPT
won't output anything, so the fact that it could have collected a
representative tuple from the RHS doesn't matter.)
However, in SORTED mode, I don't see that there's anything particularly
guaranteeing the order in which tuples arrive within a sort group.
If the sort isn't stable, and I don't think all our sorting paths are,
it would be possible to return an RHS tuple as the representative one.
This breaks the proposed optimization because it would become possible
to return a tuple that doesn't pass the pushed-down qual at all.
There are at least two ways this could be dealt with. We could add the
flag column as a low-order sort column so that it's still guaranteed that
LHS tuples arrive before RHS ones within a group. (This'd complicate
matters in generate_nonunion_path because now the sort keys would be
different from the setop node's own grouping keys, but it's certainly
possible.) Or we could fix it at runtime by complicating
setop_retrieve_direct so that it replaces the representative tuple
with the first LHS tuple when that arrives. Either way, though,
more work is needed than just hacking the qual pushdown logic.
regards, tom lane