POSTGRESQL区域设置对索引和排序的影响

最新推荐文章于 2024-09-23 11:00:00 发布

rudy_gao

最新推荐文章于 2024-09-23 11:00:00 发布

阅读量1.9k

点赞数 1

分类专栏： postgresql_优化文章标签：索引排序 postgresql

本文链接：https://blog.csdn.net/rudygao/article/details/49246919

版权

文章探讨了POSTGRESQL中区域设置如何影响索引和排序，特别是ORDER BY、文本比较、模式匹配和to_char函数。创建数据库时设定的LC_COLLATE和LC_CTYPE决定了字符串排序顺序和字符分类。非'C'或'POSIX'区域可能影响性能，例如在LIKE子句的索引使用上。通过示例展示了不同区域设置下，如何通过特定操作符类使索引在非标准区域设置中生效。

摘要由CSDN通过智能技术生成

--区域支持是在使用 initdb 创建一个数据库集群的时候自动初始化的，但可在创建数据库时单独指定


--区域设置特别影响下面的 SQL 特性
* 查询中使用 ORDER BY 或者对文本数据的标准比较操作符进行排序
* upper, lower 和 initcap 函数
* 模式匹配运算符(LIKE, SIMILAR TO, 以及 POSIX-风格的正则表达式)； 区域影
响大小写不敏感的匹配和通过字符分类正则表达式的字符分类。
* to_char 函数族
* 使用 LIKE 子句的索引能力




--其中有两个参数可以参考
LC_COLLATE 字符串排序顺序
LC_CTYPE 字符分类(什么是字母?是否区分大小写?)
--LC_COLLATE 和 LC_CTYPE 的设置都是在数据库创建时决定的， 不能被改变除非创建新的数据库


PostgreSQL 里使用非 C 或者 POSIX 区域的缺点是性能影响




--查询服务器支持的区域
select * from pg_collation;


--查看当前数据库的区域设置
show lc_ctype;
show lc_collate;




--下面针对不同的区域查看对索引的影响
--创建数据库，指定区域
createdb test_collcate --lc-collate=C --lc-ctype=C -T template0


test_collcate=# show lc_ctype;
show lc_collate; lc_ctype 
----------
 C
(1 row)


test_collcate=# show lc_collate;
 lc_collate 
------------
 C
(1 row)


--创建测试数据
test_collcate=# create table t3 (id int ,user_name varchar(50));
CREATE TABLE
test_collcate=# insert into t3 select m,'username'||m