The most common reason for this (not specific to PG) is that the function is getting compiled without the substituted constants, and the query plan is generic, whereas with specific values it is able to use column statistics to pick a more efficient one.
I have the function: CREATE OR REPLACE FUNCTION "Test"( ... ) RETURNS SETOF record AS $BODY$ BEGIN RETURN QUERY SELECT ...; END; $BODY$ LANGUAGE 'plpgsql' STABLE
The function call takes about 5 minute to proceed, but using directly its query statement, after replacing the arguments with the same values, it takes just 5 seconds !
I repeat the test several times and the duration is the same.
What is wrong ?
Please note Postgresql version is "PostgreSQL 8.3.5, compiled by Visual C++ build 1400". I used ANALYZE, and my query / function returns about 150 rows. I made the tests in pgAdmin query windows.
