Oracle中执行1毫秒的查询，在PostgreSQL中需要6秒？看我如何优化！

前言

今天生产中遇到一个比较典型的 SQL 优化案例——隐式转换，关于隐式转换，想必每个人都曾有所耳闻，今天向各位分享一个十分经典的 SQL 优化例子，一个不起眼的隐式转换，居然能让性能亏损上万倍。

小案例

首先看个最为直观的例子

postgres=# create table test1(id int primary key);
CREATE TABLE
postgres=# insert into test1 values(generate_series(1,100000));
INSERT 0 100000
postgres=# analyze test1;
ANALYZE
postgres=# explain select * from test1 where id::text = '999';
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test1  (cost=0.00..2193.00 rows=500 width=4)
   Filter: ((id)::text = '999'::text)
(2 rows)

postgres=# explain select * from test1 where id = '999'::numeric;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test1  (cost=0.00..1943.00 rows=500 width=4)
   Filter: ((id)::numeric = '999'::numeric)
(2 rows)

postgres=# explain select * from test1 where id::text = lower('999');
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on test1  (cost=0.00..2193.00 rows=500 width=4)
   Filter: ((id)::text = '999'::text)
(2 rows)

可以看到，不仅索引失效了，并且预估的 rows 也严重失真，使用了默认选择率，函数和操作符的默认选择率是 0.005

/* default selectivity estimate for equalities such as "A = b" */
#define DEFAULT_EQ_SEL 0.005

/* default selectivity estimate for range inequalities "A > b AND A < c" */
#define DEFAULT_RANGE_INEQ_SEL 0.005

/* default selectivity estimate for multirange inequalities "A > b AND A < c" */
#define DEFAULT_MULTIRANGE_INEQ_SEL 0.005

/* default selectivity estimate for pattern-match operators such as LIKE */
#define DEFAULT_MATCH_SEL 0.005

所以其影响不言而喻，索引失效，其次预估 rows 有偏差，还可能导致走错关联方式。

让我们再看一个更为常见的场景，从其他数据库迁移过来的朋友，可能会去选择使用 char (我就遇到了这样的场景)，但是在 PostgreSQL 中是不推荐使用 char，优先使用 varchar 和 text，这在官网上也有说明

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(*n*) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(*n*) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

以生产中遇到的查询为例，在 Oracle 中只需要 1 毫秒，但是在 PostgreSQL 中却需要六七秒！是什么导致了如此大的性能差异？

postgres=# create table t1(id int,info varchar(20));
CREATE TABLE
postgres=# create table t2(id int,info char(20));
CREATE TABLE
postgres=# create index on t1(info);
CREATE INDEX
postgres=# create index on t2(id);
CREATE INDEX
postgres=# insert into t1 select n,left(md5(random()::text),20) from generate_series(1,1000000) as n;
INSERT 0 1000000
postgres=# insert into t2 select n,left(md5(random()::text),20) from generate_series(1,1000000) as n;
INSERT 0 1000000
postgres=# analyze t1,t2;
ANALYZE
postgres=# explain select info from t1 where info in (select info from t2 where id = 99);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Gather  (cost=1008.46..13621.97 rows=1 width=21)
   Workers Planned: 2
   ->  Hash Semi Join  (cost=8.46..12621.87 rows=1 width=21)
         Hash Cond: ((t1.info)::bpchar = t2.info)
         ->  Parallel Seq Scan on t1  (cost=0.00..11519.67 rows=416667 width=21)
         ->  Hash  (cost=8.44..8.44 rows=1 width=21)
               ->  Index Scan using t2_id_idx on t2  (cost=0.42..8.44 rows=1 width=21)
                     Index Cond: (id = 99)
(8 rows)

t1 表上有索引的，因此理论上，t2 表根据索引扫描获取出结果之后，t1 表再根据 info 上的索引去获取是最为高效的，并且站在上帝视角，这种关联无疑走 NLJ 是最为高效的，毕竟满足 id = 99 的就一条数据，然后利用索引只需要部分扫描，但是优化器却选择走了 HASH JOIN (内层数据集和外层数据集需要全部扫描)，并且也没有使用索引，与我们的 SQL 优化法则中的减少扫描数据集思想背道而驰。没错，Oracle 中就是这么执行的，但在 PostgreSQL 中却笨呼呼地走到了 HASHJOIN (此处多谢老虎刘大师关于 O 执行计划的指点)。索引当然是正常的，如下查询便可以走到索引扫描

postgres=# explain select info from t1 where info = 'hello';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Only Scan using t1_info_idx on t1  (cost=0.42..4.44 rows=1 width=21)
   Index Cond: (info = 'hello'::text)
(2 rows)

细心的读者肯定也发现了端倪，的确，字段类型在作祟。关联条件是 Hash Cond: ((t1.info)::bpchar = t2.info)，说明将 t1.info 转化了为 bpchar，也就是转化为了 t2.info 的类型——char，bpchar 是 PostgreSQL 中的一个扩展类型，bpchar (“blank-padded char”, the internal name of the character data type)

bpchar (with length specifier) and char are aliases for character. The varchar and char aliases are defined in the SQL standard; bpchar is a PostgreSQL extension.

If bpchar lacks a length specifier, it also accepts strings of any length, but trailing spaces are semantically insignificant

bpchar 中的 b 是 blank 的缩写，而 p 则代表着 padding，如果指定了长度，就相当于是 char 的别名，如果不指定长度，也接受任意长度的字符串

postgres=# select pg_column_size (row('hello'::varchar(10))) - 24 as size;
 size 
------
    6
(1 row)

postgres=# select pg_column_size (row('hello'::char(10))) - 24 as size;
 size 
------
   11
(1 row)

postgres=# select pg_column_size (row('hello'::bpchar(10))) - 24 as size;
 size 
------
   11
(1 row)

postgres=# select pg_column_size (row('hello'::bpchar)) - 24 as size;
 size 
------
    6
(1 row)

相差为什么是 5 个字节，其原因不言而喻，后面的补全占据；那么为什么是 6 个字节？卖个关子，感兴趣的读者可以参照之前写的文章。

在执行计划中，你会看到在内部处理中，char 会被自动转化为 bpchar。

postgres=# create table t4(info char(10));
CREATE TABLE
postgres=# insert into t4 values('hello');
INSERT 0 1
postgres=# explain select * from t4 where info = 'hello';
                     QUERY PLAN                     
----------------------------------------------------
 Seq Scan on t4  (cost=0.00..24.12 rows=6 width=44)
   Filter: (info = 'hello'::bpchar)
(2 rows)

关于 bpchar，之前彭冲曾分享过一个实用小技巧

OK，铺垫了这么久，回到正题，为什么这个查询走不了索引？t1.info 创建的索引是基于 varchar，而关联条件被转化为了 bpchar，所以无法使用索引。

现在，让我们稍微调整一下字段类型，t1.info 由 varchar(20) → char(20)，t2.info 由 char(20) → varchar(20)，注意此刻的执行计划！

postgres=# alter table t1 alter column info type char(20);
ALTER TABLE
postgres=# alter table t2 alter column info type varchar(20);
ALTER TABLE
postgres=# analyze t1,t2;
ANALYZE
postgres=# explain select info from t1 where info in (select info from t2 where id = 99);
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Nested Loop  (cost=8.87..16.91 rows=1 width=21)
   ->  HashAggregate  (cost=8.45..8.46 rows=1 width=21)
         Group Key: (t2.info)::bpchar
         ->  Index Scan using t2_id_idx on t2  (cost=0.42..8.44 rows=1 width=21)
               Index Cond: (id = 99)
   ->  Index Only Scan using t1_info_idx on t1  (cost=0.42..8.44 rows=1 width=21)
         Index Cond: (info = (t2.info)::bpchar)
(7 rows)

这一次，可以看到走到了索引扫描，并且关联方式也选择了高效的 NLJ，索引的过滤条件也变成了 Index Cond: (info = (t2.info)::bpchar)，t1.info 上的索引现在是基于 char 类型创建的，自然可以使用到索引，当然这种方式有点绕，并且会导致重写，对于大表无疑会十分费力。

postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/33405/192950
(1 row)

postgres=# select pg_relation_filepath('t1_info_idx');
 pg_relation_filepath 
----------------------
 base/33405/192953
(1 row)

postgres=# alter table t1 alter column info type char(20);
ALTER TABLE
postgres=# select pg_relation_filepath('t1');   ---表自然需要重写
 pg_relation_filepath 
----------------------
 base/33405/192959
(1 row)

postgres=# select pg_relation_filepath('t1_info_idx');  ---索引也发生了重写
 pg_relation_filepath 
----------------------
 base/33405/192962
(1 row)

经过优化之后，SQL 的执行效率从最开始的 400 毫秒变成了 0.1 毫秒，足足提升了 4000倍！因此，实际生产中，需要确保关联字段类型保持一致。

下面例子各位读者可以自行模拟一下，加深印象

postgres=# create table t3(info char(20),info2 varchar(20));
CREATE TABLE
postgres=# insert into t3 select left(md5(random()::text),20),left(md5(random()::text),20) from generate_series(1,1000000) ;
INSERT 0 1000000
postgres=# create index on t3(info);
CREATE INDEX
postgres=# create index on t3(info2);
CREATE INDEX
postgres=# explain select info from t3 where info = 'hello';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Only Scan using t3_info_idx on t3  (cost=0.42..8.44 rows=1 width=21)
   Index Cond: (info = 'hello'::bpchar)
(2 rows)

postgres=# explain select info from t3 where info::char(5) = 'hello';
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Gather  (cost=1000.00..15684.71 rows=5000 width=21)
   Workers Planned: 3
   ->  Parallel Seq Scan on t3  (cost=0.00..14184.71 rows=1613 width=21)
         Filter: ((info)::character(5) = 'hello'::bpchar)
(4 rows)

postgres=# explain select info from t3 where info::bpchar = 'hello';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Only Scan using t3_info_idx on t3  (cost=0.42..4.44 rows=1 width=21)
   Index Cond: (info = 'hello'::bpchar)
(2 rows)

postgres=# explain select info from t3 where info::varchar = 'hello';
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Gather  (cost=1000.00..15684.71 rows=5000 width=21)
   Workers Planned: 3
   ->  Parallel Seq Scan on t3  (cost=0.00..14184.71 rows=1613 width=21)
         Filter: (((info)::character varying)::text = 'hello'::text)
(4 rows)

postgres=# explain select info from t3 where info2 = 'hello'::bpchar;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Gather  (cost=1000.00..14378.36 rows=1 width=21)
   Workers Planned: 3
   ->  Parallel Seq Scan on t3  (cost=0.00..13378.26 rows=1 width=21)
         Filter: ((info2)::bpchar = 'hello'::bpchar)
(4 rows)

postgres=# explain select info from t3 where info2 = 'hello'::char;
                              QUERY PLAN                              
----------------------------------------------------------------------
 Gather  (cost=1000.00..14378.36 rows=1 width=21)
   Workers Planned: 3
   ->  Parallel Seq Scan on t3  (cost=0.00..13378.26 rows=1 width=21)
         Filter: ((info2)::bpchar = 'h'::character(1))
(4 rows)

postgres=# explain select info from t3 where info2 = 'hello'::char(5);
                              QUERY PLAN                              
----------------------------------------------------------------------
 Gather  (cost=1000.00..14378.36 rows=1 width=21)
   Workers Planned: 3
   ->  Parallel Seq Scan on t3  (cost=0.00..13378.26 rows=1 width=21)
         Filter: ((info2)::bpchar = 'hello'::character(5))
(4 rows)

小结

隐式转换不仅可能导致索引失效，还可能会导致数据库选择使用默认选择率，进一步导致预估 rows 严重失真，如果是多表关联，意味着还可能走到一个糟糕的关联方式，因此，确保关联字段类型最好保持一致。

参考

https://stackoverflow.com/questions/51421269/why-char-datatype-is-converted-to-bpchar-automatically