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
