Re: Parallel Queries and PostGIS

On 1 April 2016 at 17:12, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 30 March 2016 at 09:14, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Tue, Mar 29, 2016 at 3:48 PM, Paul Ramsey <pramsey(at)cleverelephant(dot)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.53 rows=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)
>> (actual time=3.009..27.321 rows=42 loops=4)
>> Filter: (fed_num = 47005)
>> Rows Removed by Filter: 17341
>> Planning time: 0.219 ms
>> Execution 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???
>
> hmm, actually I've just realised that create_grouping_paths() should
> be accounting agg_costs differently depending if it's partial
> aggregation, finalize aggregation, or just normal. count_agg_clauses()
> needs to be passed the aggregate type information to allow the walker
> function to cost the correct portions of the aggregate correctly based
> on what type of aggregation the costs will be used for. In short,
> please don't bother to spend too much time tuning your costs until I
> fix this.
>
> As of now the Partial Aggregate is including the cost of the final
> function... that's certainly broken, as it does not call that
> function.
>
> I will try to get something together over the weekend to fix this, but
> I have other work to do until then.

Hi Paul,

As of deb71fa, committed by Robert today, you should have a bit more
control over parallel aggregate costings. You can how raise the
transfn cost, or drop the combinefn cost to encourage parallel
aggregation. Keep in mind the derialfn and deserialfn costs are now
also accounted for too.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services