PostgreSQL9.3的B-tree索引包含NULL值

PostgreSQL9.3的B-tree索引包含NULL值



测试方法一：对比索引列插入NULL值后，索引的大小变化
--测试记录如下
testdb=> create table table_test(id integer , name varchar(32));
CREATE TABLE
testdb=> create index idx_test_name on table_test using btree (name);
CREATE INDEX
testdb=> select pg_size_pretty(pg_relation_size('idx_test_name'));
 pg_size_pretty
----------------
 8192 bytes
(1 row)

testdb=> insert into table_test values(1,'test1');
INSERT 0 1

testdb=> select pg_size_pretty(pg_relation_size('idx_test_name'));
 pg_size_pretty
----------------
 16 kB
(1 row)

testdb=> insert into table_test values(1,null);
INSERT 0 1

testdb=> select pg_size_pretty(pg_relation_size('idx_test_name'));
 pg_size_pretty
----------------
 16 kB
(1 row)

testdb=> insert into table_test(id) select generate_series(2,100000);
INSERT 0 99999

testdb=> select pg_size_pretty(pg_relation_size('idx_test_name'));
 pg_size_pretty
----------------
 2728 kB
(1 row)

测试表的name列可为NULL，向测试表插入name列为NULL的记录后，查询name列上索引的大小，发现索引大小增加了。



测试方法二：查询有少量NULL值的索引列时，其执行计划是否走此索引
testdb=> create table table_test(id integer , name varchar(32));
CREATE TABLE
testdb=> create index idx_test_name on table_test using btree (name);
CREATE INDEX
testdb=> insert into table_test(id, name) values(999, NULL);
INSERT 0 1
testdb=> insert into table_test(id, name) select generate_series(2,100000), 'notnull';
INSERT 0 99999
testdb=> VACUUM ANALYZE table_test;
VACUUM
testdb=> VACUUM ANALYZE;
VACUUM
testdb=> explain analyze select * from table_test where name is null;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_test_name on table_test  (cost=0.29..4.31 rows=1 width=12) (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: (name IS NULL)
 Total runtime: 0.037 ms
(3 rows)

查询时，索引列过滤条件为NULL，依然走了索引，说明PostgreSQL的B-tree索引是包含NULL值的。