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'



本文转自 pgmia 51CTO博客,原文链接:http://blog.51cto.com/heyiyi/1873178

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
为什么不走索引, PostgreSQL
我明明创建了索引字段,为什么不选择索引扫描呢?我该注意什么,怎么解决
4083 0
PostgreSQL 快速给指定表每个字段创建索引 - 1
标签 PostgreSQL , 索引 , 所有字段 , 并行创建索引 , max_parallel_maintenance_workers 背景 如何快速给表的所有字段,每个字段都加上索引。 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》 满足任意字段组合查询的需求。
1356 0
【重新发现PostgreSQL之美】- 14 bloom 布隆过滤器索引
大家好,这里是重新发现PostgreSQL之美 - 14 bloom 布隆过滤器索引
56 0
PostgreSQL Heap Only Tuple - HOT (降低UPDATE引入的索引写IO放大)
标签 PostgreSQL , Heap Only Tuple , HOT 背景 PostgreSQL目前默认的存储引擎在更新记录时,会在堆内产生一条新版本,旧版本在不需要使用后VACUUM回收,回收旧版本前,需要先回收所有关联这个版本的所有索引POINT。
1587 0
PostgreSQL GIN索引limit慢的原因分析
PostgreSQL GIN索引的结构如下图 :假设这个表有2列,一列存储INT,另一列存储INT数组,最左边的表示记录的行号。 假设对INT数组建立GIN索引,那么GIN索引会记录每个数组element对应的行号,对于行号多的,会存成LIST,然后在索引中指向该list。 好了接下来分析一下l
6095 0
PostgreSQL 快速给指定表每个字段创建索引 - 2
标签 PostgreSQL , 索引 , 所有字段 , 并行创建单个索引 , max_parallel_maintenance_workers , 异步调用 , dblink , 并行创建多个索引 , adhoc查询 背景 PostgreSQL 支持丰富的类型、索引,统计信息。
989 0
PostgreSQL 12: 新增 pg_stat_progress_create_index 视图监控索引创建进度
PostgreSQL 12 版本之前,对PostgreSQL大表创建索引时是一个比较痛苦的过程,创建索引过程中无法得知索引创建进度,PostgreSQL 12 在运维监控功能方面得到增强,新增 pg_stat_progress_create_index 视图可以监控索引的创建进度,本文简单演示。
1308 0
14491
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载