postgresql - 为什么 Postgres 不在 2 个单独的表上使用带 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;

我使用 pg_trgm 在 op.alternate_localized_names、lower(sldt.unaccent_title) 和 lower(sldt .unaccent_description).

但是，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

但是，当我将查询拆分为 2 个时，一个在 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 子句中对 2 个单独的表设置条件会导致 Postgres 首先连接表，然后过滤它们。但我不确定这一点。令我困惑的第二件事是，我想了解 Postgres 如何管理第二个查询中的过滤。

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

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

• 如果您将其替换为

  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;
  

  结果将只有两行，因为 UNION 会删除重复项。

• 如果您改用 UNION ALL，结果将有 四 行，因为带有两个零的行将出现两次，一次来自查询。

所以这种转变并不总是安全的。在您的情况下，您可以摆脱它，因为无论如何您都删除了重复项。

顺便说一句:如果你使用UNION，你就不再需要DISTINCT了，因为重复项无论如何都会被删除。如果您删除 DISTINCT，您的查询将变得更便宜。

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

• 索引被扫描，PostgreSQL 在内存中构建一个位图，其中索引扫描结果匹配的每个表行包含 1，否则为 0。

  此位图按照表格行的物理顺序排列。

• 对于另一个索引的另一个条件，同样的事情会发生。

• 生成的位图通过按位的OR 运算连接起来。

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

  三元组索引只是一个过滤器，可能会产生误报结果，因此必须在该表扫描期间重新检查原始条件。