为什么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;

我使用pg_trgm在op.alternate_localized_names、lower(sldt.unaccent_title)和lower(sldt.unaccent_description)上放置了3个trigram索引。

但是，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子句中在两个单独的表上设置条件会导致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

• 如果你用 选择x.a，y.b从x连接y使用(c)其中x.a =0联合选择x.a，y.b从UNION y使用(c)其中y.b = 0； 结果将只有两行，因为UNION删除重复项。
• 如果使用UNION ALL，结果将有四行，因为带有两个零的行将出现两次，每次从查询的每个分支出现一次。

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

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

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

• 索引被扫描，PostgreSQL在内存中构建一个位图，该位图包含索引扫描导致匹配的每一行1，否则为0。 此位图按表行的物理顺序排列。
• 对于其他索引的其他条件，也会发生同样的情况。
• 生成的位图与一个按位排列的OR操作相结合。
• 生成的位图用于从表中获取匹配的行。 trigram索引只是一个可能有假阳性结果的过滤器，因此在表扫描过程中必须重新检查原始条件。