主页 / dba / 问题 / 157944
Accepted
Zilk
Asked: 2016-12-13 10:54:10 +0800 CST

为什么在条件中使用 OR 时 PostgreSQL 不使用索引？

  • 772

DB结构总结：

  • 主表是cases（大约 136k 行）
  • 每个案例可以有 0 - n 引用表中的行case_contacts
  • 每个案例联系人引用表中的主要联系人contacts
  • 案例联系人也可以引用辅助子联系人，也在表格中contacts
  • 联系人的姓名在 中contacts.v_fullname，使用三元组索引进行索引

目标是查找联系人或子联系人的名称包含字符串“test”的情况：

SELECT  c.id,
        c.number
  FROM  cases c
        JOIN case_contacts caco ON caco.case_id = c.id
        JOIN contacts con_main ON con_main.id = caco.contact_id
        LEFT JOIN contacts con_sub ON con_sub.id = caco.subcontact_id
 WHERE  con_main.v_fullname ILIKE '%test%'
        OR con_sub.v_fullname ILIKE '%test%'

此查询（查询计划）返回正确的结果，但不使用三元索引。大约需要 330 毫秒。

删除任何一个匹配条件（查询计划），或者让它们指向同一个表（查询计划），都可以解决性能问题。这两个都使用三元索引并在 1ms 内执行，但不解决给定的任务。

如何让 PostgreSQL 使用我的索引？

我已将此示例简化为演示效果所需的最低限度。实际的查询要复杂得多（并且部分是自动生成的），因此如果可能的话，使用两个查询的 UNION 并且每个查询只有一个文本匹配会非常困难。

我正在使用 PostgreSQL 9.5.5。
模式仍然可以修改（在某种程度上）。

根据要求，有关索引的更多信息：

dbname=# \di+ *contacts*
                                         List of relations
 Schema |               Name               | Type  | Owner |     Table     |  Size
--------+----------------------------------+-------+-------+---------------+---------
 public | case_contacts_case_id_idx        | index | x     | case_contacts | 4544 kB
 public | case_contacts_contact_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_id_case_id_idx     | index | x     | case_contacts | 4544 kB
 public | case_contacts_idx                | index | x     | case_contacts | 9608 kB
 public | case_contacts_pkey               | index | x     | case_contacts | 4544 kB
 public | case_contacts_reference_trgm_idx | index | x     | case_contacts | 4960 kB
 public | case_contacts_subcontact_id_idx  | index | x     | case_contacts | 4544 kB
 public | case_contacts_type_idx           | index | x     | case_contacts | 6208 kB
 public | case_contacts_unique_types_idx   | index | x     | case_contacts | 5464 kB
 public | contacts_parent_id_id_idx        | index | x     | contacts      | 456 kB
 public | contacts_parent_id_idx           | index | x     | contacts      | 360 kB
 public | contacts_pkey                    | index | x     | contacts      | 360 kB
 public | contacts_v_fullname_trgm_idx     | index | x     | contacts      | 1560 kB
(13 rows)

这就是在 contacts.v_fullname 上创建索引的方式：

CREATE INDEX contacts_v_fullname_trgm_idx ON contacts USING GIN (v_fullname gin_trgm_ops);
postgresql index

1 个回答

  1. Best Answer

    我不能真正回答你的问题，因为我真的不知道为什么，但我找到了一种方法让 PostgreSQL 或多或少地做我猜你想要的。我已经使用简化的模拟场景并使用 PostgreSQL 9.6.1（截至今天的最新版本）测试了您的情况。我得到相同的结果。

    好消息是：如果您可以更改查询方式，那么您有几个使用三元索引的选项。

    第一个包括移动子触点上的条件。在这种情况下，三元组索引用于其中一种情况（但不是另一种情况）：

    SELECT
    c.id, c.number
FROM  
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN 
    (
        SELECT
            * 
        FROM
            contacts  
        WHERE
            v_fullname ilike '%test%' 
    ) AS con_sub ON con_sub.id = caco.subcontact_id
WHERE  
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* if the left join gave an answer, it's got '%test%' */ ;

    使用模拟数据进行的极少数试验（其中大约 0.1%、2.5%、5% 或 25% 的 v_fullname 包含“%test%”）表明执行时间的差异很小。[我的磁盘是 SSD，真正的 HD 可能表现得非常不同。] 这实际上应该用具有真实数据的真实系统进行检查……但似乎是否使用三元索引并没有太大区别。

    PostgreSQL 并不是特别擅长估计搜索“like '%test%'”时会出现多少行，但决定使用哪个计划似乎并不重要。

    还有另一种选择，它（通过我的小实验）在大多数情况下运行得更快一些，而当“%test%”的百分比很低时运行得更快。此选项意味着使用 CTE 来“预过滤”联系人（它使用三元组索引一次，因为它不需要使用两次）：

    WITH filtered_contacts AS
(
SELECT
    *
FROM
    contacts
WHERE
    v_fullname ilike '%test%'
)
SELECT
    c.id, c.number
FROM  
    cases c
    JOIN case_contacts caco ON caco.case_id = c.id
    JOIN filtered_contacts con_main ON con_main.id = caco.contact_id
    LEFT JOIN filtered_contacts con_sub ON con_sub.id = caco.subcontact_id
WHERE
    con_main.v_fullname ILIKE '%test%'
    or con_sub.id is not null /* we need this test again, or we'll miss rows */ ;
    • 2

相关问题