From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> |
Cc: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Inlining of functions (doing LIKE on an array) |
Date: | 2016-11-11 16:46:24 |
Message-ID: | 29682.1478882784@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> writes:
> I tried "exists", but won't work in the Function, i.e.,
> CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bigint
> AS 'exists (select * from unnest($1) a where a like $2)'
> LANGUAGE SQL STRICT IMMUTABLE LEAKPROOF
Syntax and semantics problems. This would work:
regression=# CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bool
regression-# as 'select exists (select * from unnest($1) a where a like $2)'
regression-# LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION
regression=# create table tt (f1 text[]);
CREATE TABLE
regression=# explain select * from tt where ArrayLike(f1, 'foo');
QUERY PLAN
-------------------------------------------------------
Seq Scan on tt (cost=0.00..363.60 rows=453 width=32)
Filter: arraylike(f1, 'foo'::text)
(2 rows)
But we don't inline SQL functions containing sub-selects, so you're still
stuck with the rather high overhead of a SQL function. A plpgsql function
might be a bit faster:
CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bool
as 'begin return exists (select * from unnest($1) a where a like $2); end'
LANGUAGE plpgSQL STRICT IMMUTABLE;
BTW, I'd be pretty suspicious of marking this function leakproof,
because the underlying LIKE operator isn't leakproof according to
pg_proc.
> It's as expected though. As for the GIN indices, I tried and it didn't make a difference, which I guess is expected as well because of the Like operator. I don't expect regular indices to work on regular columns for Like operations, especially '%xxx' ones, so I didn't expect GIN indices to work either for Array columns with Like. Am I wrong?
Plain GIN index, probably not. A pg_trgm index could help with LIKE
searches, but I don't think we have a variant of that for array columns.
Have you considered renormalizing the data so that you don't have
arrays?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2016-11-11 23:32:33 | Re: Inlining of functions (doing LIKE on an array) |
Previous Message | Andreas Karlsson | 2016-11-11 16:22:37 | Re: Any advice tuning this query ? |