Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cxaWc-0004vn-Bv for pgsql-sql@arkaria.postgresql.org; Mon, 10 Apr 2017 14:42:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cxaWb-0004AM-U3 for pgsql-sql@arkaria.postgresql.org; Mon, 10 Apr 2017 14:42:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cxaVa-0002HM-72 for pgsql-sql@postgresql.org; Mon, 10 Apr 2017 14:41:34 +0000 Received: from mail-io0-x22f.google.com ([2607:f8b0:4001:c06::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cxaVW-0004xR-9r for pgsql-sql@postgresql.org; Mon, 10 Apr 2017 14:41:33 +0000 Received: by mail-io0-x22f.google.com with SMTP id l7so97462194ioe.3 for ; Mon, 10 Apr 2017 07:41:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=rY0ZGztuwsG/CEoEPTg9BoBpXro6SwpCmckjx1TFtVY=; b=N/JWEI7UFbaOg2XSs3crOuYTZ5IqmYtVMJLeJiwdmgUEd3ff6FEAhLtSkK0XmF4+6Y Rbwlp+E9eU7jFK8JzKbqRrLWcLTqKeSpG1hPHVyCd6WDJrLvAdUIOWZDzjgI+lBCLS8X +OmxpBBExlTHK27QsYE5P3dFFW3U+y1phxvIMGAmZR5inNVZxmnF/SEsyGZqPuYfjmDB 3dKYErJVPhBBd6w+Ccs5GSJxeCxVcLKmdM3BxuQ5RlXrbataOPBpPT4tCQeGPvkMaLPn OpBXOg1T7Dwp3rP+75y7jhzbERYpwNPX3YsRnwJAiy51uF1U8eiUUJWMGVOnWjEI2VVB IbtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :content-transfer-encoding:message-id:references:to; bh=rY0ZGztuwsG/CEoEPTg9BoBpXro6SwpCmckjx1TFtVY=; b=Gibgzdw04CnpFYZh7v4vkErxPawj6qGDL830trVOSHg1o7u5xPdd3iqZJZH8hJFbrJ iDT864xTfQb7h/nVLeauFeuivLv3dVkOHueqixFx9puGMFR4si+UGipYZ6rajVzFOJOl kAgkFOidc7hySbmRmWiwLAeUDSDS9PX/4oDQmShR07GgxBGgjtBgZGA22eIv7nZBOawI 9/xHWj0mt7Ao/4PddjPNbWMPJd2ys9Km2fntY9t7ulAj9YX2wcDvtiRJ1VTTz+Z7jG26 fl0N5AjIHbSKz6ts5zx0YE2nG7pjb1fP6MihM1yLfACLvuXSdwF+6RpYGpBka4om05Oq byjw== X-Gm-Message-State: AFeK/H268pnLyDq+w+4zCB/H/Ya7UCieNEOgOyCQfJfEHIvu7H7Okmv9gVMo0QpVJtY08g== X-Received: by 10.107.44.23 with SMTP id s23mr56664016ios.229.1491835287924; Mon, 10 Apr 2017 07:41:27 -0700 (PDT) Received: from [172.31.98.58] (50-243-0-163-static.hfc.comcastbusiness.net. [50.243.0.163]) by smtp.gmail.com with ESMTPSA id w192sm3650826ith.7.2017.04.10.07.41.26 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 10 Apr 2017 07:41:26 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-1C2E4793-639F-4EB7-919E-A8D9AEAF6A0F Mime-Version: 1.0 (1.0) Subject: Re: death of array? From: Rob Sargent X-Mailer: iPhone Mail (14D27) In-Reply-To: <58EB2B28.7090507@matrix.gatewaynet.com> Date: Mon, 10 Apr 2017 08:41:25 -0600 Cc: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit Message-Id: References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com> <58E7319B.9090306@matrix.gatewaynet.com> <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com> <58EB2B28.7090507@matrix.gatewaynet.com> To: Achilleas Mantzios X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --Apple-Mail-1C2E4793-639F-4EB7-919E-A8D9AEAF6A0F Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Actually that index is not expected, by me at least, to be involved in this j= oin. (I added the uuid gin as described in the archives. I'm using Postgres 9= .6) > On Apr 10, 2017, at 12:50 AM, Achilleas Mantzios wrote: >=20 >> On 07/04/2017 18:22, Rob Sargent wrote: >>=20 >>=20 >>> On 04/07/2017 12:28 AM, Achilleas Mantzios wrote: >>>> On 07/04/2017 06:02, David G. Johnston wrote: >>>> On Thu, Apr 6, 2017 at 7:32 PM, Rob Sargent wro= te: >>>>>=20 >>>>> I need to gather all segments whose probandset is within in a specifie= d people. >>>>> select s.* from segment s >>>>> join probandset ps on s.probandset_id =3D ps.id >>>>> --PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING: >>>>=20 >>>> =E2=80=8BSELECT s.* implies semi-joins - so lets see how that would wor= k. >>>>=20 >>>> SELECT vals.*=20 >>>> FROM ( VALUES (2),(4) ) vals (v) >>>> WHERE EXISTS ( >>>> SELECT 1 FROM ( VALUES (ARRAY[1,2,3]::integer[]) ) eyes (i) >>>> WHERE v =3D ANY(i) >>>> ); >>>> // 2 >>>=20 >>> I never understood the love for UUID keys, If he changes UUID for int, i= nstall intarray and create this index : >>> CREATE INDEX probandset_probands_gistsmall ON probandset USING gin (prob= ands gin__int_ops); >>> then he'll be able to do=20 >>> .... WHERE .... intset(people_member.personid) ~ probandset.probands ...= >>> That would boost performance quite a lot. (in my tests 100-fold) >>>=20 >>>>=20 >>>> =E2=80=8BHTH >>>>=20 >>>> David J. >>>>=20 >>>=20 >>>=20 >>> --=20 >>> Achilleas Mantzios >>> IT DEV Lead >>> IT DEPT >>> Dynacom Tankers Mgmt >> Thank you both for your suggestions, but does either apply to joining thr= ough the array in a flow of join operations? Or must I do the work on the a= rray in the where clause?=20 >>=20 >> I do have a gin index on probandset(probands). >=20 > Can you give the definition of this index? Does it get used ? Did you veri= fy with EXPLAIN ANALYZE ? > At least in 9.3, AFAIK uuid[] has no operator class for access method "gin= ", unless I am missing smth. >=20 >>=20 >> rjs >>=20 >> We can discuss my love of UUID in a separate thread ;) but the short form= is that I'm awash in separate id domains starting from 1 (or maybe 75000000= 0) and am not about to add another. >> rj. >=20 >=20 > --=20 > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt --Apple-Mail-1C2E4793-639F-4EB7-919E-A8D9AEAF6A0F Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Actually that index is not e= xpected, by me at least, to be involved in this join. (I added the uuid gin a= s described in the archives. I'm using Postgres 9.6)

On Apr 10= , 2017, at 12:50 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

=20 =20 =20
On 07/04/2017 18:22, Rob Sargent wrote:



On 04/07/2017 12:28 AM, Achilleas Mantzios wrote:
On 07/04/2017 06:02, David G. Johnston wrote:
On Thu, Apr 6, 20= 17 at 7:32 PM, Rob Sargent <robjsargent@gma= il.com> wrote:

I need to gather all segments whose probandset is within in a specified people.
select s.* from segment s
  join probandset ps on s.probandset_id =3D ps.id
--PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING:

=E2=80=8BSELECT s.* implies semi-joins - so lets see how that would work.

SELECT vals.* 
FROM ( VALUES (2),(4) ) vals (v)
WHERE EXISTS (
    SELECT 1 FROM ( VALUES (ARRAY[1,2,3]::integer[]) ) eyes (i)
        WHERE v =3D ANY(i)
);
// 2

I never understood the love for UUID keys, If he changes UUID for int, install intarray and create this index :
CREATE INDEX probandset_probands_gistsmall ON probandset USING gin (probands gin__int_ops);
then he'll be able to do
.... WHERE .... intset(people_member.personid) ~ probandset.probands ...
That would boost performance quite a lot. (in my tests 100-fold)
=

=E2=80=8BHTH

David J.



--=20
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
Thank you both for your suggestions, but does either apply to joining through the array in a flow of join operations?  Or must I= do the work on the array in the where clause?

I do have a gin index on probandset(probands).

Can you give the definition of this index? Does it get used ? Did you verify with EXPLAIN ANALYZE ?
At least in 9.3, AFAIK uuid[] has no operator class for access method "gin", unless I am missing smth.


rjs

We can discuss my love of UUID in a separate thread ;) but the short form is that I'm awash in separate id domains starting from 1 (or maybe 750000000) and am not about to add another.
rj.


--=20
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
=20
= --Apple-Mail-1C2E4793-639F-4EB7-919E-A8D9AEAF6A0F--