Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cyL8k-0004rd-Dq for pgsql-sql@arkaria.postgresql.org; Wed, 12 Apr 2017 16:29:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cyL8j-0007Wc-GF for pgsql-sql@arkaria.postgresql.org; Wed, 12 Apr 2017 16:29:05 +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 1cyL8i-0007WK-ER for pgsql-sql@postgresql.org; Wed, 12 Apr 2017 16:29:04 +0000 Received: from mail-pg0-x234.google.com ([2607:f8b0:400e:c05::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1cyL8f-0000TI-37 for pgsql-sql@postgresql.org; Wed, 12 Apr 2017 16:29:02 +0000 Received: by mail-pg0-x234.google.com with SMTP id 72so8809658pge.2 for ; Wed, 12 Apr 2017 09:29:00 -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=fo3PrgSPoBBDfBd81AXfHDcWGTPN6Otb567eL42Swbs=; b=AOKo2I3OenKZonh65Bu1KkYOzyJgRcFhTHPfn9lW4//2tJZq07YmPS/UCtv21pQjBn gSG3WHv0eKeTDird/cqqzurV/MUKj6cVAKAaw+yFImCkBwkX7Syw5ex+VrJ5jnwpckc7 0EFOoixBPVZQy8zFlf8UUNzCfe8R1oBlU3xt+5uP8qkr+uyjbeBK04r2m/anhO5Kvd76 nN8+8CjSbyqBndaWUXFKNZDngsF3veIsGgsFMh6Uz/hSjsTXqpyLVF/mYgkGLDdOjdnt ZyqUrowMDgONgWV5egZNxEcDIPtwU2haEsPGa7gVguUUmDebggG1LvJWDwNfHQZZh5xp F60g== 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=fo3PrgSPoBBDfBd81AXfHDcWGTPN6Otb567eL42Swbs=; b=BQ13MY88jWG0mZz46UjVNJ7eyY9c/mIpK4tNTiiOGlCy3MiZT7qilield2ifsaqGys 5ZNJsD8st6iAiHcq4+0f9+M/capcW0RQCnkYnrhrvwQLJdGrvdigCoFqvU0wIQVhFfGP dKi/VKtEU3BCqXCpSzo11p+rVE42Kor/GKfDLOgdIce5DlcegagcqEG+ZznC2gYgsv+P ARJ0VTJT6g5abaP+epDRFY8i5S3x7SWizPVckxprHa7CUDh0QS3MVH5N73OT7rRafyNq rHfvV8KjkXr095+gBmk3c+J4luu9XD3SjWWiYZgJw/Paw0xfGPYmc7RlR3xFR5vk44Ln /PYw== X-Gm-Message-State: AFeK/H24Uqnv2k97PMnimL4u0nQgB/wTFhosh1sNU2yVYaxiZaDIDo2KvgZ++QB5ZJYB/w== X-Received: by 10.84.210.228 with SMTP id a91mr83138952pli.120.1492014539376; Wed, 12 Apr 2017 09:28:59 -0700 (PDT) Received: from [155.100.214.120] ([155.100.214.120]) by smtp.gmail.com with ESMTPSA id h25sm37530632pfk.119.2017.04.12.09.28.57 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 12 Apr 2017 09:28:58 -0700 (PDT) Subject: Re: CTEs and re-use To: Rosser Schwarz References: Cc: "pgsql-sql@postgresql.org" From: Rob Sargent Message-ID: Date: Wed, 12 Apr 2017 10:29:25 -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="------------6ABFAB969699ADE37ACF8BBE" 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. --------------6ABFAB969699ADE37ACF8BBE Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 04/11/2017 10:04 PM, Rosser Schwarz wrote: > On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent > wrote: > > I have a lovely little CTE/select doing exactly what I need it to > do. Unfortunately I need its results in the next query. > > > Can't you just chain the CTEs? E.g., > > with segset as ( > --... > ) > , optmarkers as ( > select m.id as mkrid > --... > group by m.id > ) > select s.id , o.optval, min(m.basepos) as firstbase > from optmarkers o > --... > order by firstbase; > > No temp table to drop. > > rls > > -- > :wq In your chaining suggestion, are you thinking "optmarkers" uses "segset"?, as I have in [2] below? I also tried it with optmarkers including segset [1]. Both have the same horrible performance as seen with an in-lining of the single CTE. I haven't done the explains to see where the confusion is but clearly CTE fencing needs to be discrete. [1] Nested CTE attempt with final as( with segset as ( select s.id , s.chrom , 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 = 'b474655c-80d2-47e7-bcb5-c65245195888' and o.name = '709' ) select m.id as mkrid , min(ss.pval) as optval from segset ss join seg.markerset_member mm on ss.markerset_id = mm.markerset_id join seg.marker m on mm.member_id = m.id where m.basepos between ss.startbase and ss.endbase and m.chrom = ss.chrom and mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset group by m.id ) select s.id, f.optval, min(m.basepos) as firstbase from final f join seg.marker m on f.mkrid = m.id join seg.markerset_member mm on m.id = mm.member_id join seg.segment s on mm.markerset_id = s.markerset_id where mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset and m.basepos between s.startbase and s.endbase and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) = f.optval group by s.id, f.optval order by firstbase; [2] Chained attempt with segset as ( select s.id , s.chrom , 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 = 'b474655c-80d2-47e7-bcb5-c65245195888' and o.name = '709' ), final as( select m.id as mkrid , min(ss.pval) as optval from segset ss join seg.markerset_member mm on ss.markerset_id = mm.markerset_id join seg.marker m on mm.member_id = m.id where m.basepos between ss.startbase and ss.endbase and m.chrom = ss.chrom and mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset group by m.id ) select s.id, f.optval, min(m.basepos) as firstbase from final f join seg.marker m on f.mkrid = m.id join seg.markerset_member mm on m.id = mm.member_id join seg.segment s on mm.markerset_id = s.markerset_id where mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset and m.basepos between s.startbase and s.endbase and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) = f.optval group by s.id, f.optval order by firstbase; --------------6ABFAB969699ADE37ACF8BBE Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 04/11/2017 10:04 PM, Rosser Schwarz wrote:
On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent <robjsargent@gmail.com> wrote:
I have a lovely little CTE/select doing exactly what I need it to do.  Unfortunately I need its results in the next query.

Can't you just chain the CTEs? E.g.,

with segset as (
--...
)
, optmarkers as (
select m.id as mkrid
--... 
  group by m.id
)
select s.id, o.optval, min(m.basepos) as firstbase
  from optmarkers o 
--...
  order by firstbase;

No temp table to drop.

rls

--
:wq

In your chaining suggestion, are you thinking "optmarkers" uses "segset"?, as I have in [2] below?  I also tried it with optmarkers including segset [1]. 

Both have the same horrible performance as seen with an in-lining of the single CTE.  I haven't done the explains to see where the confusion is but clearly CTE fencing needs to be discrete.


[1] Nested CTE attempt
with final as(
     with segset as (
         select s.id
                , s.chrom
                , 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 = 'b474655c-80d2-47e7-bcb5-c65245195888'
              and o.name = '709'
     )
     select m.id as mkrid
            , min(ss.pval) as optval
     from segset ss
          join seg.markerset_member mm on ss.markerset_id = mm.markerset_id
          join seg.marker m on mm.member_id = m.id
     where
          m.basepos between ss.startbase and ss.endbase
          and m.chrom = ss.chrom
          and mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
     group by m.id
)
select s.id, f.optval, min(m.basepos) as firstbase
from final f
     join seg.marker m on f.mkrid = m.id
     join seg.markerset_member mm on m.id = mm.member_id
     join seg.segment s on mm.markerset_id = s.markerset_id
where mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
      and m.basepos between s.startbase and s.endbase
      and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) = f.optval
group by s.id, f.optval
order by firstbase;

[2] Chained attempt
with segset as (
    select s.id
           , s.chrom
           , 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 = 'b474655c-80d2-47e7-bcb5-c65245195888'
         and o.name = '709'
),
final as(
select m.id as mkrid
       , min(ss.pval) as optval
from segset ss
     join seg.markerset_member mm on ss.markerset_id = mm.markerset_id
     join seg.marker m on mm.member_id = m.id
where
     m.basepos between ss.startbase and ss.endbase
     and m.chrom = ss.chrom
     and mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
group by m.id
)
select s.id, f.optval, min(m.basepos) as firstbase
from final f
     join seg.marker m on f.mkrid = m.id
     join seg.markerset_member mm on m.id = mm.member_id
     join seg.segment s on mm.markerset_id = s.markerset_id
where mm.markerset_id = 'b474655c-80d2-47e7-bcb5-c65245195888' -- mkset
      and m.basepos between s.startbase and s.endbase
      and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less + s.events_equal + s.events_greater))) = f.optval
group by s.id, f.optval
order by firstbase;

 --------------6ABFAB969699ADE37ACF8BBE--