Re: Outer where pushed down

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.

regards, tom lane