Re: index not being used when variable is sent

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Eyal Wilde <eyal@xxxxxxxxxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux