From: Rob Sargent <robjsargent@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: death of array?
Date: Sat, 8 Apr 2017 15:07:43 -0600
Message-ID: <CED7D0FC-59B7-4F15-9F72-A879907BC210@gmail.com> (raw)
In-Reply-To: <223726fa-1013-6fde-b45b-70f5ed6e655f@gmail.com>
References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com>
<CAKFQuwZeaKm7xo-bFDSzMbB2=yxHvxeFXAc2vSSmYoVCeuHyZQ@mail.gmail.com>
<58E7319B.9090306@matrix.gatewaynet.com>
<5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com>
<CAKFQuwbiU4viib21wWKa3T_4w343MioOjeqxkeeRYkMD5hqsfA@mail.gmail.com>
<d1ce18b8-c5cb-9547-c0b3-8a4910f96483@gmail.com>
<CAKFQuwYZeUjUz1rBOJSCeO08zg1ugzOLVpPA8BuZVe5pvK1cnw@mail.gmail.com>
<223726fa-1013-6fde-b45b-70f5ed6e655f@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
> On Apr 7, 2017, at 10:17 AM, Rob Sargent <robjsargent@gmail.com> wrote:
>
>
> On 04/07/2017 10:02 AM, David G. Johnston wrote:
>> On Fri, Apr 7, 2017 at 8:57 AM, Rob Sargent <robjsargent@gmail.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
>
>
>
What I was hoping for, I guess, is a construct like
create table t(id int /*for Achilles*/, idset int[] references v.field)
and it would be understood that each element can be found in (preferably the index of) v.field
Oh, and an operator to make use of the knowledge something like
select * from t join v on t.idset “are_all_in” v.field;
Thanks to all,
rjs
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, david.g.johnston@gmail.com
Subject: Re: death of array?
In-Reply-To: <CED7D0FC-59B7-4F15-9F72-A879907BC210@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox