index not being used when variable is sent

Поиск
Список
Период
Сортировка
От Eyal Wilde
Тема index not being used when variable is sent
Дата
Msg-id CAMiEbcinfF-WE1=kwCnHGx5Uth8po5GF24ukgNWvDzKVsifn+A@mail.gmail.com
обсуждение исходный текст
Ответы Re: index not being used when variable is sent
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: hyelluas
Дата:
Сообщение: Re: How to see memory usage using explain analyze ?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: index not being used when variable is sent