Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cxTAe-0000Q4-Gu for pgsql-sql@arkaria.postgresql.org; Mon, 10 Apr 2017 06:51:28 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cxTAe-0002Xx-1J for pgsql-sql@arkaria.postgresql.org; Mon, 10 Apr 2017 06:51:28 +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 1cxT9c-0000mV-Tr for pgsql-sql@postgresql.org; Mon, 10 Apr 2017 06:50:24 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cxT9Z-0003aD-NT for pgsql-sql@postgresql.org; Mon, 10 Apr 2017 06:50:24 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v3A6oGPq093981 for ; Mon, 10 Apr 2017 09:50:18 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: death of array? To: pgsql-sql@postgresql.org References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com> <58E7319B.9090306@matrix.gatewaynet.com> <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com> From: Achilleas Mantzios Message-ID: <58EB2B28.7090507@matrix.gatewaynet.com> Date: Mon, 10 Apr 2017 09:50:16 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com> Content-Type: multipart/alternative; boundary="------------030203020500000501030100" X-Pg-Spam-Score: -1.9 (-) 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. --------------030203020500000501030100 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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, 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). 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. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt --------------030203020500000501030100 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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, 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).

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.


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--------------030203020500000501030100--