> From: pgsql-performance-ow...@postgresql.org
> [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of
> l...@laurent-hasson.com
> Sent: Freitag, 11. November 2016 07:54
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
