Re: death of array?

Actually that index is not expected, by me at least, to be involved in this join. (I added the uuid gin as described in the archives. I'm using Postgres 9.6)

> On Apr 10, 2017, at 12:50 AM, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
>
>> 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(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
>>>>> --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