Postgresql - Multicolumn Indexes 组合索引

0. 建实验表，并插入数据

 

mytest=# \d test0524;
                                      Table "public.test0524"
 Column |            Type             | Collation | Nullable |               Default
--------+-----------------------------+-----------+----------+--------------------------------------
 id     | integer                     |           | not null | nextval('test0523_id_seq'::regclass)
 col1   | character varying(20)       |           |          |
 col2   | character varying(20)       |           |          |
 col3   | character varying(20)       |           |          |
 c_time | timestamp without time zone |           |          | now()

1. 主键，组合索引，

mytest=# alter table test0524 add primary key (id, col1);
ALTER TABLE
mytest=# analyze test0524;
ANALYZE
mytest=# explain select * from test0524 where id = 1mytest-# ;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using test0524_pkey on test0524  (cost=0.28..8.29 rows=1 width=19)
   Index Cond: (id = 1)
(2 rows)

mytest=# explain select * from test0524 where id = 1 and col1 = 'a';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using test0524_pkey on test0524  (cost=0.28..8.29 rows=1 width=19)
   Index Cond: ((id = 1) AND ((col1)::text = 'a'::text))
(2 rows)

mytest=# explain select * from test0524 where  col1 = 'a';
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on test0524  (cost=0.00..19.80 rows=384 width=19)
   Filter: ((col1)::text = 'a'::text)
(2 rows)

组合主键中，查询第二个列不会使用到索引。

 

2. 普通组合索引（两列）。

 

mytest=# create index on test0524 (col1, col2);
CREATE INDEX

mytest=# analyze test0524;
ANALYZE

mytest=# explain select * from test0524 where col1 = 'a' and col2 = 'aa';
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.55..17.96 rows=27 width=19)
   Recheck Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'aa'::text))
   ->  Bitmap Index Scan on test0524_col1_col2_idx  (cost=0.00..4.54 rows=27 width=0)
         Index Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'aa'::text))
(4 rows)

mytest=# explain select * from test0524 where col1 = 'a';
                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.61..18.15 rows=43 width=19)
   Recheck Cond: ((col1)::text = 'a'::text)
   ->  Bitmap Index Scan on test0524_col1_col2_idx  (cost=0.00..4.60 rows=43 width=0)
         Index Cond: ((col1)::text = 'a'::text)
(4 rows)

mytest=# explain select * from test0524 where  col2 = 'aa';
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on test0524  (cost=0.00..19.80 rows=71 width=19)
   Filter: ((col2)::text = 'aa'::text)
(2 rows)

在两个列的组合索引中，单独查询第二个列不会使用到索引。但是单独查询第一个

 

3. 组合索引（多余两列）

 

mytest=# drop index test0524_col1_col2_idx ;
DROP INDEX
mytest=# create index on test0524(col1,col2,col3,c_time);
CREATE INDEX

mytest=# explain select * from test0524 where  col1 = 'a';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.61..12.15 rows=43 width=20)
   Recheck Cond: ((col1)::text = 'a'::text)
   ->  Bitmap Index Scan on test0524_col1_col2_col3_c_time_idx  (cost=0.00..4.60 rows=43 width=0)
         Index Cond: ((col1)::text = 'a'::text)
(4 rows)

mytest=# explain select * from test0524 where  col1 = 'a' and col2 = 'bb' ;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.69..12.29 rows=40 width=20)
   Recheck Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text))
   ->  Bitmap Index Scan on test0524_col1_col2_col3_c_time_idx  (cost=0.00..4.68 rows=40 width=0)
         Index Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text))
(4 rows)

mytest=# explain select * from test0524 where  col1 = 'a' and col2 = 'bb' and col3 = 'ccc';
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.73..12.36 rows=36 width=20)
   Recheck Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text) AND ((col3)::text = 'ccc'::text))
   ->  Bitmap Index Scan on test0524_col1_col2_col3_c_time_idx  (cost=0.00..4.73 rows=36 width=0)
         Index Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text) AND ((col3)::text = 'ccc'::text))
(4 rows)

mytest=# explain select * from test0524 where  col1 = 'a' and col2 = 'bb' and col3 = 'ccc' and c_time > '2019-05-20';
                                                                                     QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.82..12.54 rows=36 width=20)
   Recheck Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text) AND ((col3)::text = 'ccc'::text) AND (c_time > '2019-05-20 00:00:00'::timestamp without time zone))
   ->  Bitmap Index Scan on test0524_col1_col2_col3_c_time_idx  (cost=0.00..4.82 rows=36 width=0)
         Index Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text) AND ((col3)::text = 'ccc'::text) AND (c_time > '2019-05-20 00:00:00'::timestamp without time zone))
(4 rows)

mytest=# explain select * from test0524 where col2 = 'bb' ;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on test0524  (cost=0.00..19.80 rows=953 width=20)
   Filter: ((col2)::text = 'bb'::text)
(2 rows)

mytest=# explain select * from test0524 where col2 = 'bb' and col3 = 'ccc' ;
                                QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on test0524  (cost=0.00..22.36 rows=848 width=20)
   Filter: (((col2)::text = 'bb'::text) AND ((col3)::text = 'ccc'::text))
(2 rows)

mytest=# explain select * from test0524 where col2 = 'bb' and col3 = 'ccc' and c_time > '2019-05-20';
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test0524  (cost=0.00..24.92 rows=848 width=20)
   Filter: ((c_time > '2019-05-20 00:00:00'::timestamp without time zone) AND ((col2)::text = 'bb'::text) AND ((col3)::text = 'ccc'::text))
(2 rows)

mytest=# explain select * from test0524 where col2 = 'bb' and col1 = 'a' ;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.69..12.29 rows=40 width=20)
   Recheck Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text))
   ->  Bitmap Index Scan on test0524_col1_col2_col3_c_time_idx  (cost=0.00..4.68 rows=40 width=0)
         Index Cond: (((col1)::text = 'a'::text) AND ((col2)::text = 'bb'::text))
(4 rows)

mytest=# explain select * from test0524 where  col1 = 'a' and col3 = 'ccc';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test0524  (cost=4.71..12.28 rows=38 width=20)
   Recheck Cond: (((col1)::text = 'a'::text) AND ((col3)::text = 'ccc'::text))
   ->  Bitmap Index Scan on test0524_col1_col2_col3_c_time_idx  (cost=0.00..4.71 rows=38 width=0)
         Index Cond: (((col1)::text = 'a'::text) AND ((col3)::text = 'ccc'::text))
(4 rows)

说明在查询条件中，必须要有创建索引时的第一列，则可以走索引。

如果没有第一列，是肯定无法走索引的。

 

 

删除的列，是组合索引中的列。

mytest=# \d test0524;
                                      Table "public.test0524"
 Column |            Type             | Collation | Nullable |               Default
--------+-----------------------------+-----------+----------+--------------------------------------
 id     | integer                     |           | not null | nextval('test0523_id_seq'::regclass)
 col1   | character varying(20)       |           | not null |
 col2   | character varying(20)       |           |          |
 col3   | character varying(20)       |           |          |
 c_time | timestamp without time zone |           |          | now()
Indexes:
    "test0524_pkey" PRIMARY KEY, btree (id, col1)
    "test0524_col1_col2_col3_c_time_idx" btree (col1, col2, col3, c_time)

mytest=# alter table test0524 drop column col3;
ALTER TABLE
mytest=# \d test0524;
                                      Table "public.test0524"
 Column |            Type             | Collation | Nullable |               Default
--------+-----------------------------+-----------+----------+--------------------------------------
 id     | integer                     |           | not null | nextval('test0523_id_seq'::regclass)
 col1   | character varying(20)       |           | not null |
 col2   | character varying(20)       |           |          |
 c_time | timestamp without time zone |           |          | now()
Indexes:
    "test0524_pkey" PRIMARY KEY, btree (id, col1)

索引会同列一起删除，并且删除的时候不会有任何提示。