数据库postgres index vaccum学习

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（）会产生一个0.**的数字，10就是十以内的数字
INSERT 0 1000 产生一千个10以内的随机数，并且把他们作为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)

现在testindex里面有1000条数据，我再往里面加1000条。

VoicePortal=# insert into testindex VALUES ('1001',8);
INSERT 0 1
VoicePortal=# select count(*) from testindex;
count

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 |

再删除一些数据

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

这些数据会暂时放在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 |

也就是说只要最后一个数据存在，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可以做到，他会改变原来的排序，重新排列。