我正在尝试运行一个查询，它连接一个表，并进行模糊字符串比较（使用三元组比较），以查找可能的公司名称匹配。我的目标是返回这样的记录，其中一个记录的公司名称（ref_name字段）的三元组相似性与另一个记录的公司名称匹配。目前，我的阈值设置为0.9，所以它只会带回很可能包含类似字符串的匹配。
我知道自连接本质上会导致许多比较，但我希望尽可能优化查询。我不需要立即得到结果，但目前我正在运行的查询需要11个小时才能运行。
我在Ubuntu 12.04服务器上运行Postgres 9.2。我不知道ref_name字段（我正在匹配的字段）的最大长度是多少，所以我将其设置为varchar(300)。我想知道是否将其设置为文本类型可能会影响性能，或者是否有更好的字段类型可用于加速性能。我的LC_CTYPE和LC_COLLATE区域设置为"en_US.UTF-8"
我运行查询的表总共包含大约160万条记录，但是我花了11个小时运行的查询是在其中的一个子集上（大约100k）。
表格结构：

CREATE TABLE ref_name (
  ref_name_id integer,
  ref_name character varying(300),
  ref_name_type character varying(2),
  name_display text,
  load_date timestamp without time zone
)

字符串
指标：

CREATE INDEX ref_name_ref_name_trigram_idx ON ref_name
  USING gist (ref_name COLLATE pg_catalog."default" gist_trgm_ops);

CREATE INDEX ref_name_ref_name_trigram_idx_1 ON ref_name
  USING gist (ref_name COLLATE pg_catalog."default" gist_trgm_ops)
  WHERE ref_name_type::text = 'E'::text;

CREATE INDEX ref_name_ref_name_e_idx ON ref_name
  USING btree (ref_name COLLATE pg_catalog."default")
  WHERE ref_name_type::text = 'E'::text;

型
查询：

select a.ref_name_id as name_id,a.ref_name AS name,
  a.name_display AS name_display,b.ref_name_id AS matched_name_id,
  b.ref_name AS matched_name,b.name_display AS matched_name_display
from ref_name a
JOIN ref_name b
 ON a.ref_name_id<>b.ref_name_id
 AND a.ref_name_id>b.ref_name_id
 AND a.ref_name % b.ref_name
WHERE 
 a.ref_name ~>=~ 'A' and a.ref_name ~<~'B'
 AND b.ref_name ~>=~ 'A' and b.ref_name ~<~'B'
 AND a.ref_name_type='E'
 AND b.ref_name_type='E'

型
解释计划：

"Nested Loop  (cost=0.00..8560728.16 rows=3598470 width=96)"
"  ->  Seq Scan on ref_name a  (cost=0.00..96556.12 rows=103901 width=48)"
"        Filter: (((ref_name)::text ~>=~ 'A'::text) AND ((ref_name)::text ~<~ 'B'::text) AND ((ref_name_type)::text = 'E'::text))"
"  ->  Index Scan using ref_name_ref_name_trigram_idx_1 on ref_name b  (cost=0.00..80.41 rows=35 width=48)"
"        Index Cond: ((a.ref_name)::text % (ref_name)::text)"
"        Filter: (((ref_name)::text ~>=~ 'A'::text) AND ((ref_name)::text ~<~ 'B'::text) AND (a.ref_name_id <> ref_name_id) AND (a.ref_name_id > ref_name_id))"

型
以下是一些示例记录：

1652632;"A 123 SYSTEMS";"E";"A 123 SYSTEMS INC";"2014-11-14 00:00:00"
1652633;"A123 SYSTEMS";"E";"A123 SYSTEMS INC";"2014-11-14 00:00:00"
1652640;"A 1 ACCOUSTICS";"E";"A-1 ACCOUSTICS";"2014-11-14 00:00:00"
1652641;"A 1 ACOUSTICS";"E";"A-1 ACOUSTICS";"2014-11-14 00:00:00"
1652642;"A1 ACOUSTICS";"E";"A1 ACOUSTICS INC";"2014-11-14 00:00:00"
1652650;"A 1 A ELECTRICAL";"E";"A-1 A ELECTRICAL INC";"2014-11-14 00:00:00"
1652651;"A 1 A ELECTRICIAN";"E";"A 1 A ELECTRICIAN INC";"2014-11-14 00:00:00"
1652652;"A 1A ELECTRICIAN";"E";"A 1A ELECTRICIAN INC";"2014-11-14 00:00:00"
1652653;"A1 A ELECTRICIAN";"E";"A1 A ELECTRICIAN INC";"2014-11-14 00:00:00"
1691270;"ALBERT GARLATTI";"E";"ALBERT GARLATTI";"2014-11-14 00:00:00"
1691271;"ALBERT GARLATTI CONSTRUCTION";"E";"ALBERT GARLATTI CONSTRUCTION CO";"2014-11-14 00:00:00"
1680892;"AG HOG PITTSBURGH";"E";"AG-HOG PITTSBURGH CO INC";"2014-11-14 00:00:00"
1680893;"AGHOG PITTSBURGH";"E";"AGHOG PITTSBURGH CO";"2014-11-14 00:00:00"
1680928;"AGILE PURSUITS FRACHISING";"E";"AGILE PURSUITS FRACHISING INC";"2014-11-14 00:00:00"
1680929;"AGILE PURSUITS FRANCHISING";"E";"AGILE PURSUITS FRANCHISING INC";"2014-11-14 00:00:00"
1680956;"AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT";"E";"AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORT";"2014-11-14 00:00:00"
1680957;"AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI";"E";"AGING COMMUNITY COORDINATED ENTERPRISES & SUPPORTI";"2014-11-14 00:00:00"

型
正如您所看到的，我创建了一个gist三元组索引来加快速度（到目前为止尝试了两种不同的类型进行比较）。有没有人对我如何提高这个查询的性能并将其从11个小时减少到更易于管理的时间有任何建议？最后，我想对整个表运行这个查询来比较记录，而不仅仅是这个小的子集。

索引

部分GiST指数是好的，我至少会测试这两个额外的指数：
A 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';

型

查询

这个查询的核心问题是，当检查所有行时，你会遇到一个交叉连接，O（N²）。当行数非常多时，性能变得难以承受。你似乎很清楚形势。防御是限制可能的组合。你已经朝着这个方向迈出了一步，限制在同一个第一个字母。
这里一个很好的选择是建立在一个特殊的天赋GiST指数为最近的邻居搜索。有一个hint in the manual用于此查询技术：
这可以通过GiST索引而不是GIN索引非常有效地实现。当只需要少量最接近的匹配时，它通常会击败第一个公式。

GIN索引可能仍然会被使用 * 除了 * GiST索引。你必须权衡成本和收益。在9.4之前的版本中坚持使用一个大索引可能会更便宜。但在pg 9.4中可能是值得的。

Postgres 9.3+

使用LATERAL联接来匹配要设置的集合。类似于本相关答案中的第 2a 章：

优化GROUP BY查询以检索每个用户的最新行

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-所有变体都与根据案例建模的40 k行上的原始查询进行比较。
老麻雀
查询速度比原始查询快2 - 5倍。我希望它们能更好地扩展数百万行。你得先测试一下。
将b中的匹配搜索扩展到所有行（同时将a中的候选项限制在合理的数量）也相当便宜。我在小提琴上增加了另外两个变体。
旁白：我用text而不是varchar运行了所有测试，但这应该没有什么区别。
基础知识和链接：

Pattern matching with LIKE, SIMILAR TO or regular expressions的

Postgres 9.2

使用correlated subqueries替换尚未存在的missing 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，因此会自动索引。
我在fiddle中添加了两个变体。

postgresql 使用相似性Postgres模糊自连接查询提高性能

1条答案

索引

查询

Postgres 9.3+

Postgres 9.2

相关问题

热门标签

最新问答