作者
digoal
日期
2021-01-13
标签
PostgreSQL , pageinspect , 内窥heap,index存储结构
背景
内窥索引结构, heap表结构, 可以使用pageinspect插件, 但是PG的索引结构太多了, 包括btree, hash, gin, gist, spgist, brin, bloom等. pageinspect无法支持所有索引, PG 14 又新增了一个索引接口gist的内窥.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=756ab29124d7850d4392e2227b67b69b61576cd6
Add functions to 'pageinspect' to inspect GiST indexes.
author Heikki Linnakangas heikki.linnakangas@iki.fi
Wed, 13 Jan 2021 08:33:33 +0000 (10:33 +0200)
committer Heikki Linnakangas heikki.linnakangas@iki.fi
Wed, 13 Jan 2021 08:33:33 +0000 (10:33 +0200)
commit 756ab29124d7850d4392e2227b67b69b61576cd6
tree 52812d01b7db7792b105374c452493a39b832be7 tree | snapshot
parent df10ac625c1672edf839ff59cfcac9dcc097515c commit | diff
Add functions to 'pageinspect' to inspect GiST indexes.
Author: Andrey Borodin and me
Discussion: https://www.postgresql.org/message-id/3E4F9093-A1B5-4DF8-A292-0B48692E3954%40yandex-team.ru
CREATE TABLE test_gist AS SELECT point(i,i) p, i::text t FROM
2 generate_series(1,1000) i;
3 CREATE INDEX test_gist_idx ON test_gist USING gist (p);
4 -- Page 0 is the root, the rest are leaf pages
5 SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 0));
6 lsn | nsn | rightlink | flags
7 -----+-----+------------+-------
8 0/1 | 0/0 | 4294967295 | {}
9 (1 row)
10
11 SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 1));
12 lsn | nsn | rightlink | flags
13 -----+-----+------------+--------
14 0/1 | 0/0 | 4294967295 | {leaf}
15 (1 row)
16
17 SELECT * FROM gist_page_opaque_info(get_raw_page('test_gist_idx', 2));
18 lsn | nsn | rightlink | flags
19 -----+-----+-----------+--------
20 0/1 | 0/0 | 1 | {leaf}
21 (1 row)
22
23 SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 0), 'test_gist_idx');
24 itemoffset | ctid | itemlen | keys
25 ------------+-----------+---------+-------------------
26 1 | (1,65535) | 40 | (p)=((166,166))
27 2 | (2,65535) | 40 | (p)=((332,332))
28 3 | (3,65535) | 40 | (p)=((498,498))
29 4 | (4,65535) | 40 | (p)=((664,664))
30 5 | (5,65535) | 40 | (p)=((830,830))
31 6 | (6,65535) | 40 | (p)=((996,996))
32 7 | (7,65535) | 40 | (p)=((1000,1000))
33 (7 rows)
34
35 SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 1), 'test_gist_idx') LIMIT 5;
36 itemoffset | ctid | itemlen | keys
37 ------------+-------+---------+-------------
38 1 | (0,1) | 40 | (p)=((1,1))
39 2 | (0,2) | 40 | (p)=((2,2))
40 3 | (0,3) | 40 | (p)=((3,3))
41 4 | (0,4) | 40 | (p)=((4,4))
42 5 | (0,5) | 40 | (p)=((5,5))
43 (5 rows)
44
45 SELECT * FROM gist_page_items(get_raw_page('test_gist_idx', 2), 'test_gist_idx') LIMIT 5;
46 itemoffset | ctid | itemlen | keys
47 ------------+--------+---------+-----------------
48 1 | (1,10) | 40 | (p)=((167,167))
49 2 | (1,11) | 40 | (p)=((168,168))
50 3 | (1,12) | 40 | (p)=((169,169))
51 4 | (1,13) | 40 | (p)=((170,170))
52 5 | (1,14) | 40 | (p)=((171,171))
53 (5 rows)
54
55 SELECT * FROM gist_page_items_bytea(get_raw_page('test_gist_idx', 0));
56 itemoffset | ctid | itemlen | key_data
57 ------------+-----------+---------+------------------------------------------------------------------------------------
58 1 | (1,65535) | 40 | \x00000100ffff28000000000000c064400000000000c06440000000000000f03f000000000000f03f
59 2 | (2,65535) | 40 | \x00000200ffff28000000000000c074400000000000c074400000000000e064400000000000e06440
60 3 | (3,65535) | 40 | \x00000300ffff28000000000000207f400000000000207f400000000000d074400000000000d07440
61 4 | (4,65535) | 40 | \x00000400ffff28000000000000c084400000000000c084400000000000307f400000000000307f40
62 5 | (5,65535) | 40 | \x00000500ffff28000000000000f089400000000000f089400000000000c884400000000000c88440
63 6 | (6,65535) | 40 | \x00000600ffff28000000000000208f400000000000208f400000000000f889400000000000f88940
64 7 | (7,65535) | 40 | \x00000700ffff28000000000000408f400000000000408f400000000000288f400000000000288f40
65 (7 rows)
66
67 SELECT * FROM gist_page_items_bytea(get_raw_page('test_gist_idx', 1)) LIMIT 5;
68 itemoffset | ctid | itemlen | key_data
69 ------------+-------+---------+------------------------------------------------------------------------------------
70 1 | (0,1) | 40 | \x0000000001002800000000000000f03f000000000000f03f000000000000f03f000000000000f03f
71 2 | (0,2) | 40 | \x00000000020028000000000000000040000000000000004000000000000000400000000000000040
72 3 | (0,3) | 40 | \x00000000030028000000000000000840000000000000084000000000000008400000000000000840
73 4 | (0,4) | 40 | \x00000000040028000000000000001040000000000000104000000000000010400000000000001040
74 5 | (0,5) | 40 | \x00000000050028000000000000001440000000000000144000000000000014400000000000001440
75 (5 rows)
76
77 SELECT * FROM gist_page_items_bytea(get_raw_page('test_gist_idx', 2)) LIMIT 5;
78 itemoffset | ctid | itemlen | key_data
79 ------------+--------+---------+------------------------------------------------------------------------------------
80 1 | (1,10) | 40 | \x000001000a0028000000000000e064400000000000e064400000000000e064400000000000e06440
81 2 | (1,11) | 40 | \x000001000b0028000000000000006540000000000000654000000000000065400000000000006540
82 3 | (1,12) | 40 | \x000001000c0028000000000000206540000000000020654000000000002065400000000000206540
83 4 | (1,13) | 40 | \x000001000d0028000000000000406540000000000040654000000000004065400000000000406540
84 5 | (1,14) | 40 | \x000001000e0028000000000000606540000000000060654000000000006065400000000000606540
85 (5 rows)
86
87 DROP TABLE test_gist;
在PG 14之前, 我们可以使用如下插件来支持gist, spgist索引的内窥.
http://www.sai.msu.su/~megera/wiki/Gevel
如果你想了解索引的原理可以参考:
《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》
《PostgreSQL gist, spgist索引的原理、差别、应用场景》
《[未完待续] PostgreSQL hash 索引结构介绍》
《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
