首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >涉及OR-ed条件的高级索引(pgsql)

问涉及OR-ed条件的高级索引(pgsql)
EN

Stack Overflow用户
提问于 2009-11-05 00:05:01
回答 2查看 2.2K关注 0票数 4

我开始更好地理解PostgreSQL索引,但我遇到了OR条件的一个问题,在这个问题上,我不知道如何优化索引以获得更快的查询。

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

(注意:为了降低复杂性,我没有输出下面这些查询的实际查询计划,但它们都使用nested loop left joins和index scans,这与我所期望的适当索引一样。如果有必要,我可以包括查询计划,以获得更有意义的响应。)

代码语言:javascript
复制
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操作符结合起来,使每个条件都成为可能。我的合并查询看起来更像这样:

代码语言:javascript
复制
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,并在以前使用的index scans上执行各种sequence scans )。

代码语言:javascript
复制
Limit  (cost=142.62..510755.78 rows=1 width=171) (actual time=30.591..30.986 rows=10 loops=1)

有什么特别的,我应该知道的索引与或-ed条件,以改善我的最终查询?

UPDATE:如果我对每个单独的选择使用UNION,这似乎会加快查询速度。然而,如果我在未来选择的话,这会阻止我命令我的结果吗?下面是我通过UNION加快查询所做的工作:

代码语言:javascript
复制
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)
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2009-11-05 01:54:28

根据不同的条件,在逻辑上不可能使用任何索引来使用OR表达式来帮助复杂的条件。

像MySQL,PostgreSQL 8.0和更早的状态在他们的索引文档

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

有了PostgreSQL 8.1,就有了变化。

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

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

代码语言:javascript
复制
(SELECT ... )
UNION
(SELECT ... )
UNION
(SELECT ... )
ORDER BY columnname;

我希望这会有所帮助;我不是PostgreSQL优化器方面的专家。您可以尝试搜索邮寄名单档案,或者询问IRC信道。

票数 5
EN

Stack Overflow用户

发布于 2009-11-06 15:39:23

(对不起-不知道如何回复回复,所以这是最高层)

为了澄清- PG过去只对单个表使用单一索引-扫描。如果您有一个连接三个表的查询,并且每个表都有一个有用的索引,那么它总是足够聪明地使用这三个表。

在您的特殊情况下,可能发生的是您的ORed条件之间有某种联系。PostgreSQL不知道这一点,因此最终假设它匹配的行比实际多。有足够的行来更改查询计划。

而且,您的UNIONed查询与单个查询并不完全相同,因为您单独地限制了每个小查询,而不是使用UNION对整个结果集进行限制。

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

这是不对的--顺序适用于整个结果。

HTH

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/1677538

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档