Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cwVi0-0004i5-Fa for pgsql-sql@arkaria.postgresql.org; Fri, 07 Apr 2017 15:21:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cwVi0-00006r-25 for pgsql-sql@arkaria.postgresql.org; Fri, 07 Apr 2017 15:21:56 +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 1cwVhz-00006a-IW for pgsql-sql@postgresql.org; Fri, 07 Apr 2017 15:21:55 +0000 Received: from mail-pg0-x229.google.com ([2607:f8b0:400e:c05::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cwVhv-00081u-Fw for pgsql-sql@postgresql.org; Fri, 07 Apr 2017 15:21:55 +0000 Received: by mail-pg0-x229.google.com with SMTP id 21so69727825pgg.1 for ; Fri, 07 Apr 2017 08:21:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to; bh=6tZ6lSkBzdH7bWgIiWeqncYW1CyWb6l8viBPhHr+qi0=; b=ajjh6VHrkl86/ZUcMKUTS6u8+F7bwWu5ymz9BO92vPNH+kyhZ2gzkn/nS5/AHWjlFN C4bvDXwomgxQUPXbCCHZnh+laoyV1Bue8mSUSug0kMJeNBYQOAEBgJ6o4TCFIwvITA10 4ZIvFPoUc3K3do6xX1Mtdis+4QwPtIbEizEEx+kgNWEpAUC1XSdw2kh5YrsQMrh3qWzO c23lQ62xx2LY+zZumN+3CKL2BSQyJzqG9AcCHPxtL3Tebe2m5xNszWG+gH0+nYrONO8O rT36NnOnxj/Mk2uPxr5ssSjpP0JkOfxlPQtrsFy64UyypzoH7xb6tBValOGb9xd7FU8E ccFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to; bh=6tZ6lSkBzdH7bWgIiWeqncYW1CyWb6l8viBPhHr+qi0=; b=YXvbkgRQ/n3aKAGNvyf63PlVFdxMWMWobl20tbHGX2C9L42Vl9dCtos3tf+u5WNe4t TV9x1lSpmKLRSYmmP/Q5R9AsOVjPhJub3GKkKcIx7rsOjZtW2YG+nN8ZpQF7+R5HlPXI U7DL4YiBJQPkv/W0qosziimJMTNahExlT3RWsboyLlrRM5OspX+1Ll242A7AZJLm62fO cXUfWaUFcplVRKS6mV41VZxi4AzpOUuOdTJh8zcIbNZBFcckObeVsdOjYG2NVLmd/CxX 1s3DXbikcb3n7hZeVA1dyj0XfKDdKLjHCwNaGhVGlm/UPLdOBf7tTcbS8nex0dDVdti3 ILxQ== X-Gm-Message-State: AFeK/H1sXuBZEX03KtMAuthrYOx902YidCkh0ruS2UM204mWP2CCOTeHE0Sq0NWu0RIrLg== X-Received: by 10.84.217.149 with SMTP id p21mr49549569pli.59.1491578509116; Fri, 07 Apr 2017 08:21:49 -0700 (PDT) Received: from [155.100.214.120] ([155.100.214.120]) by smtp.gmail.com with ESMTPSA id x204sm9881992pgx.63.2017.04.07.08.21.47 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 07 Apr 2017 08:21:47 -0700 (PDT) Subject: Re: death of array? To: pgsql-sql@postgresql.org References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com> <58E7319B.9090306@matrix.gatewaynet.com> From: Rob Sargent Message-ID: <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com> Date: Fri, 7 Apr 2017 09:22:09 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: <58E7319B.9090306@matrix.gatewaynet.com> Content-Type: multipart/alternative; boundary="------------7C7DF07FA1D071FD416714FE" X-Pg-Spam-Score: -2.0 (--) 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 This is a multi-part message in MIME format. --------------7C7DF07FA1D071FD416714FE Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 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 = ps.id >> --PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING: >> >> >> ​SELECT 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 = 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) > >> >> ​HTH >> >> David J. >> > > > -- > 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). 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. --------------7C7DF07FA1D071FD416714FE Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit



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 <robjsargent@gmail.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 = ps.id
--PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING:

​SELECT 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 = 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)


​HTH

David J.



-- 
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).

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.
--------------7C7DF07FA1D071FD416714FE--