postgresql 9.6 建立多列索引測試

建立測試表結構

CREATE TABLE t_test

(

    id integer,

    name text COLLATE pg_catalog."default",

    address character varying(500) COLLATE pg_catalog."default"

);

插入測試數據

insert into t_test SELECT generate_series(1,10000000) as key, 'name'||(random()*(10^3))::integer, 'ChangAn Street NO'||(random()*(10^3))::integer;

建立3列索引

create index idx_t_test_id_name_address on t_test(id,name,address);

1.以下查詢語句可以使用索引且較快

索引第一列在where語句，與條件次序無關

一般3毫秒多出結果

explain analyze select * from t_test where id < 2000 and name like 'name%' and address like 'ChangAn%';

explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%' and id < 2000 ;

explain analyze select * from t_test where name like 'name%' and id < 2000 and address like 'ChangAn%'  ;

explain analyze select * from t_test where id < 2000

explain analyze select * from t_test where  name like 'name%' and id < 2000 

explain analyze select * from t_test where  address like 'ChangAn%' and id < 2000 ;

explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%' and id < 2000 ;

2.以下可以使用索引，但是查詢速度較慢

索引第一列在order by 

explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%' order by id;

17S

explain analyze select * from t_test where address like 'ChangAn%' order by id;

8s

explain analyze select * from t_test where name like 'name%' order by id;

9s

以下語句無法使用索引，索引第一列不在 where 或者 order by

explain analyze select * from t_test where address like 'ChangAn%' and name like 'name%';

explain analyze select * from t_test where address like 'ChangAn%';

explain analyze select * from t_test where name like 'name%';

建立雙列索引

create index idx_t_test_name_address on t_test(name,address);

以下語句會使用索引

explain analyze select * from t_test where name = 'name580';

explain analyze select * from t_test where address like 'ChangAn%' and name like 'name580'; 

explain analyze select * from t_test where address like 'ChangAn%' and name = 'name580';

下面語句不會使用索引

explain analyze select * from t_test where name  like 'name%'

explain analyze select * from t_test where address like 'ChangAn%'

explain analyze select * from t_test where address = 'ChangAn Street NO416'