On 04/07/2017 10:02 AM, David G. Johnston wrote:
Well you prodding got me a seriously reduced execution time. Here's the actual query. The tables are all in the 'seg' schema and not substantively different than described in first post.
create temp table opt10aut
as
with optset as (
select s.id
, s.markerset_id
, s.startbase
, s.endbase
, ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) as pval
from seg.segment s
join seg.probandset i on s.probandset_id = i.id
join (select people_id, array_agg(person_id) as persons from seg.people_member group by people_id) as pa on i.probands <@ pa.persons
join seg.people o on pa.people_id = o.id
where
s.markerset_id = '61801888-9a81-4187-922c-4d42c0471bea'
and o.name = '709'
)
select m.name
, min(pval)
from optset op
join seg.markerset_member mm on op.markerset_id = mm.markerset_id
join seg.marker m on mm.member_id = m.id
where
m.basepos between op.startbase and op.endbase
group by m.name
It is true that this particular people has only 60 members. Our upper end would be ~1000.
rjs