pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex not being used when variable is sent
4+ messages / 3 participants
[nested] [flat]
* index not being used when variable is sent
@ 2011-08-16 04:30 Eyal Wilde <eyal@impactsoft.co.il>
2011-08-16 13:40 ` Re: index not being used when variable is sent Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Eyal Wilde @ 2011-08-16 04:30 UTC (permalink / raw)
To: pgsql-performance; +Cc: erans@impactsoft.co.il
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index not being used when variable is sent
2011-08-16 04:30 index not being used when variable is sent Eyal Wilde <eyal@impactsoft.co.il>
@ 2011-08-16 13:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-17 06:49 ` Re: index not being used when variable is sent Eyal Wilde <eyal@impactsoft.co.il>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2011-08-16 13:40 UTC (permalink / raw)
To: Eyal Wilde <eyal@impactsoft.co.il>; +Cc: pgsql-performance; erans@impactsoft.co.il
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index not being used when variable is sent
2011-08-16 04:30 index not being used when variable is sent Eyal Wilde <eyal@impactsoft.co.il>
2011-08-16 13:40 ` Re: index not being used when variable is sent Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-08-17 06:49 ` Eyal Wilde <eyal@impactsoft.co.il>
2011-08-17 20:49 ` Re: index not being used when variable is sent Jim Nasby <jim@nasby.net>
0 siblings, 1 reply; 4+ messages in thread
From: Eyal Wilde @ 2011-08-17 06:49 UTC (permalink / raw)
To: 25601.1313502058@sss.pgh.pa.us; pgsql-performance; +Cc: erans@impactsoft.co.il, Tom Lane <tgl@sss.pgh.pa.us>
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:
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index not being used when variable is sent
2011-08-16 04:30 index not being used when variable is sent Eyal Wilde <eyal@impactsoft.co.il>
2011-08-16 13:40 ` Re: index not being used when variable is sent Tom Lane <tgl@sss.pgh.pa.us>
2011-08-17 06:49 ` Re: index not being used when variable is sent Eyal Wilde <eyal@impactsoft.co.il>
@ 2011-08-17 20:49 ` Jim Nasby <jim@nasby.net>
0 siblings, 0 replies; 4+ messages in thread
From: Jim Nasby @ 2011-08-17 20:49 UTC (permalink / raw)
To: Eyal Wilde <eyal@impactsoft.co.il>; +Cc: 25601.1313502058@sss.pgh.pa.us, pgsql-performance@postgresql.org, erans@impactsoft.co.il, Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-08-17 20:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-16 04:30 index not being used when variable is sent Eyal Wilde <eyal@impactsoft.co.il>
2011-08-16 13:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-08-17 06:49 ` Eyal Wilde <eyal@impactsoft.co.il>
2011-08-17 20:49 ` Jim Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox