Re: Parallel Queries and PostGIS

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.

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