关于postgresql同一表中的同一列可以填加多个相同索引的问题

关于postgresql同一表中的同一列可以填加多个相同索引的问题 首先我们建一张表 postgres=# create table test (id int ,name text,num numeric); CREATE TABLE 插入数据 postgres=# insert into test values (( generate_series(1,1000)),'张三','123'); INSERT 0 1000 在id这个字段上创建索引 postgres=# create index test1_index on test (id); CREATE INDEX 查看一下 postgres=# \d+ test Table "public.test" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+----------+--------------+------------- id | integer | | plain | | name | text | | extended | | num | numeric | | main | | Indexes: "test1_index" btree (id) 查看一下这个索引的大小 postgres=# select pg_size_pretty(pg_relation_size('test1_index')); pg_size_pretty ---------------- 40 kB (1 row) 查询数据看一下时间 postgres=#\timing postgres=# select * from test where id =155; id | name | num -----+------+----- 155 | 张三 | 123 (1 row) Time: 0.622 ms postgres=# select * from test where id =99; id | name | num ----+------+----- 99 | 张三 | 123 (1 row) Time: 0.541 ms 接着在创建一个索引 postgres=# create index test2_index on test (id); CREATE INDEX postgres=# \d+ test Table "public.test" Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+----------+--------------+------------- id | integer | | plain | | name | text | | extended | | num | numeric | | main | | Indexes: "test1_index" btree (id) "test2_index" btree (id) 查询索引的大小 postgres=# select pg_size_pretty(pg_relation_size('test2_index')); pg_size_pretty ---------------- 40 kB (1 row) postgres=# select * from test where id =99; id | name | num ----+------+----- 99 | 张三 | 123 (1 row) Time: 0.522 ms postgres=# select * from test where id =155; id | name | num -----+------+----- 155 | 张三 | 123 (1 row) 可以看出来第二个索引和第一个索引除了名字不一样以为，其他和第一个没有什么区别。 创建第三个索引 postgres=# create index test3_index on test (id); CREATE INDEX Time: 27.050 ms postgres=# select pg_size_pretty(pg_relation_size('test3_index')); pg_size_pretty ---------------- 40 kB (1 row) Time: 0.741 ms 再插入数据 postgres=# insert into test values (( generate_series(1001,3000)),'李四','123'); INSERT 0 2000 Time: 50.341 ms postgres=# select * from test where id =2000; id | name | num ------+------+----- 2000 | 李四 | 123 (1 row) Time: 0.939 ms postgres=# select * from test where id =2000; id | name | num ------+------+----- 2000 | 李四 | 123 (1 row) Time: 0.365 ms postgres=# select * from test where id =1000; id | name | num ------+------+----- 1000 | 张三 | 123 (1 row) Time: 0.905 ms 查看三个索引的大小 postgres=# select pg_size_pretty(pg_relation_size('test3_index')); pg_size_pretty ---------------- 88 kB (1 row) Time: 0.496 ms postgres=# select pg_size_pretty(pg_relation_size('test2_index')); pg_size_pretty ---------------- 88 kB (1 row) Time: 0.469 ms postgres=# select pg_size_pretty(pg_relation_size('test1_index')); pg_size_pretty ---------------- 88 kB (1 row) 三个索引大小变大，且还是相同的大小。 删除两个索引 postgres=# drop index test2_index ; DROP INDEX Time: 6.601 ms postgres=# drop index test3_index ; DROP INDEX Time: 10.985 ms postgres=# select * from test where id =1000; id | name | num ------+------+----- 1000 | 张三 | 123 (1 row) Time: 0.635 ms postgres=# select * from test where id =1000; id | name | num ------+------+----- 1000 | 张三 | 123 Time: 0.436 ms postgres=# select * from test where id =1000; id | name | num ------+------+----- 1000 | 张三 | 123 Time: 0.363 ms postgres=# select pg_size_pretty(pg_relation_size('test1_index')); pg_size_pretty ---------------- 88 kB (1 row) Time: 0.439 ms 可以发现，在删除了两个索引以后，剩的那个索引的大小并没有变，查询时间上也没有明显的变化。 由此可以的得出：在同一个表的相同的列上建多个相同的索引（除了名字不同）没有什么实际作用，反而会增大表的大小。