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

1 回答

• 6

  指数

  部分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';
  

  查询

  当您针对所有行检查所有行时，此查询的核心问题是您正在与 O(N²) 进行交叉连接 . 对于大量的行，性能变得难以忍受 . 你似乎很清楚这种动态 . 辩护是限制可能的组合 . 你已朝这个方向迈出了一步，限制了相同的第一个字母 .

  这里一个非常好的选择是 Build 在 GiST indices 的特殊天赋上，用于 nearest neighbour 搜索 . 这种查询技术有一个hint in the manual：

  这可以通过GiST索引非常有效地实现，但不能通过GIN索引实现 . 当只需要少量最接近的匹配时，它通常会超过第一个公式 .

  除了GiST索引之外，还可以使用 GIN index . 你必须权衡成本和收益 . 在9.4之前的版本中坚持使用一个大索引可能会更便宜 . 但它可能在第9.4页中值得 .

  Postgres 9.2

  使用 correlated subqueries 替换尚未存在的缺失 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，因此自动索引 .

  我在SQL Fiddle中添加了 two more variants .

  Postgres 9.3

  使用 LATERAL join将匹配集设置为set . 与此相关答案中的第2a章类似：

  • Optimize GROUP BY query to retrieve latest record per user

  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
  ,   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;
  

  SQL Fiddle所有变体与您的案例后建模的40k行的原始查询进行比较 .

  查询比你在小提琴中的原始速度快2到5倍 . 而且我希望它们能够在数百万行的情况下更好地扩展 . 你必须测试 .

  将 b 中的匹配搜索扩展到所有行（同时将 a 中的候选限制为合理的数字）也相当便宜 . 我在小提琴中添加了另外两个变体 .

  旁白：我用 text 而不是 varchar 运行所有测试，但这不应该有所作为 .

  基础知识和链接：

  • Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

  回复于 2024-05-02T16:33:55+08:00