PostgreSQL 11 相同前缀订单号序列排名查询优化方法

背景说明 #

Dok表用于存储订单号，建表语句如下：

create table dok ( doktyyp char(1),
     tasudok char(25) );
CREATE INDEX dok_tasudok_idx ON dok (tasudok);
CREATE UNIQUE INDEX dok_tasudok_unique_idx ON dok (doktyyp,tasudok)
WHERE doktyyp IN ( 'T', 'U') ;

订单号规则为相同前缀加不同后缀，示例如下：

91000465663        
91000465663-1
91000465663-2
91000465663-T
91000465663-T-1

需求 #

当doktyyp字段值固定为'T'时，查询指定前缀的订单号对应的序列排名，预期输出：

91000465663        返回 1
91000465663-1      返回 2
91000465663-2      返回 3
91000465663-T      返回 4
91000465663-T-1    返回 5

原有实现问题 #

现有查询功能正常，但写法冗长，执行时需要先拉取所有匹配前缀的订单、排序后再过滤目标值，性能有优化空间：

with koik as (
select rank() over (order by tasudok), tasudok
from dok 
where doktyyp='T' and tasudok  like '91000465663%'
)

select rank 
from koik 
where tasudok='91000465663-1'

原有执行计划（PostgreSQL 11环境）如下：

"Subquery Scan on koik  (cost=685.04..685.07 rows=1 width=8)"
"  Filter: (koik.tasudok = '91000465663-1'::bpchar)"
"  ->  WindowAgg  (cost=685.04..685.06 rows=1 width=34)"
"        ->  Sort  (cost=685.04..685.05 rows=1 width=26)"
"              Sort Key: dok.tasudok"
"              ->  Bitmap Heap Scan on dok  (cost=23.55..685.03 rows=1 width=26)"
"                    Recheck Cond: (doktyyp = 'T'::bpchar)"
"                    Filter: (tasudok ~~ '91000465663%'::text)"
"                    ->  Bitmap Index Scan on dok_tasudok_unique_idx  (cost=0.00..23.55 rows=437 width=0)"
"                          Index Cond: (doktyyp = 'T'::bpchar)"

优化方案 #

极简高效版（推荐） #

利用排名逻辑等价于“小于等于目标值的符合条件条目数”的特性，直接用计数代替窗口函数，不需要额外排序操作，写法更短、性能提升明显：

SELECT COUNT(*) AS rank
FROM dok
WHERE doktyyp = 'T'
  AND tasudok LIKE '91000465663%'
  AND tasudok <= '91000465663-1';

注：由于你已经为doktyyp='T'的场景建立了doktyyp + tasudok的唯一索引，同前缀下不会有重复的tasudok值，因此计数逻辑和RANK()/ROW_NUMBER()的返回结果完全一致，且可以直接命中现有索引，IO和计算开销都远低于原有写法。

简化窗口函数版 #

如果需要同时查询多个同前缀订单的排名，可以简化原有写法去掉冗余CTE：

SELECT rank
FROM (
  SELECT RANK() OVER (ORDER BY tasudok) AS rank, tasudok
  FROM dok
  WHERE doktyyp = 'T' AND tasudok LIKE '91000465663%'
) t
WHERE tasudok = '91000465663-1';

内容的提问来源于stack exchange，提问作者Andrus