关于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
可以发现，在删除了两个索引以后，剩的那个索引的大小并没有变，查询时间上也没有明显的变化。
由此可以的得出：在同一个表的相同的列上建多个相同的索引（除了名字不同）没有什么实际作用，反而会增大表的大小。