在pg11之后,引入了indnkeyatts字段,根据官方文档解释其作用:The number of key columns in the index, not counting any included columns, which are merely stored and do not participate in the index semantics。
第一感觉,和indnatts字段差不多,但两者的说明存在差异。后者是参与索引的列的计数,前者是关键列的计数。
于是做了一个小测试,如下:
新建test表
CREATE TABLE test (
a _int8 NULL,
b _int8 NULL,
c _int8 NULL
);
CREATE INDEX test_a_idx ON test (a);
--单列索引
CREATE INDEX test_a_idx1 ON test (a,b);
--组合索引
CREATE INDEX test_a_idx2 ON test (a,b) include (c);
--使用inclue创建非键列
---查询各个索引的计数
SELECT
indnkeyatts,indnatts,i_c.relname
FROM
pg_index i
JOIN
pg_class c ON c.oid = i.indrelid
JOIN
pg_class i_c ON i_c.oid = i.indexrelid
JOIN
pg_am am ON am.oid = i_c.relam
LEFT JOIN
pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
JOIN
pg_namespace n ON n.oid = c.relnamespace
WHERE
c.relname = 'test'
结果如下
indnkeyatts | indnatts | relname |
---|---|---|
2 | 3 | test_a_idx2 |
2 | 3 | test_a_idx2 |
2 | 3 | test_a_idx2 |
1 | 1 | test_a_idx |
2 | 2 | test_a_idx1 |
2 | 2 | test_a_idx1 |
感觉差异就在于对include字段的处理上。不知道还有其他情况,会影响计数的值,有了解的可以留言讨论下。
标签:index,JOIN,CREATE,oid,pg,test From: https://www.cnblogs.com/GISyunqi/p/18242733