Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group