Tom Lane wrote: > Gaetano Mendola <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
