Re: CTEs and re-use

On 04/11/2017 10:04 PM, Rosser Schwarz wrote:
> On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)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 <http://m.id/> as mkrid
> --...
> group by m.id <http://m.id/>
> )
> select s.id <http://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;