agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: CTEs and re-use
Date: Tue, 11 Apr 2017 18:41:22 -0600
Message-ID: <c0a06409-5cca-901b-48f8-2740f38eaba4@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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-killed intolerable.
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 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 = 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.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))) = o.optval
group by s.id, o.optval
order by firstbase;
end;
$$ language plpgsql;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (6+ messages) latest in thread
Message-ID: <c0a06409-5cca-901b-48f8-2740f38eaba4@gmail.com>
Permalink: ../c0a06409-5cca-901b-48f8-2740f38eaba4@gmail.com/
Also on: postgresql.org/message-id/c0a06409-5cca-901b-48f8-2740f38eaba4@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
Subject: Re: CTEs and re-use
In-Reply-To: <c0a06409-5cca-901b-48f8-2740f38eaba4@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