免费学设计的网站,静态网页是什么意思,商业网站缩写,怎样在网上推广自己的产品PostgreSQL 字段使用pglz压缩测试
测试一#xff1a;
创建测试表 yewu1.test1#xff0c;并插入1000w行数据
创建测试表 yewu1.test2#xff0c;使用 pglz压缩字段#xff0c;并插入1000w行数据–创建测试表1#xff0c;并插入1000w行数据
white# create table yewu1.t…PostgreSQL 字段使用pglz压缩测试
测试一
创建测试表 yewu1.test1并插入1000w行数据
创建测试表 yewu1.test2使用 pglz压缩字段并插入1000w行数据–创建测试表1并插入1000w行数据
white# create table yewu1.test1 (name varchar(20));
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test1::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | name |
(7 rows)
white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test1 VALUES (white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test1)) AS table_size;table_size
------------422 MB
(1 row)–创建测试表2使用 pglz压缩字段并插入1000w行数据
white#
white# create table yewu1.test2 (name varchar(20) COMPRESSION pglz);
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test2::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | name | p
(7 rows)
white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test2 VALUES (white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test2)) AS table_size;table_size
------------422 MB
(1 row)对比表yewu1.test1和yewu1.test2的大小没体现出压缩了。
测试二
创建测试表 yewu1.test3text数据类型并插入1000w行数据
创建测试表 yewu1.test4text数据类型使用 pglz压缩字段并插入1000w行数据–创建测试表3并插入1000w行数据
white# create table yewu1.test3 (name text);
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test3::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | name |
(7 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test3 VALUES (white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test3)) AS table_size;table_size
------------422 MB
(1 row)–创建测试表4使用 pglz压缩字段并插入1000w行数据
white# create table yewu1.test4 (name text COMPRESSION pglz);
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test4::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | name | p
(7 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test4 VALUES (white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test4)) AS table_size;table_size
------------422 MB
(1 row)对比表yewu1.test3和yewu1.test4的大小没体现出压缩了。
测试三
创建测试表 yewu1.test5text数据类型并插入1000w行重复的数据
创建测试表 yewu1.test6text数据类型使用 pglz压缩字段并插入1000w行重复的数据–创建测试表5并插入1000w行重复的数据
white# create table yewu1.test5 (name text);
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test5::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | name |
(7 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test5 VALUES (white12345678);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test5)) AS table_size;table_size
------------422 MB
(1 row)–创建测试表6使用 pglz压缩字段并插入1000w行重复的数据
white# create table yewu1.test6 (name text COMPRESSION pglz);
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test6::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | name | p
(7 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test6 VALUES (white12345678);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test6)) AS table_size;table_size
------------422 MB
(1 row)对比表yewu1.test5和yewu1.test6的大小没体现出压缩了。
测试四
创建测试表 yewu1.test7带有主键text数据类型并插入1000w行重复的数据
创建测试表 yewu1.test8带有主键text数据类型使用 pglz压缩字段并插入1000w行重复的数据–创建测试表7带有主键并插入1000w行重复的数据
white# create table yewu1.test7 (
white(# id serial primary key,
white(# name text
white(# );
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test7::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | id | name |
(8 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test7 VALUES (aa,white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test7)) AS table_size;table_size
------------490 MB
(1 row)–创建测试表8带有主键使用 pglz压缩字段并插入1000w行重复的数据
white# create table yewu1.test8 (
white(# id serial primary key,
white(# name text COMPRESSION pglz
white(# );
CREATE TABLE
white#
white# SELECT attname, attcompression
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test8::regclass;attname | attcompression
--------------------------tableoid | cmax | xmax | cmin | xmin | ctid | id | name | p
(8 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test8 VALUES (aa,white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test8)) AS table_size;table_size
------------490 MB
(1 row)对比表yewu1.test7和yewu1.test8的大小没体现出压缩了。
测试五
清空测试表 yewu1.test8并修改字段存储类型为MAIN再插入1000w行重复的数据–清空测试表8并修改字段存储类型为MAIN再插入1000w行重复的数据
white# truncate table yewu1.test8;
TRUNCATE TABLE
white# SELECT pg_size_pretty(pg_table_size(yewu1.test8)) AS table_size;table_size
------------8192 bytes
(1 row)white#
white# SELECT attname, attcompression,attstorage
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test8::regclass;attname | attcompression | attstorage
--------------------------------------tableoid | | pcmax | | pxmax | | pcmin | | pxmin | | pctid | | pid | | pname | p | x
(8 rows)white#
white# ALTER TABLE yewu1.test8 ALTER COLUMN name SET STORAGE MAIN;
ALTER TABLE
white# SELECT attname, attcompression,attstorage
white-# FROM pg_attribute
white-# WHERE attrelid yewu1.test8::regclass;attname | attcompression | attstorage
--------------------------------------tableoid | | pcmax | | pxmax | | pcmin | | pxmin | | pctid | | pid | | pname | p | m
(8 rows)white#
white# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..10000000 LOOP
white$# INSERT INTO yewu1.test8 VALUES (aa,white || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white#
white# SELECT pg_size_pretty(pg_table_size(yewu1.test8)) AS table_size;table_size
------------490 MB
(1 row)–未完待续
