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=12 loops=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 ---



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Fri, 9 Jul 2004, Dmitry Karasik wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html