共計 3311 個字符，預計需要花費 9 分鐘才能閱讀完成。
這篇文章主要講解了“PostgreSQL 12 B-tree 的改進是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“PostgreSQL 12 B-tree 的改進是什么”吧！
創建數據表，創建索引
[local]:5110 xdb@testdb=# drop table rel;
DROP TABLE
Time: 130.868 ms
[local]:5110 xdb@testdb=# CREATE TABLE rel (
xdb@testdb(# aid bigint NOT NULL,
xdb@testdb(# bid bigint NOT NULL
xdb@testdb(# );
CREATE TABLE
Time: 16.041 ms
[local]:5110 xdb@testdb=#
[local]:5110 xdb@testdb=# ALTER TABLE rel
xdb@testdb-# ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);
ALTER TABLE
Time: 5.236 ms
[local]:5110 xdb@testdb=#
[local]:5110 xdb@testdb=# CREATE INDEX rel_bid_idx ON rel (bid);
CREATE INDEX
Time: 1.838 ms
[local]:5110 xdb@testdb=#
[local]:5110 xdb@testdb=# INSERT INTO rel (aid, bid)
xdb@testdb-# SELECT i, i / 10000
xdb@testdb-# FROM generate_series(1, 20000000) AS i;
INSERT 0 20000000
Time: 152699.275 ms (02:32.699)
[local]:5110 xdb@testdb=#
[local]:5110 xdb@testdb=#查看索引信息
[local]:5110 xdb@testdb=#
[local]:5110 xdb@testdb=# \d rel
Table public.rel
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+---------
aid | bigint | | not null |
bid | bigint | | not null |
Indexes:
rel_pkey PRIMARY KEY, btree (aid, bid)
rel_bid_idx btree (bid)
[local]:5110 xdb@testdb=# \di+ rel_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------+-------+-------+-------+--------+-------------
public | rel_pkey | index | xdb | rel | 602 MB |
(1 row)
[local]:5110 xdb@testdb=# \di+ rel_bid_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+-------+-------+--------+-------------
public | rel_bid_idx | index | xdb | rel | 545 MB |
(1 row)創建數據表，創建索引
[local:/run/pg12]:5120 pg12@testdb=# \timing on
Timing is on.
[local:/run/pg12]:5120 pg12@testdb=# drop table rel;
DROP TABLE
Time: 279.144 ms
[local:/run/pg12]:5120 pg12@testdb=# CREATE TABLE rel (
pg12@testdb(# aid bigint NOT NULL,
pg12@testdb(# bid bigint NOT NULL
pg12@testdb(# );
CREATE TABLE
Time: 1.579 ms
[local:/run/pg12]:5120 pg12@testdb=#
[local:/run/pg12]:5120 pg12@testdb=# ALTER TABLE rel
pg12@testdb-# ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);
ALTER TABLE
Time: 3.450 ms
[local:/run/pg12]:5120 pg12@testdb=#
[local:/run/pg12]:5120 pg12@testdb=# CREATE INDEX rel_bid_idx ON rel (bid);
CREATE INDEX
Time: 1.201 ms
[local:/run/pg12]:5120 pg12@testdb=#
[local:/run/pg12]:5120 pg12@testdb=# INSERT INTO rel (aid, bid)
pg12@testdb-# SELECT i, i / 10000
pg12@testdb-# FROM generate_series(1, 20000000) AS i;
INSERT 0 20000000
Time: 124503.212 ms (02:04.503)
[local:/run/pg12]:5120 pg12@testdb=#查看索引信息
[local:/run/pg12]:5120 pg12@testdb=# \di+ rel_pkey
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------+-------+-------+-------+--------+-------------
public | rel_pkey | index | pg12 | rel | 601 MB |
(1 row)
[local:/run/pg12]:5120 pg12@testdb=# \di+ rel_bid_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+-------+-------+--------+-------------
public | rel_bid_idx | index | pg12 | rel | 408 MB |
(1 row)
[local:/run/pg12]:5120 pg12@testdb=#可以看到 PK 沒有太大的變化，但有很多重復值的 bid 列索引則有明顯的變化，比 PG 11 少了 25% 的空間。
感謝各位的閱讀，以上就是“PostgreSQL 12 B-tree 的改進是什么”的內容了，經過本文的學習后，相信大家對 PostgreSQL 12 B-tree 的改進是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！
正文完
