Re: Parallel Queries and PostGIS

On Tue, Mar 29, 2016 at 3:48 PM, Paul Ramsey <pramsey@cleverelephant.ca> wrote:
>> I have no idea why the cost adjustments that you need are different
>> for the scan case and the aggregate case.  That does seem problematic,
>> but I just don't know why it's happening.
>
> What might be a good way to debug it? Is there a piece of code I can
> look at to try and figure out the contribution of COST in either case?

Well, the cost calculations are mostly in costsize.c, but I dunno how
much that helps.  Maybe it would help if you posted some EXPLAIN
ANALYZE output for the different cases, with and without parallelism?

One thing I noticed about this output (from your blog)...

Finalize Aggregate(cost=16536.53..16536.79 rows=1 width=8)(actual time=2263.638..2263.639 rows=1 loops=1)  ->  Gather
(cost=16461.22..16461.53rows=3 width=32)  (actual time=754.309..757.204 rows=4 loops=1)        Number of Workers: 3
  ->  Partial Aggregate        (cost=15461.22..15461.23 rows=1 width=32)        (actual time=676.738..676.739 rows=1
loops=4)             ->  Parallel Seq Scan on pd              (cost=0.00..13856.38 rows=64 width=2311)
(actualtime=3.009..27.321 rows=42 loops=4)                    Filter: (fed_num = 47005)                    Rows Removed
byFilter: 17341Planning time: 0.219 msExecution time: 2264.684 ms
 

...is that the finalize aggregate phase is estimated to be very cheap,
but it's actually wicked expensive.  We get the results from the
workers in only 750 ms, but it takes another second and a half to
aggregate those 4 rows???

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company