Thread: Speed of the stored procedures?

Hi,

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?

Thanks!

### case 1, command line:

# explain analyze select count(*) from queues where hostname like 'strikeair%';
                                                              QUERY PLAN
              

--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=17.08..17.08 rows=1 width=0) (actual time=49.419..49.422 rows=1 loops=1)
   ->  Index Scan using queues_idx_hostname on queues  (cost=0.00..17.07 rows=5 width=0) (actual time=38.619..49.238
rows=12loops=1) 
         Index Cond: ((hostname >= 'strikeair'::text) AND (hostname < 'strikeais'::text))
         Filter: (hostname ~~ 'strikeair%'::text)
 Total runtime: 62.776 ms
(5 rows)


### case 2, stored procedure:

CREATE OR REPLACE FUNCTION
f_test(TEXT)
RETURNS integer AS '
DECLARE
    p_from ALIAS FOR $1;
    c INTEGER;
BEGIN
    SELECT INTO c count(id) FROM queues WHERE hostname LIKE p_from;
    RETURN c;
END;
' LANGUAGE 'plpgsql';


# explain analyze  select * from f_test('strikeair%');
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Function Scan on f_test  (cost=0.00..12.50 rows=1000 width=4) (actual time=5490.035..5490.040 rows=1 loops=1)
 Total runtime: 5490.124 ms
(2 rows)


--
Sincerely,
    Dmitry

--- www.karasik.eu.org ---

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';