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

From Dmitry Karasik
Subject Speed of the stored procedures?
Date
Msg-id 848ydtqyuh.fsf@plab.ku.dk
Whole thread Raw
Responses Re: Speed of the stored procedures?  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
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 ---


pgsql-novice by date:

Previous
From: Oliver Elphick
Date:
Subject: Re: array in postgre
Next
From: "M. Bastin"
Date:
Subject: Extended query language: does it work?