前言

xiongcc老师的一篇短文，Oracle中执行1毫秒的查询，在PostgreSQL中需要6秒？看我如何优化！，想必很多人都看过。这种类似的经历，估计从事过SQL优化调优以及相关维护的人全都经历过。

尤其是遇到char(N)和不定长字符串类型 varchar(N)。当两种类型的字段用到同一个条件表达式里头时，有一头的索引必然用不上。而使用的人还很难查觉，如果不看查询计划的话。

本文要说的是另外两种情形，也蛮有意思的，相当于是小坑，有时候也会闯祸。

案例

案例1: varchar上的索引有时候不生效

这个案例发生在SAP BTP的hyperscaler PG里头，底层提供者可以是aws, azure或GCP任意一个。

先看一下：collate相关配置：en_US.UTF-8，预置的，没有办法。

select name, setting  from pg_settings where name ~ 'collate';
name      |setting    |
----------+-----------+
lc_collate|en_US.UTF-8|

建表测试：

create table customer(id int primary key, col2 varchar(256));
insert into customer select n, 'test ' || n from generate_series(1, 200000) as n;
create index idx_customer_col2 on customer(col2);

explain (analyze, buffers, timing, costs) select * from customer where col2 like 'test 11111%'

我们看看上边的结果：

QUERY PLAN                                                                                                     
---------------------------------------------------------------------------------------------------------------
Seq Scan on customer  (cost=0.00..3582.00 rows=111111 width=15) (actual time=0.010..26.664 rows=111111 loops=1)
  Filter: ((col2)::text ~~ 'test 1%'::text)                                                                    
  Rows Removed by Filter: 88889                                                                                
  Buffers: shared hit=1082                                                                                     
Planning Time: 0.192 ms                                                                                        
Execution Time: 31.667 ms

并没有走索引。这是因为collation用的是en_US.UTF-8. 数据库都提前建好了，也改不了。开发人员大量的代码里头，也无法去改。这种错误都是通过慢查询监控慢慢找出来的。

解决办法：

create index idx_customer_col2_2 on customer(col2 varchar_pattern_ops);

在col2上建索引时，针对不兼容或无法处理的collation, 这个选项就很有用。原始文档出处：

参考：https://www.postgresql.org/docs/14/indexes-opclass.html

The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard “C” locale.

指定针对varchar类型的操作符pattern：varchar_pattern_ops以后，再看执行计划：

explain (analyze, buffers, timing, costs) select * from customer where col2 like 'test 11111%'
QUERY PLAN                                                                                                   
Index Scan using idx_customer_col2_2 on customer  (cost=0.42..8.44 rows=20 width=15) (actual time=0.024..0.028 rows=11 loops=1)|
  Index Cond: (((col2)::text ~>=~ 'test 11111'::text) AND ((col2)::text ~<~ 'test 11112'::text))             
  Filter: ((col2)::text ~~ 'test 11111%'::text)                                                               
  Buffers: shared hit=2 read=3                                                                               
  I/O Timings: read=0.011                                                                                     
Planning Time: 0.184 ms                                                                                       
Execution Time: 0.051 ms

这就是PG里头有时候使用varchar类型，与索引相关的比较诡异的案例。

在实际使用过程当中，因为没有使用上索引，如果用上的是大表并且包含关联，极容易造成额外的tmp文件不断生成，最后DB空间甚至用尽的情况。

上图就来自真实案例当中，每隔一两天就发作一次的tmp disk space用光的情况，最后数据库甚至会出现服务中断的情形。

案例2:

citext类型，使用时也要小心。

提到citext类型，不得不说，它是一个好东西，不区分大小写吗。对吧。请看示例：

drop table customer;
create table customer(id int primary key, col2 citext);
insert into customer select n, 'test ' || n from generate_series(1, 200000) as n;

create index idx_customer_col2 on customer (col2 varchar_pattern_ops);
select * from customer where col2 = 'TesT 11112'
id |col2    |
---+--------+
123|test 11112|
-- 不区分大小写，确实爽

看看查询计划：
explain (analyze, buffers, timing, costs)  select * from customer where col2 = 'TesT 11112'
QUERY PLAN                                                                                           |
-----------------------------------------------------------------------------------------------------+
Seq Scan on customer  (cost=0.00..3582.00 rows=1 width=15) (actual time=5.098..93.873 rows=1 loops=1)|
  Filter: (col2 = 'TesT 11112'::citext)                                                              |
  Rows Removed by Filter: 199999                                                                     |
  Buffers: shared hit=1082                                                                           |
Planning Time: 0.047 ms                                                                              |
Execution Time: 93.900 ms                                                                            |

是不是有点傻眼了？

explain (analyze, buffers, timing, costs) select * from customer where col2 like 'test 11112%'
QUERY PLAN                                                                                                |
----------------------------------------------------------------------------------------------------------+
Seq Scan on customer  (cost=0.00..3582.00 rows=1000 width=15) (actual time=5.935..119.020 rows=11 loops=1)|
  Filter: (col2 ~~ 'test 11112%'::citext)                                                                 |
  Rows Removed by Filter: 199989                                                                          |
  Buffers: shared hit=1082                                                                                |
Planning Time: 0.044 ms                                                                                   |
Execution Time: 119.054 ms                                                                                |

眼看着索引用不上，换成普通索引试下？

drop index idx_customer_col2;
create index idx_customer_col2 on customer (col2);

explain (analyze, buffers, timing, costs)  select * from customer where col2 = 'TesT 11112'
QUERY PLAN                                                                                                   
Index Scan using idx_customer_col2 on customer  (cost=0.42..8.44 rows=1 width=15) (actual time=0.042..0.042 rows=1 loops=1)|
  Index Cond: (col2 = 'TesT 11112'::citext)                                                                  
  Buffers: shared hit=1 read=3                                                                               
  I/O Timings: read=0.011                                                                                     
Planning Time: 0.206 ms                                                                                       
Execution Time: 0.064 ms

看一看，普通索引，等值查询用上索引了。相当可以！！

再看看like查询：

explain (analyze, buffers, timing, costs) select * from customer where col2 like 'test 11112%'
QUERY PLAN                                                                                                |
----------------------------------------------------------------------------------------------------------+
Seq Scan on customer  (cost=0.00..3582.00 rows=1000 width=15) (actual time=5.927..108.945 rows=11 loops=1)|
  Filter: (col2 ~~ 'test 11112%'::citext)                                                                 |
  Rows Removed by Filter: 199989                                                                          |
  Buffers: shared hit=1082                                                                                |
Planning Time: 0.060 ms                                                                                   |
Execution Time: 108.978 ms                                                                                |

看到这儿，是不是有点想哭的感觉。

于是，最后要让like操作利用上这个索引，还得想想办法：

1、改动SQL语句：

select * from customer where col2::varchar like 'test 11112%'

2、再基于::varchar创建对应的索引：

create index idx_customer_col2_2 on customer ((col2::varchar) varchar_pattern_ops);
explain (analyze, buffers, timing, costs) select * from customer where col2::varchar like 'test 11112%';
QUERY PLAN                                                                                                                     |
-------------------------------------------------------------------------------------------------------------------------------+
Index Scan using idx_customer_col2_2 on customer  (cost=0.42..8.44 rows=20 width=15) (actual time=0.025..0.028 rows=11 loops=1)|
  Index Cond: (((col2)::text ~>=~ 'test 11112'::text) AND ((col2)::text ~<~ 'test 11113'::text))                               |
  Filter: ((col2)::text ~~ 'test 11112%'::text)                                                                                |
  Buffers: shared hit=2 read=3                                                                                                 |
  I/O Timings: read=0.011                                                                                                      |
Planning Time: 0.185 ms                                                                                                        |
Execution Time: 0.055 ms                                                                                                       |

这样确实能将索引用上。但是，它有一个缺陷。那就是：

select count(*) from customer where col2::varchar like 'Test 11112%';
count为0
-- 意味着是区分大小写的
select count(*) from customer where col2 like 'Test 11112%';
count|
-----+
   11|

总结：

有得往往可能有失。要想用到相关功能，可能就有所取舍。写了这么多，也不知道写清楚了没有。

越是简单的类型，反而有时候越容易出问题。看来对于异常SQL的监控（执行时间，tmp文件，cpu, 内存消耗）等方面，都能提供有力的证据。越早解决越好，拖到后来，就会变成灾难了。

我是【Sean】, 欢迎大家长按关注并加星公众号：数据库杂记。有好资源相送，同时为你提供及时更新。已关注的朋友，发送0、1到7，都有好资源相送。

往期导读和参考:
1. PostgreSQL中配置单双向SSL连接详解
2. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(1)
3. 提升PSQL使用技巧：PostgreSQL中PSQL使用技巧汇集(2)
4. PostgreSQL SQL的基础使用及技巧
5. PostgreSQL开发技术基础:过程与函数

ITPub博客

害怕不？PG中你很可能经历过：类型转换导致索引不可用, 出现灾难性后果

前言

案例

案例1: varchar上的索引有时候不生效

案例2:

总结：

最新文章