From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: death of array? |
Date: | 2017-04-10 14:41:25 |
Message-ID: | BB39A931-A26F-4A0F-ACB3-8EBE5119973A@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2017-04-12 00:41:22 | CTEs and re-use |
Previous Message | Achilleas Mantzios | 2017-04-10 06:50:16 | Re: death of array? |