PostgreSQL 12 B-tree的改进是什么

这篇文章主要讲解了"PostgreSQL 12 B-tree的改进是什么"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"PostgreSQL 12 B-tree的改进是什么"吧！

创建数据表，创建索引

[local]:5110 xdb@testdb=# drop table rel;DROP TABLETime: 130.868 ms[local]:5110 xdb@testdb=# CREATE TABLE rel (xdb@testdb(#    aid bigint NOT NULL,xdb@testdb(#    bid bigint NOT NULLxdb@testdb(# );CREATE TABLETime: 16.041 ms[local]:5110 xdb@testdb=#  [local]:5110 xdb@testdb=# ALTER TABLE relxdb@testdb-#    ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);ALTER TABLETime: 5.236 ms[local]:5110 xdb@testdb=#  [local]:5110 xdb@testdb=# CREATE INDEX rel_bid_idx ON rel (bid);CREATE INDEXTime: 1.838 ms[local]:5110 xdb@testdb=#  [local]:5110 xdb@testdb=# INSERT INTO rel (aid, bid)xdb@testdb-#    SELECT i, i / 10000xdb@testdb-#    FROM generate_series(1, 20000000) AS i; INSERT 0 20000000Time: 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 onTiming is on.[local:/run/pg12]:5120 pg12@testdb=# drop table rel;DROP TABLETime: 279.144 ms[local:/run/pg12]:5120 pg12@testdb=# CREATE TABLE rel (pg12@testdb(#    aid bigint NOT NULL,pg12@testdb(#    bid bigint NOT NULLpg12@testdb(# );CREATE TABLETime: 1.579 ms[local:/run/pg12]:5120 pg12@testdb=#  [local:/run/pg12]:5120 pg12@testdb=# ALTER TABLE relpg12@testdb-#    ADD CONSTRAINT rel_pkey PRIMARY KEY (aid, bid);ALTER TABLETime: 3.450 ms[local:/run/pg12]:5120 pg12@testdb=#  [local:/run/pg12]:5120 pg12@testdb=# CREATE INDEX rel_bid_idx ON rel (bid);CREATE INDEXTime: 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 / 10000pg12@testdb-#    FROM generate_series(1, 20000000) AS i; INSERT 0 20000000Time: 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的改进是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！