agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: Rosser Schwarz <rosser.schwarz@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: CTEs and re-use
Date: Wed, 12 Apr 2017 10:29:25 -0600
Message-ID: <e877b29e-9bcb-9d23-5477-dbc9f3d1a6ac@gmail.com> (raw)
In-Reply-To: <CAFnxYwhpbiPJo6-To8oHw5NgA3TvaA2EUxma=QjjYDVvAB2ZQA@mail.gmail.com>
References: <c0a06409-5cca-901b-48f8-2740f38eaba4@gmail.com>
<CAFnxYwhpbiPJo6-To8oHw5NgA3TvaA2EUxma=QjjYDVvAB2ZQA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 04/11/2017 10:04 PM, Rosser Schwarz wrote:
> On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent <robjsargent@gmail.com
> <mailto: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 <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;
view thread (6+ messages)
Message-ID: <e877b29e-9bcb-9d23-5477-dbc9f3d1a6ac@gmail.com>
Permalink: ../e877b29e-9bcb-9d23-5477-dbc9f3d1a6ac@gmail.com/
Also on: postgresql.org/message-id/e877b29e-9bcb-9d23-5477-dbc9f3d1a6ac@gmail.com
reply
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, rosser.schwarz@gmail.com
Subject: Re: CTEs and re-use
In-Reply-To: <e877b29e-9bcb-9d23-5477-dbc9f3d1a6ac@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox