postgresql 9.6 建立多列索引测试

前端之家收集整理的这篇文章主要介绍了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'

原文链接:https://www.f2er.com/postgresql/194273.html

猜你在找的Postgre SQL相关文章