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

Search Discussions

  • Tom Lane at Aug 16, 2011 at 1:41 pm

    Eyal Wilde 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
  • Eyal Wilde at Aug 17, 2011 at 6:49 am
    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 wrote:

    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
  • Jim Nasby at Aug 17, 2011 at 8:50 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedAug 16, '11 at 4:30a
activeAug 17, '11 at 8:50p
posts4
users3
websitepostgresql.org
irc#postgresql

3 users in discussion

Eyal Wilde: 2 posts Tom Lane: 1 post Jim Nasby: 1 post

People

Translate

site design / logo © 2022 Grokbase