postgresql - 涉及 OR 条件的高级索引 (pgsql)

Question

我开始更好地掌握 PostgreSQL 索引，但我遇到了 OR 条件的问题，我不知道如何优化我的索引以加快查询速度。

我有 6 个条件，当单独运行时，它们的成本似乎很小。下面是修剪查询的示例，包括查询计划计算时间。

(注意:为了降低复杂性，我没有在下面输出这些查询的实际查询计划，但它们都使用了嵌套循环左连接和索引扫描 正如我所期望的那样具有适当的索引。如有必要，我可以包含查询计划以获得更有意义的响应。)

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions1)
 LIMIT 10;

QUERY PLAN
-------------------------------------------------------------------------------------
Limit  (cost=0.25..46.69 rows=1 width=171) (actual time=0.031..0.031 rows=0 loops=1)

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions2)
 LIMIT 10;

QUERY PLAN
-------------------------------------------------------------------------------------
Limit  (cost=0.76..18.97 rows=1 width=171) (actual time=14.764..14.764 rows=0 loops=1)

/* snip */

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions6)
 LIMIT 10;

QUERY PLAN
-------------------------------------------------------------------------------------
Limit  (cost=0.51..24.48 rows=1 width=171) (actual time=0.252..5.332 rows=10 loops=1)

我的问题是我想将这 6 个条件与 OR 运算符连接在一起，使每个条件都成为可能。我的组合查询看起来更像这样:

EXPLAIN ANALYZE SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions1 OR conditions2 OR conditions3 OR conditions4 OR conditions5 OR conditions 6)
 LIMIT 10;

不幸的是，这导致查询计划大幅增加，似乎不再使用我的索引(相反，选择执行 hash left join 而不是 nested loop left join，并对之前使用的索引扫描执行各种序列扫描。

Limit  (cost=142.62..510755.78 rows=1 width=171) (actual time=30.591..30.986 rows=10 loops=1)

关于 OR-ed 条件的索引，有什么我应该知道的可以改进我的最终查询的特别之处吗？

更新:如果我对每个单独的 SELECT 使用 UNION，这似乎可以加快查询速度。但是，如果我将来选择这样做，这会阻止我订购我的结果吗？这是我通过 UNION 加速查询所做的:

EXPLAIN ANALYZE
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions1)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions2)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions3)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions4)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions5)
UNION
SELECT t1.*, t2.*, t3.*
  FROM t1 LEFT JOIN t2 on t2.id = t1.t2_id LEFT JOIN t3 ON t3.id = t1.t3_id
 WHERE (conditions6)
 LIMIT 10;

QUERY PLAN
-------------------------------------------------------------------------------------
Limit  (cost=219.14..219.49 rows=6 width=171) (actual time=125.579..125.653 rows=10 loops=1)

Answer 1

根据条件，逻辑上可能无法使用任何索引来帮助使用 OR 表达式的复杂条件。

与 MySQL、PostgreSQL 8.0 和更早版本一样，它们的 docs on indexes :

Note that a query or data manipulation command can use at most one index per table.

对于 PostgreSQL 8.1，这有 changed .

但是，如果这没有帮助，您可以使用您尝试过的 UNION 解决方案(这是 MySQL 用户的常见解决方案，它仍然存在每个表一个索引的限制).

您应该能够对 UNION 查询的结果进行排序，但是您必须使用括号来指定 ORDER BY 应用于 的结果>UNION，不仅仅是链中的最后一个子查询。

(SELECT ... )
UNION
(SELECT ... )
UNION
(SELECT ... )
ORDER BY columnname;

---

希望对您有所帮助；我不是 PostgreSQL 优化器方面的专家。您可以尝试搜索 mailing list archives , 或在 IRC channel 上询问.