Re: death of array?

On 04/07/2017 10:02 AM, David G. Johnston wrote:
> On Fri, Apr 7, 2017 at 8:57 AM, Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>>wrote:
>
> Understood but true if any match is found, I need every array
> member to match as I want them all to be of a specific people as
> input into the query (so field = all(array_col)) and "field" here
> would be people_member.person_id and using that yields zero
> results in full query (very quickly though).
>
>
> ​I didn't actually attempt to comprehend your original email. If you
> want to supply a self-contained, functioning, query and expected
> output (ideally something simpler but that covers your main question)
> I'd be inclined to dig further. Even a broken one with what you think
> should work would be
>
> person = ALL(persons) doesn't really seem like it will typically work.
>
> Here are the various array operators supplied by PostgreSQL. You
> might find one of them helpful. In particular "contains".
>
> https://www.postgresql.org/docs/9.6/static/functions-array.html
>
> David J.
>
> ​

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