agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: death of array?
Date: Fri, 7 Apr 2017 09:22:09 -0600
Message-ID: <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com> (raw)
In-Reply-To: <58E7319B.9090306@matrix.gatewaynet.com>
References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com>
<CAKFQuwZeaKm7xo-bFDSzMbB2=yxHvxeFXAc2vSSmYoVCeuHyZQ@mail.gmail.com>
<58E7319B.9090306@matrix.gatewaynet.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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 <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
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.
view thread (11+ messages) latest in thread
Message-ID: <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com>
Permalink: ../5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com/
Also on: postgresql.org/message-id/5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.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: robjsargent@gmail.com
Subject: Re: death of array?
In-Reply-To: <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.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