I have three very similar functions, two of which are
fast and one is slow and I cannot explain the slowness of
the third. All of which, with the correct arguments should return
the same "optimal set". I present one of the two fast
versions and the slow one, in whole and in part. I'm
using postgres 9.6 on ubuntu 16.4 desktop (16G memory).
I'm confused because the bulk of the difference in the sql is in
the "with" clause and those are equally quick and return the same
dataset, and to my eye the processing after that is the same as
sql but clearly not in the server. I'm missing something obvious
I'm sure but whatever it is is causing a nasty Cartesian then
sorting that out.
(And I get that mm.markerset_id is not needed in the where clause)
Any insights appreciated.
Thanks,
rjs
I have not included table defs, thinking that since they're the
same for both queries ...
Fast function
create or replace function
seg.optimal_pvalue_set(people_name text, markers_name text, chr
int)
returns table (segment_id uuid, optval numeric, firstbase
int) as
$$
declare
mkset uuid;
rcount int;
begin
select id into mkset from seg.markerset where name =
markers_name and chrom = chr;
--
create temp table optmarkers on commit drop 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 --
'b474655c-80d2-47e7-bcb5-c65245195888'
group by m.id;
--
get diagnostics rcount = ROW_COUNT;
raise notice '% segments to optimize', rcount;
--
return query
select s.id as segment_id, o.optval, min(m.basepos) as
firstbase
from optmarkers o
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;
-- timing the CTE base
--with, the first
select count(s.id)
-- 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 =
'ed3b4817-1739-4727-9fac-35d1d63071ea' --mkset
and o.name = '1151704'; -- people_name
count
-------
30762
(1 row)
--explain analyze of Full CTE portion (that which constructs the
temp table in prep for final query)
--manually supplying the needed ids and such.
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=15412.11..15413.24 rows=65 width=48)
(actual time=5462.143..6042.901 rows=52196 loops=1)
Group Key: m.id
CTE segset
-> Nested Loop (cost=4820.07..6024.99 rows=310
width=76) (actual time=11.854..449.869 rows=30762 loops=1)
-> Seq Scan on probandset i (cost=0.00..21.27
rows=1 width=16) (actual time=0.007..0.286 rows=57 loops=1)
Filter: ((probands <@
'{1bfa3037-bf65-483b-9ac5-f7a57f9e48a6,69f339e3-8ef4-440d-82ee-76627caf8e8b,7a2150d9-cd9c-454a-92d4-4b5cda85258d,7b42b108-8c17-4261-a4d1-3ed1f5da892d,b56886d3-981e-4872-847f-ce019fe70a87,c7f46c7d-0223-4b89-8093-771b5d6964d8}'::uuid[])
AND (people_id = '10c8d51d-c413-4943-90f7-9e0d2dd8c8c4'::uuid))
Rows Removed by Filter: 494
-> Bitmap Heap Scan on segment s
(cost=4820.07..5995.20 rows=310 width=84) (actual
time=7.438..7.493 rows=540 loops=57)
Recheck Cond: ((probandset_id = i.id) AND
(markerset_id = 'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid))
Heap Blocks: exact=646
-> BitmapAnd (cost=4820.07..4820.07
rows=310 width=0) (actual time=7.434..7.434 rows=0 loops=57)
-> Bitmap Index Scan on useg
(cost=0.00..283.82 rows=5235 width=0) (actual time=0.357..0.357
rows=6470 loops=57)
Index Cond: (probandset_id = i.id)
-> Bitmap Index Scan on
segment_markerset_id_idx (cost=0.00..4493.24 rows=170774
width=0) (actual time=7.064..7.064 rows=150550 loops=57)
Index Cond: (markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid)
-> Sort (cost=9387.11..9387.28 rows=65 width=48) (actual
time=5462.122..5818.158 rows=1780618 loops=1)
Sort Key: m.id
Sort Method: external merge Disk: 64168kB
-> Nested Loop (cost=105.76..9385.16 rows=65
width=48) (actual time=11.884..4648.098 rows=1780618 loops=1)
-> Nested Loop (cost=105.34..9013.49
rows=706 width=64) (actual time=11.875..944.708 rows=1796463
loops=1)
-> CTE Scan on segset ss
(cost=0.00..6.97 rows=2 width=60) (actual time=11.857..461.254
rows=30762 loops=1)
Filter: (markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid)
-> Bitmap Heap Scan on marker m
(cost=105.34..4471.17 rows=3209 width=24) (actual
time=0.007..0.011 rows=58 loops=30762)
Recheck Cond: ((chrom = ss.chrom) AND
(basepos >= ss.startbase) AND (basepos <= ss.endbase))
Heap Blocks: exact=54458
-> Bitmap Index Scan on
marker_chrom_basepos_idx (cost=0.00..104.54 rows=3209 width=0)
(actual time=0.005..0.005 rows=58 loops=30762)
Index Cond: ((chrom = ss.chrom)
AND (basepos >= ss.startbase) AND (basepos <= ss.endbase))
-> Index Scan using
markerset_member_member_id_idx on markerset_member mm
(cost=0.42..0.52 rows=1 width=32) (actual time=0.002..0.002
rows=1 loops=1796463)
Index Cond: (member_id = m.id)
Filter: (markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid)
Rows Removed by Filter: 0
Planning time: 0.478 ms
Execution time: 6052.463 ms
(33 rows)
Time: 6053.983 ms
Slow Function
-- For an (potentially) arbitrary of grouping of
probandsets
create or replace function
seg.optimal_pvalue_set_group(pbgroup_id uuid, markers_name text,
people_name text, chr int)
returns table (segment_id uuid, optval numeric, firstbase int)
as
$$
declare
mkset uuid;
rcount int;
peop_id uuid;
begin
select id into mkset from seg.markerset where name =
markers_name and chrom = chr;
select id into peop_id from seg.people where name =
people_name;
raise notice 'marker set id is %; people id is % (%)', mkset,
peop_id, clock_timestamp();
--
create temp table optmarkers on commit drop 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 seg.probandset_group_member pgm on
s.probandset_id = pgm.member_id
where
s.markerset_id = mkset
and i.people_id = peop_id
and pgm.group_id = pbgroup_id
)
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;
get diagnostics rcount = ROW_COUNT;
raise notice 'found % segments to optimize by probandset group
id % (%)', rcount, pbgroup_id, clock_timestamp();
--
return query
select s.id as segment_id, o.optval, min(m.basepos) as
firstbase
from optmarkers o
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;
--timing the CTE base
--with, the third
select count(s.id)
-- 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 seg.probandset_group_member pgm on
s.probandset_id = pgm.member_id
where
s.markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea' -- mkset
and i.people_id =
'10c8d51d-c413-4943-90f7-9e0d2dd8c8c4' -- peop_id
and pgm.group_id =
'e7c01f89-8301-4221-9980-343b119711c5' -- pbgroup_id
;
count
-------
30392
(1 row)
Time: 146.692 ms
--explain analyze of Full CTE
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=39420.04..39425.18 rows=294 width=48)
(actual time=154302.816..154860.213 rows=52196 loops=1)
Group Key: m.id
CTE segset
-> Nested Loop (cost=282.86..23235.89 rows=1795
width=76) (actual time=0.568..82.964 rows=30392 loops=1)
-> Hash Join (cost=11.11..31.27 rows=6 width=32)
(actual time=0.035..0.147 rows=56 loops=1)
Hash Cond: (i.id = pgm.member_id)
-> Seq Scan on probandset i
(cost=0.00..19.89 rows=57 width=16) (actual time=0.005..0.092
rows=57 loops=1)
Filter: (people_id =
'10c8d51d-c413-4943-90f7-9e0d2dd8c8c4'::uuid)
Rows Removed by Filter: 494
-> Hash (cost=10.41..10.41 rows=56
width=16) (actual time=0.027..0.027 rows=56 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
11kB
-> Bitmap Heap Scan on
probandset_group_member pgm (cost=4.71..10.41 rows=56 width=16)
(actual time=0.013..0.021 rows=56 loops=1)
Recheck Cond: (group_id =
'e7c01f89-8301-4221-9980-343b119711c5'::uuid)
Heap Blocks: exact=1
-> Bitmap Index Scan on
psgm_pkey (cost=0.00..4.70 rows=56 width=0) (actual
time=0.008..0.008 rows=56 loops=1)
Index Cond: (group_id =
'e7c01f89-8301-4221-9980-343b119711c5'::uuid)
-> Bitmap Heap Scan on segment s
(cost=271.75..3859.10 rows=310 width=84) (actual
time=0.446..1.196 rows=543 loops=56)
Recheck Cond: (probandset_id = i.id)
Filter: (markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid)
Rows Removed by Filter: 5974
Heap Blocks: exact=8223
-> Bitmap Index Scan on useg
(cost=0.00..271.68 rows=5235 width=0) (actual time=0.434..0.434
rows=6517 loops=56)
Index Cond: (probandset_id = i.id)
-> Sort (cost=16184.15..16184.88 rows=294 width=48)
(actual time=154302.795..154633.935 rows=1767743 loops=1)
Sort Key: m.id
Sort Method: external merge Disk: 63712kB
-> Nested Loop (cost=40.92..16172.09 rows=294
width=48) (actual time=96.084..153723.675 rows=1767743 loops=1)
-> Hash Join (cost=40.50..14499.61 rows=3177
width=64) (actual time=96.074..151048.722 rows=1783466 loops=1)
Hash Cond: (m.chrom = ss.chrom)
Join Filter: ((m.basepos >=
ss.startbase) AND (m.basepos <= ss.endbase))
Rows Removed by Join Filter: 1598476902
-> Seq Scan on marker m
(cost=0.00..11647.63 rows=635363 width=24) (actual
time=0.007..39.685 rows=635412 loops=1)
-> Hash (cost=40.39..40.39 rows=9
width=60) (actual time=96.027..96.027 rows=30392 loops=1)
Buckets: 32768 (originally 1024)
Batches: 1 (originally 1) Memory Usage: 2432kB
-> CTE Scan on segset ss
(cost=0.00..40.39 rows=9 width=60) (actual time=0.571..92.319
rows=30392 loops=1)
Filter: (markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid)
-> Index Scan using
markerset_member_member_id_idx on markerset_member mm
(cost=0.42..0.52 rows=1 width=32) (actual time=0.001..0.001
rows=1 loops=1783466)
Index Cond: (member_id = m.id)
Filter: (markerset_id =
'ed3b4817-1739-4727-9fac-35d1d63071ea'::uuid)
Rows Removed by Filter: 0
Planning time: 0.867 ms
Execution time: 154868.420 ms
(42 rows)
|
