Обсуждение: [SQL] CTEs and re-use

I have a lovely little CTE/select doing exactly what I need it to do.  
Unfortunately I need its results in the next query. I have this in the 
function def below.

The gripe is that the function puts the results of the CTE/select into a 
temp table for the follow-on query.  That mean I have a name collision 
and have to drop the temp table.

I've tried in-lining the CTE/select put the performance is horrible. ( From 10 seconds (tolerable) to
over-a-minute-and-killedintolerable.  
 
The CTE is the long pole in the tent; running it standalone takes 9.9 
seconds).


What am I missing here in building the fence and losing the neighbours?

The CTE/select gives me the minimum value for all markers involved. The 
second part finds the "segment" from which that lowest p-value came, per 
marker. Then we reduce the list to distinct segment/p-value combinations.

create or replace function optimal_pvalue_set(people_name text, 
markers_name text, chr int)
returns table (segmentid uuid, optval numeric, firstbase int) as
$$
declare  mkset uuid;
begin  select id into mkset from seg.markerset where name = markers_name and 
chrom = chr;
  create temp table optmarkers 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
ons.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 = mkset           and o.name = people_name  )  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 = mkset  group by m.id;
 
  return query  select s.id, o.optval, min(m.basepos) as firstbase  from optmarkers o  ---
<<<<----------------------------Tried
 
in-lining the CTE here.       join seg.marker m on o.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 = mkset        and
m.baseposbetween s.startbase and s.endbase        and ((s.events_equal + s.events_greater)/(1.0 * (s.events_less 
 
+ s.events_equal + s.events_greater))) = o.optval  group by s.id, o.optval  order by firstbase;
end;
$$ language plpgsql;

> On Apr 11, 2017, at 8:43 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tuesday, April 11, 2017, Rob Sargent <robjsargent@gmail.com> wrote:
> If ‘on commit drop’ does the trick that clears the annoyance but still wondering if there’s a way to retain the
productof the CTE/select for further processing without resorting to a temp table. 
>
> A CTE is a same-query-only temp table.  If you need it to persist post query you have to use a real temporary table.
>
> David J.

Right then.  Thought I might be missing some nifty construct.  A little surprised but oh, well.

Thanks for taking the time.
rjs

> On Apr 11, 2017, at 8:47 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tuesday, April 11, 2017, Rob Sargent <robjsargent@gmail.com> wrote:
>
> I’m really just bumping into the annoyance of manually dropping the temp table as I work this up (though real
collisionin production would is possible, it would be unlikely) and thought to try to rework the function without the
temptable - that’s the SQL question - and presumed the specifics of the function would come in useful. 
>
>
> I'm not positive what you are thinking here but the names of temporary tables are session-unique.  They are not prone
toconcurrent use namespace collisions and not do not interfere with permanent tables either.  They are placed into a
temporaryschema that is only visible to the current session. 
>
> David J.
Of course ‘on commit drop’ works like a charm.
Thanks a ton.
rjs

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;