Re: death of array?

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 <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).

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