From: | Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | partition table and stddev() /variance() behaviour |
Date: | 2018-06-21 12:18:20 |
Message-ID: | CAKcux6nU4E2x8nkSBpLOT2DPvQ5LviJ3SGyAN6Sz7qDH4G4+Pw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2018-06-21 12:56:10 | Re: Postgres 11 release notes |
Previous Message | Ashutosh Sharma | 2018-06-21 12:10:23 | Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query |