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

Re: index not being used when variable is sent

From: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
To: 25601(dot)1313502058(at)sss(dot)pgh(dot)pa(dot)us, pgsql-performance(at)postgresql(dot)org
Cc: erans(at)impactsoft(dot)co(dot)il, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: index not being used when variable is sent
Date: 2011-08-17 06:49:05
Message-ID: CAMiEbcg5TR7MkHrK+QQmH0eDxTuBiw9OzZ6ai7NydUt1=yihZA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Eyal Wilde <eyal(at)impactsoft(dot)co(dot)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
>

In response to

Responses

pgsql-performance by date

Next:From: Andy ColsonDate: 2011-08-17 13:41:46
Subject: Re: Tuning Tips for a new Server
Previous:From: OgdenDate: 2011-08-17 01:35:03
Subject: Tuning Tips for a new Server

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