Re: using custom scan nodes to prototype parallel sequential scan

On Fri, Nov 14, 2014 at 2:12 PM, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:

> > On 12 November 2014 07:54, David Rowley <dgrowleyml(at)gmail(dot)com>
> > Take int4_avg_accum() for example it does:
> >
> >
> > transdata->count++;
> > transdata->sum += newval;
> >
> > The merge function would need to perform something like:
> >
> > transdata->count += transdata2merge.count; sum += transdata2merge.sum;
> >
> > Then the final function could be called on the merged aggregate state.
> >
> More simplify, we can describe parallel aware aggregate function.
> Please assume AVG(X) function that takes nrows and sum of X. Its transition
> function performs as like you described above, then final function works as
> usual.
>
> The job of parallel seq scan needs to do is:
> 1. replace AVG(X) by AVG(nrows, sum(X)
> 2. generate count(*) on the partial relation being grouped.
> 3. generate sum(X) on the partial relation being grouped.
>
> It looks like the following query:
> SELECT AVG(nrows, sum_X) FROM (
> SELECT count(*) nrows, sum(X) sum_X FROM tbl WHERE blkno between 0 and
> 999 GROUP BY cat
> UNION
> SELECT count(*) nrows, sum(X) sum_X FROM tbl WHERE blkno between 1000
> and 1999 GROUP BY cat
> UNION
> SELECT count(*) nrows, sum(X) sum_X FROM tbl WHERE blkno between 2000
> and 2999 GROUP BY cat
> UNION
> SELECT count(*) nrows, sum(X) sum_X FROM tbl WHERE blkno between 3000
> and 3999 GROUP BY cat
> );
>
>
Well, this would require giving the planner some kind of knowledge of what
AVG() is. It currently knows nothing about that. It currently calls the
transition function for each row, and the final function at the end and
does not care or need to care about what either of those functions actually
does. The transformation above looks like it would need to care and the
logic to add that would be way more complex than aggregate merge functions.

Likely for most aggregates, like count, sum, max, min, bit_and and bit_or
the merge function would be the same as the transition function, as the
state type is just the same as the input type. It would only be aggregates
like avg(), stddev*(), bool_and() and bool_or() that would need a new merge
function made... These would be no more complex than the transition
functions... Which are just a few lines of code anyway.

We'd simply just not run parallel query if any aggregates used in the query
didn't have a merge function.

When I mentioned this, I didn't mean to appear to be placing a road block.I
was just bringing to the table the information that COUNT(*) + COUNT(*)
works ok for merging COUNT(*)'s "sub totals", but AVG(n) + AVG(n) does not.

Merge functions should be a simple patch to write. If I thought there was
going to be a use for them in this release, I'd put my hand up to put a
patch together.

Regards

David Rowley