我开始更好地理解PostgreSQL索引,但我遇到了OR条件的一个问题,在这个问题上,我不知道如何优化索引以获得更快的查询。
我有6个条件,当单独运行,似乎有一个小的成本。下面是一个经过修剪的查询的示例,包括查询计划计算的时间。
(注意:为了降低复杂性,我没有输出下面这些查询的实际查询计划,但它们都使用nested loop left joins和index scans,这与我所期望的适当索引一样。如果有必要,我可以包括查询计划,以获得更有意义的响应。)
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,并在以前使用的index scans上执行各种sequence scans )。
Limit (cost=142.62..510755.78 rows=1 width=171) (actual time=30.591..30.986 rows=10 loops=1)有什么特别的,我应该知道的索引与或-ed条件,以改善我的最终查询?
UPDATE:如果我对每个单独的选择使用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)发布于 2009-11-05 01:54:28
根据不同的条件,在逻辑上不可能使用任何索引来使用OR表达式来帮助复杂的条件。
像MySQL,PostgreSQL 8.0和更早的状态在他们的索引文档
请注意,查询或数据操作命令每个表最多只能使用一个索引。
有了PostgreSQL 8.1,就有了变化。
但是,如果这没有帮助,那么您可以使用您尝试过的UNION解决方案(这是MySQL用户的一个常见解决方案,它仍然具有每个表的一个索引限制)。
您应该能够排序UNION查询的结果,但是必须使用括号来指定ORDER BY应用于UNION的结果,而不仅仅是链中的最后一个子查询。
(SELECT ... )
UNION
(SELECT ... )
UNION
(SELECT ... )
ORDER BY columnname;发布于 2009-11-06 15:39:23
(对不起-不知道如何回复回复,所以这是最高层)
为了澄清- PG过去只对单个表使用单一索引-扫描。如果您有一个连接三个表的查询,并且每个表都有一个有用的索引,那么它总是足够聪明地使用这三个表。
在您的特殊情况下,可能发生的是您的ORed条件之间有某种联系。PostgreSQL不知道这一点,因此最终假设它匹配的行比实际多。有足够的行来更改查询计划。
而且,您的UNIONed查询与单个查询并不完全相同,因为您单独地限制了每个小查询,而不是使用UNION对整个结果集进行限制。
您应该能够对UNION查询的结果进行排序,但是您必须使用括号来指定该order应用于UNION的结果,而不仅仅是应用于链中的最后一个子查询。
这是不对的--顺序适用于整个结果。
HTH
https://stackoverflow.com/questions/1677538
复制相似问题