Speed of the stored procedures? - Mailing list pgsql-novice

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