Tom Lane wrote:
Gaetano Mendola <> 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...

Gaetano Mendola

Search Discussions

Discussion Posts


Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouppgsql-hackers @
postedOct 6, '05 at 11:14a
activeOct 6, '05 at 2:22p

2 users in discussion

Gaetano Mendola: 2 posts Tom Lane: 1 post



site design / logo © 2022 Grokbase