Hi,
I encountered a problem while trying to improve the performance of a certain select query I have made.
here is a simplified code for the function I am using
CREATE OR REPLACE FUNCTION test_func(STR text)
RETURNS integer AS
$BODY$
begin
insert into plcbug(val) values('begin time before perform');
perform t1.val FROM t1 WHERE
(COALESCE(rpad(t1.val, 100),'') ) like COALESCE(STR || '%','')
order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
insert into plcbug(val) values('time after perform');
return 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_func(text) OWNER TO postgres;
plcbug is a table I am using in order to see how much time has past between the perform query.
t1 (about 800,000 records) is:
create table t1 (val varchar(200))
this is the code of the index for the query
CREATE INDEX ixt1 ON t1 USING btree
((COALESCE(rpad(val::text, 100), ''::text)) varchar_pattern_ops)
the problem is that for some reason the index is not being used when I try to run the function with the STR variable(the running time is about 70 milliseconds), but if I am writing the same text instead of using the variable STR then the index is being used(the runing time is about 6 milliseconds)
to make it more clear
COALESCE(STR || '%','') this is when I use the variable and the function is being called by
select test_func('si')
COALESCE('si' || '%','') this is when I write the text at hand and the index is being used.
I tried to cast the _expression_ with every type I could think of with no success of making the index work
postgresql version is 9.0.4 64-bit on windows server 2008 R2.
more info:
i did not know how to do "explain analyze" for the code inside the function. so i did something which i believe still represent the same problem. instead of using the variable (STR) i did a select from a very simple, one record table t2, which holds the value.
create table t2 (val varchar(200));
insert into t2 (val) values ('si');
analyze t2;
select t1.val FROM t1 WHERE
(COALESCE(rpad(t1.val, 100),'') ) like COALESCE((select val from t2 limit 1) || '%','')
order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
select t1.val FROM t1 WHERE
(COALESCE(rpad(t1.val, 100),'') ) like COALESCE('si' || '%','')
order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
Thanks in advance for the help!
Eran
