I agree that brutally disabling filter pushdown is out of question. However, it may not be so bad in terms of performance if done selectively only for operators that may raise an error.
If for instance you have a big conjunction and only one conjunct contains a cast you can push everything below the join and keep only the cast on the top.
There may be other ways to retain performance while being inline with standard SQL semantics; I haven't given too much though about it.
I didn't find a relevant discussion in the archives so I thought it was worth putting this down for people who may bump into this in the future.
I don't have a strong opinion if it is worth trying to fix this or not cause I don't really know much about Postgres internals.
In any case, it may be good to document the current behavior somewhere.
Note that the workaround with CASE WHEN can address this simplistic example but it can easily get very complicated if you want to perform a cast based on the result of a complex join; every join condition has to become part of the WHEN clause.
There are probably other workarounds such as wrapping the join in a sub query, putting the risky computation in project, and the filtering in a outer query but these syntactic rewritings may become meaningless in the eyes of the query optimizer. I don't know what Postgres will do in this case but I know other optimizers that will happily pushdown the filter all the way down and on top of the scan.
All that to say that the SQL semantics that say that WHERE is applied on the result of FROM are quite well-known so when exceptions come into play things get tricky.
Apologies for the long message and many thanks for taking the time to look into this.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
>> The SQL standard (Section 7.4 general rule 1) mandates that WHERE should be
>> applied to the result of FROM so in the case above pushing filters below
>> the join seems to violate the standard.
> The failure to document such a deviation from the standard can be
> considered a bug but not the deviation itself. That is intentional. In
> terms of trade-offs the current behavior seems reasonable.
Enforcing such a restriction would be absolutely disastrous for the
performance of a large number of real-world queries. I doubt that
any other RDBMS takes that spec wording literally either.
I'd suggest something like
CASE WHEN e.empno = 0 THEN NULL ELSE 10 / e.empno END
if you need to program around the lack of guarantees about the
order of evaluation. A WITH clause can also be used as an
optimization fence when you need one.
BTW, this *is* documented, see for example
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL
regards, tom lane