Re: Speed of the stored procedures?

On Fri, 9 Jul 2004, Dmitry Karasik wrote:

> Can anyone explain what may be the reason to the drastic difference
> in the execution speed of the same SQL statement, executed from the
> command line and from inside the stored procedure? Or, which is
> more important, how to fight this?

Those aren't the same statements precisely.  In one case there's a given
anchored constant which can be used for purposes of planning and for
converting to an index condition.  In the other, it's a variable, and
there's no way to know that you will not pass '%foo' or some other
non-anchored string.

If you want to replan inside the function using the actual passed value,
you can use some EXECUTE variant (probably FOR ... IN EXECUTE since you
want a value out).

Something like the untested:

CREATE OR REPLACE FUNCTION
f_test(TEXT)
RETURNS integer AS '
DECLARE
        p_from ALIAS FOR $1;
        c INTEGER;
    r record;
BEGIN
    FOR r IN EXECUTE ''select count(id) as c FROM queues WHERE
hostname LIKE '' || quote_literal(p_from) LOOP
        RETURN r.c;
    END LOOP;
    RETURN NULL;
END;
' LANGUAGE 'plpgsql';