[SQL] CTEs and re-use - Mailing list pgsql-sql
From | Rob Sargent |
---|---|
Subject | [SQL] CTEs and re-use |
Date | |
Msg-id | c0a06409-5cca-901b-48f8-2740f38eaba4@gmail.com Whole thread Raw |
Responses |
Re: [SQL] CTEs and re-use
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: [SQL] CTEs and re-use (Rosser Schwarz <rosser.schwarz@gmail.com>) |
List | pgsql-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-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;