index not being used when variable is sent

From: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
To: pgsql-performance(at)postgresql(dot)org
Cc: erans(at)impactsoft(dot)co(dot)il
Subject: index not being used when variable is sent
Date: 2011-08-16 04:30:18
Message-ID: CAMiEbcinfF-WE1=kwCnHGx5Uth8po5GF24ukgNWvDzKVsifn+A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

http://explain.depesz.com/s/FRb

select t1.val FROM t1 WHERE
(COALESCE(rpad(t1.val, 100),'') ) like COALESCE('si' || '%','')
order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;

http://explain.depesz.com/s/2XI

Thanks in advance for the help!
Eran

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2011-08-16 13:40:58 Re: index not being used when variable is sent
Previous Message David Boreham 2011-08-16 04:13:51 Re: Reports from SSD purgatory