Hi,
I am getting different output for stddev/variance functions with partition
tables.
CREATE TABLE part (c1 INT,c2 INT) PARTITION BY RANGE (c1);
CREATE TABLE part_p1 PARTITION OF part FOR VALUES FROM (1) TO (3);
CREATE TABLE part_p2 PARTITION OF part FOR VALUES FROM (3) TO (5);
INSERT INTO part VALUES (1,5),(2,15),(3,3),(4,17);
postgres=# SET parallel_setup_cost=0;
SET
postgres=# EXPLAIN SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
QUERY
PLAN
--------------------------------------------------------------------------------------------
Finalize Aggregate (cost=70.36..70.37 rows=1 width=72)
-> Gather (cost=70.12..70.33 rows=2 width=72)
Workers Planned: 2
-> Partial Aggregate (cost=70.12..70.13 rows=1 width=72)
-> Parallel Append (cost=0.00..56.00 rows=1882 width=8)
-> Parallel Seq Scan on part_p1 (cost=0.00..23.29
rows=1329 width=8)
-> Parallel Seq Scan on part_p2 (cost=0.00..23.29
rows=1329 width=8)
(7 rows)
postgres=# SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
* count | stddev | variance -------+--------+---------- 4 | 0
| 0(1 row)*postgres=#
postgres=# RESET parallel_setup_cost;
RESET
postgres=# EXPLAIN SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=121.71..121.72 rows=1 width=72)
-> Append (cost=0.00..87.80 rows=4520 width=8)
-> Seq Scan on part_p1 (cost=0.00..32.60 rows=2260 width=8)
-> Seq Scan on part_p2 (cost=0.00..32.60 rows=2260 width=8)
(4 rows)
postgres=# SELECT COUNT(c1),STDDEV(c2),VARIANCE(c2) FROM part;
* count | stddev | variance
-------+--------------------+--------------------- 4 |
7.0237691685684926 | 49.3333333333333333(1 row)*
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
