Re: Statistical aggregate functions are not working with partitionwise aggregate

On Fri, May 3, 2019 at 2:56 PM Rajkumar Raghuwanshi <
rajkumar.raghuwanshi@enterprisedb.com> wrote:

> Hi,
>
> On PG-head, Some of statistical aggregate function are not giving correct
> output when enable partitionwise aggregate while same is working on v11.
>

I had a quick look over this and observed that something broken with the
PARTIAL aggregation.

I can reproduce same issue with the larger dataset which results into
parallel scan.

CREATE TABLE tbl1(a int2,b float4) partition by range(a);
create table tbl1_p1 partition of tbl1 for values from (minvalue) to (0);
create table tbl1_p2 partition of tbl1 for values from (0) to (maxvalue);
insert into tbl1 select i%2, i from generate_series(1, 1000000) i;

# SELECT regr_count(b, a) FROM tbl1;
 regr_count
------------
          0
(1 row)

postgres:5432 [120536]=# explain SELECT regr_count(b, a) FROM tbl1;
                                           QUERY
PLAN
------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=15418.08..15418.09 rows=1 width=8)
   ->  Gather  (cost=15417.87..15418.08 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=14417.87..14417.88 rows=1 width=8)
               ->  Parallel Append  (cost=0.00..11091.62 rows=443500
width=6)
                     ->  Parallel Seq Scan on tbl1_p2  (cost=0.00..8850.00
rows=442500 width=6)
                     ->  Parallel Seq Scan on tbl1_p1  (cost=0.00..24.12
rows=1412 width=6)
(7 rows)

postgres:5432 [120536]=# set max_parallel_workers_per_gather to 0;
SET
postgres:5432 [120536]=# SELECT regr_count(b, a) FROM tbl1;
 regr_count
------------
    1000000
(1 row)

After looking further, it seems that it got broken by following commit:

commit a9c35cf85ca1ff72f16f0f10d7ddee6e582b62b8
Author: Andres Freund <andres@anarazel.de>
Date:   Sat Jan 26 14:17:52 2019 -0800

    Change function call information to be variable length.


This commit is too big to understand and thus could not get into the excact
cause.

Thanks


> below are some of examples.
>
> CREATE TABLE tbl(a int2,b float4) partition by range(a);
> create table tbl_p1 partition of tbl for values from (minvalue) to (0);
> create table tbl_p2 partition of tbl for values from (0) to (maxvalue);
> insert into tbl values (-1,-1),(0,0),(1,1),(2,2);
>
> --when partitionwise aggregate is off
> postgres=# SELECT regr_count(b, a) FROM tbl;
>  regr_count
> ------------
>           4
> (1 row)
> postgres=# SELECT regr_avgx(b, a), regr_avgy(b, a) FROM tbl;
>  regr_avgx | regr_avgy
> -----------+-----------
>        0.5 |       0.5
> (1 row)
> postgres=# SELECT corr(b, a) FROM tbl;
>  corr
> ------
>     1
> (1 row)
>
> --when partitionwise aggregate is on
> postgres=# SET enable_partitionwise_aggregate = true;
> SET
> postgres=# SELECT regr_count(b, a) FROM tbl;
>  regr_count
> ------------
>           0
> (1 row)
> postgres=# SELECT regr_avgx(b, a), regr_avgy(b, a) FROM tbl;
>  regr_avgx | regr_avgy
> -----------+-----------
>            |
> (1 row)
> postgres=# SELECT corr(b, a) FROM tbl;
>  corr
> ------
>
> (1 row)
>
> Thanks & Regards,
> Rajkumar Raghuwanshi
> QMG, EnterpriseDB Corporation
>


-- 
Jeevan Chalke
Technical Architect, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company