From: Tom Lane <tgl@sss.pgh.pa.us>
To: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
cc: Marc Mamin <M.Mamin@intershop.de>,
        "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Inlining of functions (doing LIKE on an array)
In-reply-to: 
 <35BE9BD1DD4DD444B0EDE3721E2AEA860115D550@P3PWEX4MB001.ex4.secureserver.net>
References: 
 <35BE9BD1DD4DD444B0EDE3721E2AEA860115CB08@P3PWEX4MB001.ex4.secureserver.net>
 <B6F6FD62F2624C4C9916AC0175D56D88421F197B@jenmbs01.ad.intershop.net>
 <35BE9BD1DD4DD444B0EDE3721E2AEA860115D550@P3PWEX4MB001.ex4.secureserver.net>
Comments: In-reply-to "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
	message dated "Fri, 11 Nov 2016 16:14:08 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <29681.1478882784.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 11 Nov 2016 11:46:24 -0500
Message-ID: <29682.1478882784@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

"ldh@laurent-hasson.com" <ldh@laurent-hasson.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=3D# CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS b=
ool
regression-# as 'select exists (select * from unnest($1) a where a like $2)'
regression-# LANGUAGE SQL STRICT IMMUTABLE;
CREATE FUNCTION
regression=3D# create table tt (f1 text[]);
CREATE TABLE
regression=3D# explain select * from tt where ArrayLike(f1, 'foo');
                      QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20
-------------------------------------------------------
 Seq Scan on tt  (cost=3D0.00..363.60 rows=3D453 width=3D32)
   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 ma=
ke a difference, which I guess is expected as well because of the Like oper=
ator. I don't expect regular indices to work on regular columns for Like op=
erations, especially '%xxx' ones, so I didn't expect GIN indices to work ei=
ther 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


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance