Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cwWaS-0002Cl-OY for pgsql-sql@arkaria.postgresql.org; Fri, 07 Apr 2017 16:18:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cwWaR-0003WA-EC for pgsql-sql@arkaria.postgresql.org; Fri, 07 Apr 2017 16:18:11 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cwWZP-0001fX-5T for pgsql-sql@postgresql.org; Fri, 07 Apr 2017 16:17:07 +0000 Received: from mail-pg0-x232.google.com ([2607:f8b0:400e:c05::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cwWZH-0004hE-BO for pgsql-sql@postgresql.org; Fri, 07 Apr 2017 16:17:05 +0000 Received: by mail-pg0-x232.google.com with SMTP id x125so71001277pgb.0 for ; Fri, 07 Apr 2017 09:16:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to; bh=drmF2Px69QV1GrgnEKbFKBfwxSRRfjK5KrI5iKHaBlU=; b=ite7VWMzwHR4p6vtTSM77RsbZzWd/UzcoVv+B+mHLy670Ayv/Mhsnrlq4WY36HOOiP wKJTIb3+iTF4hKPCNu1y7qKdpoHkzC4KTnBc14ea1oqjJoPFrL9oxBFpbcvEvjkKlzrW mTya6BPKI1Xg70Xknq8J0NYUwr1Z9CSip9jxuE6/kiOm8jA7fcjXpUVE0UkL2dFk9q+v bGLzqiq9jmW2FuXxW74PrkAnSLhGzftj++aVp+cxLzf9GQsGPXNZklyvEZwxywM38ks5 BLZd0LgMe/0fx2ECfkA+KjBq7VnpqsZfjzcqUs0fxH0pNqKpGeCP9A0YVQCdiO5/7xus ItHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to; bh=drmF2Px69QV1GrgnEKbFKBfwxSRRfjK5KrI5iKHaBlU=; b=piNey9818YaiIabEI1IDJHS0rkuxl7z7v/UmjhFdq+DvCeT4tT9QVc4HZSvQM+ZKqI RInYGU+4TSNJquScqe8Jc21ifLpRyzbbZyfMDeE8A3BNGY7iJaS3IVExNv+gAGwQWKAU C2C4Dpiowy4MX8JSddOCvbvBTDpI6JKDOGOINaX3XxtJYNQfiWmrZyUXulsCIHGTJwE3 p7Q+nz1hSUyI6wyVRLWGaVNR1E2RoSFgqQFsltSDanpoL2OyCx9AX4apExR2nALJauVu GGeSkhWS0I/h9ggKs0nCmPKuWQWiuineDSC5EUvUC+ubLMLopkAmn6WrKuu6Sdfioi4p LktA== X-Gm-Message-State: AN3rC/5UAqnfjT8UGWlLSrzViQ6Jbt2kFmiNcFmZl8ZxM8P+jqiUqc+/px/zfLKO7os3eA== X-Received: by 10.99.3.139 with SMTP id 133mr4922354pgd.222.1491581817915; Fri, 07 Apr 2017 09:16:57 -0700 (PDT) Received: from [155.100.214.120] ([155.100.214.120]) by smtp.gmail.com with ESMTPSA id u64sm10521495pfk.113.2017.04.07.09.16.56 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 07 Apr 2017 09:16:56 -0700 (PDT) Subject: Re: death of array? To: "David G. Johnston" References: <73B8E1AF-BD4A-4E6A-B192-8394D59EF47C@gmail.com> <58E7319B.9090306@matrix.gatewaynet.com> <5fef6cfe-a47f-4276-f95f-259fb661276a@gmail.com> Cc: "pgsql-sql@postgresql.org" From: Rob Sargent Message-ID: <223726fa-1013-6fde-b45b-70f5ed6e655f@gmail.com> Date: Fri, 7 Apr 2017 10:17:18 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------254E7DBC7366598270CEBDE1" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------254E7DBC7366598270CEBDE1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 04/07/2017 10:02 AM, David G. Johnston wrote: > On Fri, Apr 7, 2017 at 8:57 AM, Rob Sargent >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 --------------254E7DBC7366598270CEBDE1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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


--------------254E7DBC7366598270CEBDE1--