About a month ago I asked the general list about plpgsql functions that
occasionally significantly underperform their straight SQL equivalents.
Tom noted that a different query plan was almost certainly being chosen by
the plpgsql function:
http://archives.postgresql.org/pgsql-general/2003-05/msg00966.php
http://archives.postgresql.org/pgsql-general/2003-05/msg00998.php
Tom suggested checking for sloppy datatype declarations in the plpgsql
functions. Double-checked, a-ok.
Tom also suggested that indexscans might not get picked by the plpgsql
function if I have some very skewed statistics. Is there a way to verify
the plpgsql function's planner choices?
My casual observations are that this problem occurs with aggregates, and
that the big performance hit is not consistent. I'd like advice on more
formal troubleshooting.
I can provide examples (my latest problem function is currently taking
over 4 seconds vs. .04 seconds for its straight SQL equivalent), table
schema, explain output for the straight SQL, etc., if anyone cares to work
through this with me.
thanks,
michael