PostgreSQL逐次移除VARCHAR末尾字符直至匹配的高效查询方法

需求场景：对输入的VARCHAR类型拨号号码，逐次移除最右侧字符做匹配，直到找到命中的拨号编码。例如待匹配号码442079285200逐位截断后，最终匹配到归属地为UNITED KINGDOM-LONDON的编码44207。

你需要实现的逻辑本质是拨号编码最长前缀匹配，不需要循环逐次查询，用集合查询可以实现数量级的性能提升。 #

原有PL/pgSQL代码的问题 #

你写的初步代码无法运行、性能差的核心原因有三个：

• 变量名不匹配：声明的输入号码变量是m，查询条件里误用了不存在的dialing_number
• 逻辑缺失：循环内的查询没有接收返回结果，也没有命中后提前终止的逻辑，即使修正变量名也会跑完所有循环，做大量无用查询
• 性能极差：逐位截断循环查询的逻辑，单号码就要执行十几次查询，批量匹配数千个号码时会产生数万次查询，在2万条编码的场景下延迟会非常高

最优实现方案 #

核心思路：不需要逐次截断号码，直接筛选出所有满足「拨号编码是输入号码前缀」的记录，取长度最长的编码就是最终匹配结果，单条SQL即可实现，支持索引优化。

单个号码匹配 #

SELECT
    destination,
    dialing_code,
    current_rate,
    rounding_rule
FROM v_destination_rates
WHERE '442079285200' LIKE dialing_code || '%'
ORDER BY LENGTH(dialing_code) DESC
LIMIT 1;

以上查询会直接返回UNITED KINGDOM-LONDON对应的44207记录，不需要任何循环逻辑。

批量匹配数千个号码 #

不要循环单个查询，用CTE加关联查询一次返回所有结果，性能比循环高几十到上百倍：

WITH input_numbers AS (
    -- 此处替换为待匹配的号码列表，也可关联其他业务表取数
    SELECT UNNEST(ARRAY[
        '442079285200',
        '870123456',
        '882345678',
        '521844207123'
    ]) AS dial_number
)
SELECT DISTINCT ON (dial_number)
    n.dial_number,
    r.destination,
    r.dialing_code,
    r.current_rate,
    r.rounding_rule
FROM input_numbers n
LEFT JOIN v_destination_rates r
    ON n.dial_number LIKE r.dialing_code || '%'
ORDER BY n.dial_number, LENGTH(r.dialing_code) DESC;

性能优化建议 #

针对2万条拨号编码、数千个待匹配号码的场景，做以下优化可以把查询压到毫秒级：

• 给v_destination_rates依赖的底层表的dialing_code字段创建B树索引：dialing_code || '%'这种前缀匹配模式可以直接利用B树索引，避免全表扫描
• 尽量不要用PL/pgSQL循环处理批量数据，PostgreSQL的集合查询性能远好于逐行循环逻辑
• 上述SQL的时间复杂度为O(待匹配号码数 * log(编码总数))，远优于循环方案的O(待匹配号码数 * 号码长度 * 编码总数)

附：v_destination_rates 示例数据 #

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