亚洲女同成aV人片在线观看|亚洲www啪成人一区二区麻豆|亚洲国产中日韩精品综合|亚洲国产成人精品一级片|亚洲无码在线视频免费

膨脹率的表膨精確計算

PostgreSQL自帶了pgstattu(′▽?zhuān)?)ple模塊，可用于精確計算表的脹監膨脹ヾ(′▽?zhuān)??率。譬如這里的控案tuple_percent字段就是元組實(shí)際字節占關(guān)系總大小的百分比，用1減去該值即為膨脹率。例精

#插入1000W數據
postgres=# inser??t into t select id,確計id from generate_series(1,10000000) as id;
INSERT 0 100ヾ(′ω｀)?00000

#表ヾ(?■_■)ノ膨脹系數為0.097
postgres=# select *, 1.??0 - tuple_len::numeric / ta(╬?益?)ble_len as bloat from pgstattuple('t');
ta??ble_len | tuple_count | tuple_len | tuple_perc(??ヮ?)?*:???ent | dead_??(′?｀*)tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_perc??ent | bloat
--------?---+-------------+-----------+------------(′?｀)---+------------------+----------------??+??------(′?ω?`)--------------+------------+--------------(′；ω；`)+------------------------
442818560 | 10000001 | 400000040 | 90.33 | 0 | 0 | 0 | 1304976 | 0.29 | 0.09669540499838127833
(1 row)

#占用54055個(gè)??page
postヽ(′ー｀)ノgres=# select * from pg_relpages('t');
pg_relpages
-------------
54055
(1 row)

#刪除數據
postgres=# delete from t where id<>10000000;
DELETE 9999999

#仍然占用54055( ?° ?? ?°)個(gè)page
postgres=# select * from pg_relpages('t');
pg_relpages
-------------
54055
(1 row)

#膨脹率已經(jīng)為0.999999
postgres=# se??lect *, 1.0 - tuple_len::numeric / table_len as bloat from pgstattuple('t');
table_len | tuple?_count | tuple_len | tup(/ω＼)le_percent | dea( ?ω?)d_tuple_count | dea??d_tuple_len | dead_tuple_percent | free_space | free_pe(′_｀)rcent | bloat
-----------+-------------+-----------+-------??--------+------------------+----------------+--------------------+------------+--------------+----------------------------
442818560 | 2 | 80 | 0 | 9999999 | 399999960 | 90.33 | 1304976 | 0.29 | 0.999999819339099065766349

#vacuum表
postgres=# vacuum (verbose,full,analyze) t;
INFO: vacuuming "public.t"
INFO: "t": found 5372225 removable, 2 nonremovable row versions in 54055 pages
DETAIL: 0 dead row versions cannot be removed yet.??
CPU: user: 0.89 s, sy(′?_?`)stem: 0.00 s, elap(◎_◎;)sed: 0.89 s.
INFO: analyzing "p??ublic.t"
INFO: "t": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample,(?⊿?) 2 estimated total rows
VACUUM??

補充：pg索引膨脹問(wèn)題---重建索引

問(wèn)題：

發(fā)現數據庫中很多表的索引大小超過(guò)數據大小。經(jīng)檢查，表膨生產(chǎn)CA、脹監CZ、控案MU、例精HU、確計PSG、表膨RIUE庫都存在這個(gè)現象。脹監

原因：據運行同事介紹索引膨脹問(wèn)題無(wú)法避免，控案頻繁更新就會(huì )帶來(lái)這個(gè)問(wèn)題。例(′▽?zhuān)?精

解決方法：

對于大的確計索引可以采用重建的方式解決(jue)。以下兩種方法推薦第一種。

方法一：停止應用（這(zhe)個(gè)操作(zuo)會(huì )鎖表），重建索引（注：重(′▽?zhuān)?)建完索引名稱(chēng)不變）

sql：reindex index 索引名稱(chēng)

還可以針對表重建索引，這個(gè)操作會(huì )加排他鎖 ：

reindex?? table?? 表名

普通索引

create index concurrently idx_tbl_2 on tbl(id);
drop index idx_tbl_1;

唯一索引

create unique index concurrently user_info_username_key_1 on user_info(username);
begin;
alter table user_in(′?｀*)fo drop constraint user_info_username_key;
alter table user_info add constrain(//ω//)t user?_info_usern??ame_key unique using index user_info_username_key_1;
end;

主鍵索引

create unique index concurrently user_info_pkey_1 on user_info(id);
begin;
alter table user_info drop const??raint user_info_pkey;
a??lter table user_info add constraint user_info_pkey primary key using index user_info_p??key_1;
end;

文(wen)章來(lái)源：腳本之家

來(lái)源地址：https://www.jb51.net/arヽ(′ー｀)ノticle(′?ω?`)/204334.htm