Hello,
I am trying to implement an efficient "like" over a text[]. I see a lot of
people have tried before me and I learnt a lot through the forums. The results
of my search is that a query like the following is optimal:
select count(*)
from claims
where (select count(*)
from unnest("ICD9_DGNS_CD") x_
where x_ like '427%'
) > 0
So I figured I'd create a Function to encapsulate the concept:
CREATE OR REPLACE FUNCTION ArrayLike(text[], text)
RETURNS bigint
AS 'select count(*) from unnest($1) a where a like $2'
LANGUAGE SQL STRICT IMMUTABLE LEAKPROOF
This works functionally, but performs like crap: full table scan, and cannot
make use of any index it seems. Basically, it feels like PG can't inline that
function.
I have been trying all evening to find a way to rewrite it to trick the
compiler/planner into inlining. I tried the operator approach for example, but
performance is again not good.
create function rlike(text,text)
returns bool as 'select $2 like $1' language sql strict immutable;
create operator ``` (procedure = rlike, leftarg = text,
rightarg = text, commutator = ```);
CREATE OR REPLACE FUNCTION MyLike(text[], text)
RETURNS boolean
AS 'select $2 ``` ANY($1)'
LANGUAGE SQL STRICT IMMUTABLE LEAKPROOF
And by not good, I mean that on my table of 2M+ rows, the "native" query takes
3s, while the function version takes 9s and the operator version takes (via the
function, or through the operator directly), takes 15s.
Any ideas or pointers?
Thank you,
Laurent Hasson
