I wrote:
> Hmm. The code explicitly won't push conditions down through an EXCEPT:
> * 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
> * quals into it, because that could change the results.
> I remember coming to the conclusion that this is safe for
> UNION/INTERSECT but not EXCEPT, but right at the moment I'm not sure why
> I thought that --- it seems like a qual that suppresses specific rows
> should suppress all matching copies.
I dug in the archives and found the discussion that led up to the
current behavior; see thread starting here
http://archives.postgresql.org/pgsql-hackers/2002-08/msg00041.php
The issue is that rows that are "equal" according to the rules used by
UNION/INTERSECT/EXCEPT may nonetheless be distinguishable to the
expression in the upper WHERE clause, and if that's the case, pushing
down the WHERE can lead to provably incorrect results. So the behavior
is correct, or at least very difficult to improve on, as it stands.
regards, tom lane