Indices
部分 GiST 索引很好，我至少会测试这两个额外的索引：
GIN 索引：
CREATE INDEX ref_name_trgm_gin_idx ON ref_name
USING gin (ref_name gin_trgm_ops)
WHERE ref_name_type = 'E';
这可能会或可能不会被使用。如果您升级到 Postgres 9.4，机会会好得多，因为 GIN 索引有了重大改进。
varchar_pattern_ops 索引：
CREATE INDEX ref_name_pattern_ops_idx
ON ref_name (ref_name varchar_pattern_ops)
WHERE ref_name_type = 'E';
Query
这个查询的核心问题是您遇到了交叉连接O(N²)当对照所有行检查所有行时。当行数非常大时，性能变得难以忍受。您似乎很了解动态。防御措施是限制可能的组合。您已经朝这个方向迈出了一步，限制为相同的第一个字母。
这里一个非常好的选择是建立在特殊才能的基础上GiST指数 for 最近的邻居搜索。有一个说明书上有提示 https://www.postgresql.org/docs/current/pgtrgm.html对于这种查询技术：
这可以通过 GiST 索引非常有效地实现，但不能通过
GIN 索引。当只有一个时，它通常会击败第一个配方
需要少量最接近的匹配。
A 杜松子酒指数可能仍然会习惯此外到 GiST 索引。你必须权衡成本和收益。在 9.4 之前的版本中，坚持使用一个大索引总体上可能会更便宜。但在 9.4 页中这可能是值得的。
Postgres 9.3+
Use a LATERAL加入匹配集到集。类似于章节2a在这个相关的答案中：
- 优化 GROUP BY 查询以检索每个用户的最新行 https://stackoverflow.com/questions/25536422/optimize-group-by-query-to-retrieve-latest-record-per-user/25536748#25536748
SELECT a.ref_name_id
, a.ref_name
, a.name_display
, b.ref_name_id AS match_name_id
, b.ref_name AS match_name
, b.name_display AS match_name_display
FROM ref_name a
CROSS JOIN LATERAL (
SELECT b.ref_name_id, b.ref_name, b.name_display
FROM ref_name b
WHERE b.ref_name ~~ 'A%'
AND b.ref_name_type = 'E'
AND a.ref_name_id < b.ref_name_id
AND a.ref_name % b.ref_name -- also enforce min. similarity
ORDER BY a.ref_name <-> b.ref_name
LIMIT 10 -- max. 10 best matches
) b
WHERE a.ref_name ~~ 'A%' -- you can extend the search
AND a.ref_name_type = 'E'
ORDER BY 1;
fiddle https://dbfiddle.uk/-j5lePwo - with all variants compared to your original query on 40k rows modeled after your case.
Old sqlfiddle http://sqlfiddle.com/#!17/5d4be/1
查询速度比小提琴中的原始查询快 2 - 5 倍。我希望他们能够规模更好有数百万行。你必须进行测试。
扩展对匹配项的搜索b到所有行（同时限制候选者a到一个合理的数字）也相当便宜。我在小提琴中添加了另外两个变体。
旁白：我运行了所有测试text代替varchar，但这应该没有什么区别。
基础知识和链接：
- 使用 LIKE、SIMILAR TO 或正则表达式进行模式匹配 https://dba.stackexchange.com/a/10696/3684
Postgres 9.2
Use 相关子查询来替代尚未存在的缺失LATERAL join:
SELECT a.*
, b.ref_name AS match_name
, b.name_display AS match_name_display
FROM (
SELECT ref_name_id
, ref_name
, name_display
, (SELECT ref_name_id AS match_name_id
FROM ref_name b
WHERE ref_name_type = 'E'
AND ref_name ~~ 'A%'
AND ref_name_id > a.ref_name_id
AND ref_name % a.ref_name
ORDER BY ref_name <-> a.ref_name
LIMIT 1 -- max. 1 best match
)
FROM ref_name a
WHERE ref_name ~~ 'A%'
AND ref_name_type = 'E'
) a
JOIN ref_name b ON b.ref_name_id = a.match_name_id
ORDER BY 1;
显然，这也需要一个索引ref_name_id，通常应该是 PK，因此会自动索引。
I added 还有两个变体 to the fiddle https://dbfiddle.uk/-j5lePwo.
