To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: Rob Sargent <robjsargent@gmail.com>
Subject: CTEs and re-use
Message-ID: <c0a06409-5cca-901b-48f8-2740f38eaba4@gmail.com>
Date: Tue, 11 Apr 2017 18:41:22 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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