From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: death of array? |
Date: | 2017-04-07 06:28:43 |
Message-ID: | 58E7319B.9090306@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 07/04/2017 06:02, David G. Johnston wrote:
> On Thu, Apr 6, 2017 at 7:32 PM, Rob Sargent <robjsargent(at)gmail(dot)com <mailto:robjsargent(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2017-04-07 15:22:09 | Re: death of array? |
Previous Message | David G. Johnston | 2017-04-07 03:02:53 | Re: death of array? |