> From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of ldh@xxxxxxxxxxxxxxxxxx > Sent: Freitag, 11. November 2016 07:54 > To: pgsql-performance@xxxxxxxxxxxxxx > Subject: Inlining of functions (doing LIKE on an array) > > 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 > Hi, are you using GIN indexes? http://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns moreover your query can still be optimized: => select count(*) from claims where exists (select * from unnest("ICD9_DGNS_CD") x_ where x_ like '427%' ) regards, Marc Mamin > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
