From: Rob Sargent <robjsargent@gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\))
Date: Thu, 6 Apr 2017 20:32:31 -0600
Subject: death of array?
Message-Id: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

I believe I have an appropriate use[1] for an array column, but I=E2=80=99m=
 having a hard time using that array in a join clause.
The SQL question is how to use an array value in the join clause?  I=E2=80=
=99m using postgres 9.6 on ubuntu 16.04


create table probandset (id UUID, probands UUID[])
create table segment(id uuid, chr int, sbp int, epb int, probandset_id)
create table people_member(people_id uuid, person_id uuid)
create table people(id uuid, name text)
create table person(id uuid, name text)

probandset.probands is a set of person.id

I need to gather all segments whose probandset is within in a specified peo=
ple.
select s.* from segment s
  join probandset ps on s.probandset_id =3D ps.id
--PROBLEM: WOULD LIKE SOMETHING BETTER THAN THE FOLLOWING:
  join (select id, unnest(probands) as proband from probandset as l) as pu =
on s.probandset_id =3D pu.id
  join people_member pm on pu.proband =3D pm.person_id
  join people pl on pm.people_id =3D pl.id
where pl.name =3D =E2=80=98target population name=E2=80=99

The query I have works (showing only half of it here) and I=E2=80=99m not p=
ushing the performance of it too much right now as I=E2=80=99m more interes=
ted in the SQL problem. However, I am getting a seq scan on people_member, =
not surprisingly.  People_member will be blocks of people, 50 to 1000 per b=
lock and each block loaded in a single transaction, no editing: should this=
 be clustered (and reclustered)? Over time would the seq scan go away as pe=
ople_member.people_id becomes more discriminating? (There is an index on pe=
ople_member.people_id). A people has a know set of probands (and we need al=
l subsets of those probands)

Current discussions at our end on whether or not a probandset may be filled=
 with members of more than one people.  If not I might be able to add peopl=
e_id to probandset and I am home free.
But that still doesn=E2=80=99t answer the SQL question.

Thanks for reading, sorry it=E2=80=99s a tad wordy.
rjs

[1] I=E2=80=99m dealing with power sets. A given set of N element has 2**N =
subsets and the number of subset grows exponentially (obviously).  But to m=
odel this =E2=80=98normally=E2=80=99 would require an even larger number of=
 subset member records. (That summation left to the student[2]).  My choice=
 was to list each subset once with and array of elements.  Still an exponen=
tial problem, but only one exponential problem.
It all breaks down somewhere between 20 and 30 elements but we'll burn that=
 bridge when we get there.
[2] Something like the sum over i=3D0..N of ((N choose i) * i maybe?

--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql