涉及OR-ed条件的高级索引（pgsql） [英] Advanced indexing involving OR-ed conditions (pgsql)

问题描述

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

I'm starting to get a much better grasp on PostgreSQL indexing, but I've run into an issue with the OR conditional, where I don't know how to go about optimizing my indexes for a faster query.

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

I have 6 conditionals that, when run individually, appear to have a small cost. Here's an example of the trimmed queries, including query plan calculated times.

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

(NOTE: I haven't output the actual query plans for these queries below for the sake of reducing complexity, but they all use nested loop left joins and index scans as I would expect with proper indexing. If necessary, I can include the query plans for a more meaningful response.)

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)

我的问题是我想与OR运算符一起加入这6个条件，使每个条件成为可能。我的组合查询更像是这样：

My problem is that I want to join these 6 conditions together with OR operators, making each condition a possibility. My combined query appears more like this:

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;

不幸的是，这会导致查询计划大幅增加，这似乎不再使用我的索引（相反，选择执行哈希左连接而不是嵌套循环左连接，并执行各种序列扫描超过以前使用的索引扫描）。

Unfortunately, this results in a MASSIVE increase on the query plan, which no longer seems to be using my indexes (instead, choosing to do a hash left join rather than a nested loop left join, and performing various sequence scans over the previously used index scans).

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

关于可以改善我的最终查询的OR-ed条件，我应该知道关于索引的任何特殊内容吗？

Is there anything special I should know about indexing with regards to OR-ed conditions that would improve my final query?

UPDATE ：如果我为每个SELECT使用UNION，那么这似乎可以加快查询速度。但是，如果我将来选择，这会阻止我订购我的结果吗？以下是我通过UNION加速查询所做的工作：

UPDATE: If I use a UNION for each individual SELECT, that seems to speed up the query. However, will that prevent me from ordering my results if I choose to in the future? Here's what I did to speed up the query via 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)

推荐答案

根据条件，在逻辑上不可能使用任何索引来帮助使用 OR <复杂条件/ code>表达式。

Depending on the conditions, it may be logically impossible to use any index to help a complex condition using OR expressions.

像MySQL，PostgreSQL 8.0及其早期的状态索引文档：

Like MySQL, PostgreSQL 8.0 and earlier states in their docs on indexes:

请注意，查询或数据操作命令最多只能使用一个每张表的索引。

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

使用PostgreSQL 8.1，它有已更改。

With PostgreSQL 8.1, this has changed.

但是，如果这没有帮助，您可以使用 UNION 您尝试过的解决方案（这是MySQL用户的常用解决方案，它继续具有每个表一个索引的限制）。

However, if this doesn't help, you can use the UNION solution you tried (this is a common solution for MySQL users, which continues to have a one-index-per-table limitation).

您应该可以订购 UNION 查询的结果，但您必须使用括号来指定 ORDER BY 适用于 UNION 的结果，而不仅仅是链中的最后一个子查询。

You should be able to order the results of a UNION query, but you have to use parentheses to specify that that ORDER BY applies to the result of the UNION, not merely to the last subquery in the chain.

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

---

我希望这会有所帮助;我不是PostgreSQL优化器的专家。您可以尝试搜索邮件列表档案，或询问 IRC频道。

这篇关于涉及OR-ed条件的高级索引（pgsql）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！