sql - PostgreSQL - gin 索引来自许多表和 OR 条件的字段

Question

这是我非常简化的业务需求(使用 PostgreSQL 10.1):

有两个表 order 和 customer ，它们是使用 join 从 db 中一起检索的。两个表都包含必须使用 ILIKE 关键字进行过滤的字段。然后我有两个 gin 索引来提高查询性能:

CREATE INDEX order_type ON orderd USING gin (type gin_trgm_ops);
CREATE INDEX customer_name ON customer USING gin (name gin_trgm_ops);

现在我希望我的查询看起来像这样:

SELECT * FROM orderd o JOIN customer c ON c.id=o.customer_id
WHERE o.type ILIKE '%user_query%' OR c.name ILIKE '%user_query%';

我希望它使用索引，但 EXPLAIN ANALYZE 显示了一些不同的东西。这些是我经过多次试验后的观察:

1. 如果我尝试使用单一条件，没有 OR，它会起作用(使用索引)
2. 如果我用 AND 替换 OR - 它有效
3. 如果我在同一张表上有两个索引，它会起作用(对于 OR 和 AND)

但如果我想使用来自不同表的索引和 OR 条件，它似乎无法工作。这是为什么？有什么办法让它发挥作用吗？

我的实际业务场景要复杂得多，多了两个表，多了两个字段，还有其他过滤器，还有查询中计算的一些统计信息，所以我不得不在一个查询中实现所有的过滤。我有什么想法可以做到这一点吗？

Answer 1

你可以尝试使用UNION/UNION ALL:

SELECT *
FROM orderd o 
JOIN customer c
  ON c.id=o.customer_id
WHERE o.type ILIKE '%user_query%'
UNION ALL
SELECT *
FROM orderd o 
JOIN customer c
  ON c.id=o.customer_id
WHERE c.name ILIKE '%user_query%';

我假设 OR EXPANSION将以类似于 Oracle 的方式工作。

为了避免重复(在某种程度上)，您可以使用 CTE:

WITH cte AS (
    SELECT *
    FROM orderd o 
    JOIN customer c
      ON c.id=o.customer_id
)
SELECT *
FROM cte
WHERE type ILIKE '%user_query%'   --predicate pushdown should do the job
UNION ALL
SELECT *
FROM cte
WHERE name name ILIKE '%user_query%';