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' (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |