为什么Postgres在两个独立表上使用OR条件时不使用索引

Question

我试图提高在Postgres 9.4数据库上SQL查询的性能。我设法重写了查询，以便使用索引，并且现在速度非常快！但是我不太理解为什么。

这是原始查询：

SELECT DISTINCT dt.id, dt.updated_at
FROM public.day dt
    INNER JOIN public.optimized_localized_day sldt ON sldt.day_id = dt.id
    INNER JOIN public.day_template_place dtp ON dtp.day_template_id = dt.id
    INNER JOIN public.optimized_place op ON op.geoname_id = dtp.geoname_id
WHERE
    op.alternate_localized_names ILIKE unaccent('%query%') OR
    lower(sldt.unaccent_title) LIKE unaccent(lower('%query%')) OR
    lower(sldt.unaccent_description) LIKE unaccent(lower('%query%'))
ORDER BY dt.updated_at DESC
LIMIT 100;

我已经在op.alternate_localized_names、lower(sldt.unaccent_title)和lower(sldt.unaccent_description)上使用了pg_trgm来放置三个三元组索引。

但是，Postgres没有使用它们，而是执行了一次SeqScan来加入整张表格，如EXPLAIN所示：

Limit
  ->  Unique
        ->  Sort
              Sort Key: dt.updated_at, dt.id
              ->  Hash Join
                    Hash Cond: (sldt.day_id = dt.id)
                    Join Filter: ((op.alternate_localized_names ~~* unaccent('%query%'::text)) OR (lower(sldt.unaccent_title) ~~ unaccent('%query%'::text)) OR (lower(sldt.unaccent_description) ~~ unaccent('%query%'::text)))
                    ->  Seq Scan on optimized_localized_day sldt
                    ->  Hash
                          ->  Hash Join
                                Hash Cond: (dtp.geoname_id = op.geoname_id)
                                ->  Hash Join
                                      Hash Cond: (dtp.day_template_id = dt.id)
                                      ->  Seq Scan on day_template_place dtp
                                      ->  Hash
                                            ->  Seq Scan on day dt
                                ->  Hash
                                      ->  Seq Scan on optimized_place op

然而，当我将查询拆分成两部分，一部分搜索 public.optimized_localized_day，另一部分搜索 public.optimized_place 时，它现在使用它们的索引：

SELECT DISTINCT dt.id, dt.updated_at
FROM public.day dt
         INNER JOIN public.day_template_place dtp ON dtp.day_template_id = dt.id
         INNER JOIN public.optimized_place op ON op.geoname_id = dtp.geoname_id
WHERE op.alternate_localized_names ILIKE unaccent('%query%')
UNION
SELECT DISTINCT dt.id, dt.updated_at
FROM public.day dt
         INNER JOIN public.optimized_localized_day sldt ON sldt.day_id = dt.id
WHERE lower(sldt.unaccent_title) LIKE unaccent(lower('%query%'))
   OR lower(sldt.unaccent_description) LIKE unaccent(lower('%query%'));

关于 EXPLAIN:

HashAggregate
  ->  Append
        ->  HashAggregate
              ->  Nested Loop
                    ->  Nested Loop
                          ->  Bitmap Heap Scan on optimized_place op
                                Recheck Cond: (alternate_localized_names ~~* unaccent('%query%'::text))
                                ->  Bitmap Index Scan on idx_trgm_place_lower
                                      Index Cond: (alternate_localized_names ~~* unaccent('%jericho%'::text))
                          ->  Bitmap Heap Scan on day_template_place dtp
                                Recheck Cond: (geoname_id = op.geoname_id)
                                ->  Bitmap Index Scan on day_template_place_geoname_idx
                                      Index Cond: (geoname_id = op.geoname_id)
                    ->  Index Scan using day_pkey on day dt
                          Index Cond: (id = dtp.day_template_id)
        ->  HashAggregate
              ->  Nested Loop
                    ->  Bitmap Heap Scan on optimized_localized_day sldt
                          Recheck Cond: ((lower(unaccent_title) ~~ unaccent('%query%'::text)) OR (lower(unaccent_description) ~~ unaccent('%query%'::text)))
                          ->  BitmapOr
                                ->  Bitmap Index Scan on tgrm_idx_localized_day_title
                                      Index Cond: (lower(unaccent_title) ~~ unaccent('%query%'::text))
                                ->  Bitmap Index Scan on tgrm_idx_localized_day_description
                                      Index Cond: (lower(unaccent_description) ~~ unaccent('%query%'::text))
                    ->  Index Scan using day_pkey on day dt_1
                          Index Cond: (id = sldt.day_id)

据我所知，在OR子句中在两个不同的表上设置条件会导致Postgres先连接这些表，然后再进行筛选。但是我对此并不确定。还有一件让我困惑的事情是，我想了解Postgres如何管理第二个查询中的筛选。

你们知道Postgres如何处理这两种情况吗？ 谢谢 :)

- RomWW12

Answer 1

原始查询转换为UNION是无法自动完成的。

考虑一个简化的情况：

SELECT x.a, y.b
FROM x JOIN y USING (c)
WHERE x.a = 0 OR x.b = 0;

假设它有三个结果行：

 a | b
---+---
 0 | 0
 1 | 0
 1 | 0

• If you replace this with

  SELECT x.a, y.b
  FROM x JOIN y USING (c)
  WHERE x.a = 0
  UNION
  SELECT x.a, y.b
  FROM x JOIN y USING (c)
  WHERE y.b = 0;
  

  the result will only have two rows, because UNION removes duplicates.

• If you use UNION ALL instead, the result will have four rows, because the row with the two zeros will appear twice, once from each branch of the query.

这种转换并非总是安全的。在你的情况下，可以这样做，因为你无论如何都会去重。

顺便说一下：如果使用UNION，则不再需要DISTINCT，因为重复项将被自动删除。如果删除DISTINCT，则查询将更加高效。

在第二个查询的第二个分支中，PostgreSQL 可以通过索引扫描处理OR，因为条件是在同一张表上的。这种情况下，PostgreSQL 可以执行位图索引扫描：

• 索引被扫描，并在内存中构建一个位图。对于每一行表格，如果索引扫描结果匹配，则该位图包含值 1，否则为值 0。

  该位图按表行的物理顺序排序。

• 使用另一个索引也执行相同的操作。

• 两个生成的位图通过位运算OR连接。

• 使用生成的位图从表格中获取匹配的行。

  三元组索引仅作为过滤器，可能存在误报结果，因此在表格扫描期间必须重新检查原始条件。

- Laurenz Albe

---

谢谢！关于第二个查询，我的理解是两个LIKE过滤器在表连接之前被应用，因此Postgres在连接后需要连接的行数更少，我理解正确吗？ - RomWW12

自然而然地，缩进更多的节点会在缩进较少的节点之前执行。 - Laurenz Albe

非常有帮助，谢谢！ - RomWW12

@tayfun 你的意思是它有文档记录吗？没有。 - Laurenz Albe

@kbrock LIKE 条件是使用 ~~ 运算符的条件。它们在问题的计划中位于连接之前。只有在外部连接时，将条件编写为连接条件或 WHERE 条件才会有影响。 - Laurenz Albe