建立测试表结构CREATE TABLE t_test( id integer, name text COLLATE pg_catalog."default
建立测试表结构
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'
--结束END--
本文标题: postgresql 9.6 建立多列索引测试
本文链接: https://www.lsjlt.com/news/42454.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
一口价域名售卖能注册吗?域名是网站的标识,简短且易于记忆,为在线用户提供了访问我们网站的简单路径。一口价是在域名交易中一种常见的模式,而这种通常是针对已经被注册的域名转售给其他人的一种方式。
一口价域名买卖的过程通常包括以下几个步骤:
1.寻找:买家需要在域名售卖平台上找到心仪的一口价域名。平台通常会为每个可售的域名提供详细的描述,包括价格、年龄、流
443px" 443px) https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294.jpg https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294-768x413.jpg 域名售卖 域名一口价售卖 游戏音频 赋值/切片 框架优势 评估指南 项目规模
0