agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: death of array?
Date: Fri, 7 Apr 2017 09:28:43 +0300
Message-ID: <58E7319B.9090306@matrix.gatewaynet.com> (raw)
In-Reply-To: <CAKFQuwZeaKm7xo-bFDSzMbB2=yxHvxeFXAc2vSSmYoVCeuHyZQ@mail.gmail.com>
References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com>
	<CAKFQuwZeaKm7xo-bFDSzMbB2=yxHvxeFXAc2vSSmYoVCeuHyZQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

On 07/04/2017 06:02, David G. Johnston wrote:
> On Thu, Apr 6, 2017 at 7:32 PM, Rob Sargent <robjsargent@gmail.com <mailto: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 <http://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

view thread (11+ messages)  latest in thread

Message-ID: <58E7319B.9090306@matrix.gatewaynet.com>
Permalink:  ../58E7319B.9090306@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/58E7319B.9090306@matrix.gatewaynet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com
  Subject: Re: death of array?
  In-Reply-To: <58E7319B.9090306@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox