PostgreSQL DBA(61) - zedstore：想说爱你不容易

怼烎@ 2021-11-22 23:36 246阅读 0赞

## testdb=\# create table t\_ap\_heap (id1 int,id2 int,id3 int,id4 int,id5 int ,id6 int,id7 int,id8 int,id9 int);   CREATE TABLE   testdb=\#   testdb=\# insert into t\_ap\_heap   testdb-\# select x,x,x,x,x,x,x,x,x   testdb-\# from generate\_series(1,10000000) as x;   INSERT 0 10000000   testdb=\#   testdb=\# drop table if exists t\_ap\_zedstore;   DROP TABLE   testdb=\# create table t\_ap\_zedstore   testdb-\# (id1 int,id2 int,id3 int,id4 int,id5 int ,id6 int,id7 int,id8 int,id9 int) using zedstore;   CREATE TABLE   testdb=\#   testdb=\# insert into t\_ap\_zedstore   testdb-\# select x,x,x,x,x,x,x,x,x   testdb-\# from generate\_series(1,10000000) as x;   INSERT 0 10000000   testdb=\#   testdb=\# select pg\_size\_pretty(pg\_table\_size(‘t\_ap\_heap’));   pg\_size\_pretty ##

## 651 MB   (1 row)   testdb=\# select pg\_size\_pretty(pg\_table\_size(‘t\_ap\_zedstore’));   pg\_size\_pretty ##

## 501 MB   (1 row)   testdb=\# explain analyze select avg(id1) from t\_ap\_heap;   QUERY PLAN ##

--------------------

Finalize Aggregate (cost=136417.97…136417.98 rows=1 width=32) (actual time=2432.238…2432.240 rows=1 loops=1)  
\-> Gather (cost=136417.75…136417.96 rows=2 width=32) (actual time=2432.015…2433.781 rows=3 loops=1)  
Workers Planned: 2  
Workers Launched: 2  
\-> Partial Aggregate (cost=135417.75…135417.76 rows=1 width=32) (actual time=2363.185…2363.185 rows=1 loops=3)  
\-> Parallel Seq Scan on t\_ap\_heap (cost=0.00…125001.00 rows=4166700 width=4) (actual time=0.348…1843.592 r  
ows=3333333 loops=3)  
Planning Time: 28.360 ms  
Execution Time: 2434.173 ms  
(8 rows)  
testdb=\# explain analyze select avg(id1) from t\_ap\_zedstore;  
psql: WARNING: terminating connection because of crash of another server process  
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.  
HINT: In a moment you should be able to reconnect to the database and repeat your command.  
psql: server closed the connection unexpectedly  
This probably means the server terminated abnormally  
before or while processing the request.  
The connection to the server was lost. Attempting reset: Failed.  
!>

重新连接,执行查询:

## testdb=\# explain analyze select avg(id1) from t\_ap\_heap;   QUERY PLAN ##

--------------------

## Finalize Aggregate (cost=136417.97…136417.98 rows=1 width=32) (actual time=1357.266…1357.267 rows=1 loops=1)   \-> Gather (cost=136417.75…136417.96 rows=2 width=32) (actual time=1357.068…1362.153 rows=3 loops=1)   Workers Planned: 2   Workers Launched: 2   \-> Partial Aggregate (cost=135417.75…135417.76 rows=1 width=32) (actual time=1346.515…1346.515 rows=1 loops=3)   \-> Parallel Seq Scan on t\_ap\_heap (cost=0.00…125001.00 rows=4166700 width=4) (actual time=0.488…830.427 ro   ws=3333333 loops=3)   Planning Time: 0.550 ms   Execution Time: 1362.347 ms   (8 rows)   testdb=\# explain analyze select avg(id1) from t\_ap\_zedstore;   QUERY PLAN ##

--------------------

Finalize Aggregate (cost=107843.55…107843.56 rows=1 width=32) (actual time=9.579…9.580 rows=1 loops=1)  
\-> Gather (cost=107843.33…107843.54 rows=2 width=32) (actual time=0.467…11.620 rows=3 loops=1)  
Workers Planned: 2  
Workers Launched: 2  
\-> Partial Aggregate (cost=106843.33…106843.34 rows=1 width=32) (actual time=0.020…0.021 rows=1 loops=3)  
\-> Parallel Seq Scan on t\_ap\_zedstore (cost=0.00…98295.87 rows=3418987 width=4) (actual time=0.007…0.008 r  
ows=0 loops=3)  
Planning Time: 0.268 ms  
Execution Time: 11.835 ms  
(8 rows)  
testdb=\#

heap vs zedstore : 1362ms vs 12ms,性能确实有大幅提升.

不过,等等

## testdb=\# select avg(id1) from t\_ap\_heap;   avg ##

## 5000000.500000000000   (1 row)   testdb=\# select avg(id1) from t\_ap\_zedstore;   avg ##

## (1 row)   testdb=\# select count(\*) from t\_ap\_zedstore;   count ##

(1 row)  
testdb=\# insert into t\_ap\_zedstore  
testdb-\# select x,x,x,x,x,x,x,x,x  
testdb-\# from generate\_series(1,10000000) as x;  
psql: ERROR: too many attributes for zedstore  
testdb=\#