Обсуждение: index not being used when variable is sent
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
Eyal Wilde <eyal@impactsoft.co.il> writes:
> CREATE OR REPLACE FUNCTION test_func(STR text)
> ...
> perform t1.val FROM t1 WHERE
> (COALESCE(rpad(t1.val, 100),'') ) like COALESCE(STR || '%','')
> order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
[ doesn't use index ]
No, it doesn't. The LIKE index optimization requires the LIKE pattern
to be a constant at plan time, so that the planner can extract the
pattern's fixed prefix. An expression depending on a function parameter
is certainly not constant.
If you really need this to work, you could use EXECUTE USING so that
the query is re-planned for each execution.
regards, tom lane
Thanks for the reply.
(i'm sorry for that i didn't really know how to reply to a certain message...)
well, i used LIKE, but i actually wanted just "starts with".
the solution i found without using LIKE is this:
CREATE OR REPLACE FUNCTION test_func(STR text)
RETURNS integer AS
$BODY$
declare
STR2 varchar;
begin
-- example: if STR is 'abc' then STR2 would be 'abd'
STR2 := substring(STR,0,length(STR))||chr((ascii(substring(STR,length(STR)))+1));
insert into plcbug(val) values('begin time before perform');
perform t1.val FROM t1 WHERE
(COALESCE(rpad((val)::text, 100, ' '::text), ''::text) ~>=~ STR::text) AND
(COALESCE(rpad((val)::text, 100, ' '::text), ''::text) ~<~ STR2::text)
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;
1. is there any more elegant solution?
2. considering LIKE, practically there are only two cases: the expression (variable||'%') may be '%something%' or 'something%' [*], right?? do you think the optimizer can do better by conditionally splitting the plan according to actual value of a variable?
[*] for the sake of the discussion lets forget about '_something'.
Thanks again.
On Tue, Aug 16, 2011 at 16:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ...> perform t1.val FROM t1 WHERE[ doesn't use index ]
> (COALESCE(rpad(t1.val, 100),'') ) like COALESCE(STR || '%','')
> order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
No, it doesn't. The LIKE index optimization requires the LIKE pattern
to be a constant at plan time, so that the planner can extract the
pattern's fixed prefix. An expression depending on a function parameter
is certainly not constant.
If you really need this to work, you could use EXECUTE USING so that
the query is re-planned for each execution.
regards, tom lane
On Aug 17, 2011, at 1:49 AM, Eyal Wilde wrote: > 1. is there any more elegant solution? Very possibly, but I'm having a heck of a time trying to figure out what your current code is actually doing. What's the actual problem you're trying to solve here? -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net