您好,登錄后才能下訂單哦!
VoicePortal=# create table testindex (no serial primary key, value integer);
NOTICE: CREATE TABLE will create implicit sequence "testindex_no_seq" for serial column "testindex.no"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "testindex_pkey" for table "testindex"
VoicePortal=# insert into testindex(value)
VoicePortal-# select trunc(random() *10) from generate_series(1,1000); random()會產(chǎn)生一個0.**的數(shù)字,10就是十以內(nèi)的數(shù)字
INSERT 0 1000 產(chǎn)生一千個10以內(nèi)的隨機數(shù),并且把他們作為value值插入到testindex里面去
VoicePortal=# select * from testindex limit 10;
no | value
----+-------
1 | 0
2 | 8
3 | 3
4 | 2
5 | 8
6 | 9
7 | 6
8 | 5
9 | 9
10 | 9
(10 rows)
這樣testindex的表建好了,用它來做測試
VoicePortal=# \d testindex
Table "public.testindex"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
no | integer | not null default nextval('testindex_no_seq'::regclass)
value | integer |
Indexes:
"testindex_pkey" PRIMARY KEY, btree (no) 自動建立索引
流出一個table的詳細信息。
VoicePortal=# \di+ testindex_pkey 查表的索引大小
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------+-------+----------+-----------+-------+-------------
public | testindex_pkey | index | postgres | testindex | 40 kB |
(1 row)
VoicePortal=# \dt+ testindex 查表大小
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-----------+-------+----------+-------+-------------
public | testindex | table | postgres | 64 kB |
(1 row)
1001
(1 row)
VoicePortal=# insert into testindex(value)
select trunc(random() *10) from generate_series(1002,1100);
INSERT 0 99
VoicePortal=# insert into testindex(value)
VoicePortal-# select trunc(random()*10)from generate_series(1101,2000);
INSERT 0 900
VoicePortal=# \di+ testindex_pkey;
List of relations
-[ RECORD 1 ]---------------
Schema | public
Name | testindex_pkey
Type | index
Owner | postgres
Table | testindex
Size | 64 kB
Description |
VoicePortal=# \dt+ testindex;
List of relations
-[ RECORD 1 ]----------
Schema | public
Name | testindex
Type | table
Owner | postgres
Size | 96 kB
Description |
再刪除一些數(shù)據(jù)
VoicePortal=# delete FROM testindex where value <8;
DELETE 605
VoicePortal=# select * from pg_stat_user_tables where relname='testindex';
-[ RECORD 1 ]-----+------------------------------
relid | 1445721
schemaname | public
relname | testindex
seq_scan | 8
seq_tup_read | 5831
idx_scan | 1
idx_tup_fetch | 1001
n_tup_ins | 2001
n_tup_upd | 0
n_tup_del | 1597
n_tup_hot_upd | 0
n_live_tup | 403
n_dead_tup | 605
last_vacuum |
last_autovacuum | 2016-12-08 18:09:00.7149-07
last_analyze | 2016-12-08 18:03:16.074174-07
last_autoanalyze | 2016-12-08 18:09:00.715874-07
vacuum_count | 0
autovacuum_count | 1
analyze_count | 2
autoanalyze_count | 3
這些數(shù)據(jù)會暫時放在dead里面等待autovaccum
VoicePortal=# select * from pg_stat_user_tables where relname='testindex';
-[ RECORD 1 ]-----+------------------------------
relid | 1445721
schemaname | public
relname | testindex
seq_scan | 8
seq_tup_read | 5831
idx_scan | 1
idx_tup_fetch | 1001
n_tup_ins | 2001
n_tup_upd | 0
n_tup_del | 1597
n_tup_hot_upd | 0
n_live_tup | 403
n_dead_tup | 0
last_vacuum |
last_autovacuum | 2016-12-08 18:11:00.715995-07
last_analyze | 2016-12-08 18:03:16.074174-07
last_autoanalyze | 2016-12-08 18:11:00.716891-07
vacuum_count | 0
autovacuum_count | 2
analyze_count | 2
autoanalyze_count | 4
之后清空dead。
VoicePortal=# \di+ testindex_pkey;
List of relations
-[ RECORD 1 ]---------------
Schema | public
Name | testindex_pkey
Type | index
Owner | postgres
Table | testindex
Size | 64 kB
Description |
VoicePortal=# \dt+ testindex;
List of relations
-[ RECORD 1 ]----------
Schema | public
Name | testindex
Type | table
Owner | postgres
Size | 104 kB
Description |
也就是說只要最后一個數(shù)據(jù)存在,vacuum是無法真正釋放空間的
VoicePortal=# vacuum FULL testindex;
VACUUM
VoicePortal=# \di+ testindex_pkey;
List of relations
-[ RECORD 1 ]---------------
Schema | public
Name | testindex_pkey
Type | index
Owner | postgres
Table | testindex
Size | 32 kB
Description |
VoicePortal=# \dt+ testindex;
List of relations
-[ RECORD 1 ]----------
Schema | public
Name | testindex
Type | table
Owner | postgres
Size | 16 kB
Description |
FULL vacuum可以做到,他會改變原來的排序,重新排列。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。