Search Postgresql Archives

Re: query not scaling

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 10/26/2017 09:01 AM, Tom Lane wrote:
Laurenz Albe <laurenz.albe@xxxxxxxxxxx> writes:
Also, to have PostgreSQL inline the function, which would be good
for performance, it should be declared IMMUTABLE.
Actually, if you hope to have a SQL function be inlined, it's better
not to decorate it at all --- not with IMMUTABLE, and not with STRICT
either.  Both of those restrict the parser's ability to inline unless
it can prove the contained _expression_ is equally immutable/strict.
With the default attributes of volatile/not strict, there's nothing
to prove.

(In any case, it's usually easy enough to tell from EXPLAIN output
whether inlining has happened.)

			regards, tom lane

In another instance of the same schema, in same database as original
slow execution I've loaded 823591 segments (though in this case all
of them are on one chromosome, one markerset) and 65K proband sets
using same marker table as the slow(est) query.  In the fastest run,
there are only 46K segments for the given markerset.



                                                                                    QUERY PLAN                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=82122076.59..82122225.35 rows=14876 width=48) (actual time=208203.091..208210.348 rows=14645 loops=1)
   Output: m.id, min(((1.0 * (((s.events_greater + s.events_equal) + 0))::numeric) / ((((s.events_less + s.events_equal) + s.events_greater) + 0))::numeric))
   Group Key: m.id
   Buffers: shared hit=43209090
   ->  Nested Loop  (cost=3799.40..44686205.23 rows=1361304413 width=40) (actual time=55.443..89684.451 rows=75577302 loops=1)
         Output: m.id, s.events_greater, s.events_equal, s.events_less
         Buffers: shared hit=43209090
         ->  Hash Join  (cost=3798.98..43611.56 rows=823591 width=32) (actual time=55.393..1397.509 rows=823591 loops=1)
               Output: s.events_greater, s.events_equal, s.events_less, s.startbase, s.endbase
               Inner Unique: true
               Hash Cond: (s.probandset_id = p.id)
               Buffers: shared hit=19222
               ->  Seq Scan on sui.segment s  (cost=0.00..29414.86 rows=823591 width=48) (actual time=0.017..669.915 rows=823591 loops=1)
                     Output: s.id, s.chrom, s.markerset_id, s.probandset_id, s.startbase, s.endbase, s.firstmarker, s.lastmarker, s.events_less, s.events_equal, s.events_greater
                     Filter: ((s.chrom = 22) AND (s.markerset_id = 'edf95066-24d2-4ca1-bad6-aa850cc82fef'::uuid))
                     Buffers: shared hit=17061
               ->  Hash  (cost=2979.99..2979.99 rows=65519 width=16) (actual time=55.272..55.272 rows=65519 loops=1)
                     Output: p.id
                     Buckets: 65536  Batches: 1  Memory Usage: 3584kB
                     Buffers: shared hit=2161
                     ->  Seq Scan on sui.probandset p  (cost=0.00..2979.99 rows=65519 width=16) (actual time=0.007..33.582 rows=65519 loops=1)
                           Output: p.id
                           Filter: (p.people_id = '9b2308b1-9659-4a2c-91ae-8f95cd0a90b3'::uuid)
                           Buffers: shared hit=2161
         ->  Index Scan using marker_chrom_basepos_idx on base.marker m  (cost=0.42..37.67 rows=1653 width=20) (actual time=0.010..0.075 rows=92 loops=823591)
               Output: m.id, m.name, m.chrom, m.basepos, m.alleles
               Index Cond: ((m.chrom = 22) AND (m.basepos >= s.startbase) AND (m.basepos <= s.endbase))
               Buffers: shared hit=43189868
 Planning time: 0.764 ms
 Execution time: 208214.816 ms
(30 rows)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux