Re: Outer where pushed down

Tom Lane wrote:
> Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
>> CREATE OR REPLACE VIEW v_current_connection AS
>> SELECT ul.id_user
>> FROM user_login ul,
>> current_connection cc
>> WHERE ul.id_user = cc.id_user;
>
>> # explain select * from v_current_connection_test where sp_connected_test(id_user) = FALSE;
>
>> why postgres doesn't apply that function at table current_connection given the fact are extimated
>> only 919 vs 27024 rows?
>
> Because the condition is on a field of the other table.
>
> You seem to wish that the planner would use "ul.id_user = cc.id_user"
> to decide that "sp_connected_test(ul.id_user)" can be rewritten as
> "sp_connected_test(cc.id_user)", but in general this is not safe.
> The planner has little idea of what the datatype-specific semantics
> of equality are, and none whatsoever what the semantics of your
> function are. As a real-world example: IEEE-standard floating
> point math considers that +0 and -0 are different bit patterns.
> They compare as equal, but it's very easy to come up with user-defined
> functions that will yield different results for the two inputs.
> So the proposed transformation is definitely unsafe for float8.

And what about to define for each type when this is safe and let
the planner make his best choice ?
Rewriting that view the execution time passed from 4 secs to 1 sec,
that is not bad if the planner can do it autonomously. In this very
example I can decide if it's better expose one column or the other
one but in other cases not...

Regards
Gaetano Mendola