agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedCTEs and re-use
6+ messages / 3 participants
[nested] [flat]
* CTEs and re-use
@ 2017-04-12 00:41 Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rob Sargent @ 2017-04-12 00:41 UTC (permalink / raw)
To: 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-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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and re-use
@ 2017-04-12 01:47 David G. Johnston <david.g.johnston@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
1 sibling, 2 replies; 6+ messages in thread
From: David G. Johnston @ 2017-04-12 01:47 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent <robjsargent@gmail.com> wrote:
> 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.
> [...]
>
> What am I missing here in building the fence and losing the neighbours?
>
Best guess:
CREATE TEMP TABLE ... ON COMMIT DROP AS
or
DROP TABLE IF EXISTS ..
You show a lot of detail in the function but its not useful. Given the
stated problem your queries or "SELECT '1'::text" both will exhibit the
same behavior. What you don't show or describe, however, is how and when
the function is called.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and re-use
@ 2017-04-12 02:47 Rob Sargent <robjsargent@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2017-04-12 02:47 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
> On Apr 11, 2017, at 8:43 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tuesday, April 11, 2017, Rob Sargent <robjsargent@gmail.com> wrote:
> If ‘on commit drop’ does the trick that clears the annoyance but still wondering if there’s a way to retain the product of the CTE/select for further processing without resorting to a temp table.
>
> A CTE is a same-query-only temp table. If you need it to persist post query you have to use a real temporary table.
>
> David J.
Right then. Thought I might be missing some nifty construct. A little surprised but oh, well.
Thanks for taking the time.
rjs
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and re-use
@ 2017-04-12 03:05 Rob Sargent <robjsargent@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2017-04-12 03:05 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
> On Apr 11, 2017, at 8:47 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tuesday, April 11, 2017, Rob Sargent <robjsargent@gmail.com> wrote:
>
> I’m really just bumping into the annoyance of manually dropping the temp table as I work this up (though real collision in production would is possible, it would be unlikely) and thought to try to rework the function without the temp table - that’s the SQL question - and presumed the specifics of the function would come in useful.
>
>
> I'm not positive what you are thinking here but the names of temporary tables are session-unique. They are not prone to concurrent use namespace collisions and not do not interfere with permanent tables either. They are placed into a temporary schema that is only visible to the current session.
>
> David J.
Of course ‘on commit drop’ works like a charm.
Thanks a ton.
rjs
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and re-use
@ 2017-04-12 04:04 Rosser Schwarz <rosser.schwarz@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Rosser Schwarz @ 2017-04-12 04:04 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
On Tue, Apr 11, 2017 at 5:41 PM, Rob Sargent <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 as mkrid
--...
group by m.id
)
select s.id, o.optval, min(m.basepos) as firstbase
from optmarkers o
--...
order by firstbase;
No temp table to drop.
rls
--
:wq
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CTEs and re-use
@ 2017-04-12 16:29 Rob Sargent <robjsargent@gmail.com>
parent: Rosser Schwarz <rosser.schwarz@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2017-04-12 16:29 UTC (permalink / raw)
To: Rosser Schwarz <rosser.schwarz@gmail.com>; +Cc: pgsql-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;
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-04-12 16:29 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-04-12 00:41 CTEs and re-use Rob Sargent <robjsargent@gmail.com>
2017-04-12 01:47 ` David G. Johnston <david.g.johnston@gmail.com>
2017-04-12 02:47 ` Rob Sargent <robjsargent@gmail.com>
2017-04-12 03:05 ` Rob Sargent <robjsargent@gmail.com>
2017-04-12 04:04 ` Rosser Schwarz <rosser.schwarz@gmail.com>
2017-04-12 16:29 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox