Re: [SQL] death of array? - Mailing list pgsql-sql

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