我开始更好地掌握 PostgreSQL 索引,但我遇到了 OR 条件的问题,我不知道如何优化索引以实现更快的查询。
我有 6 个条件,当单独运行时,它们的成本似乎很小。以下是修剪查询的示例,包括查询计划计算时间。
(注意:为了降低复杂性,我没有输出下面这些查询的实际查询计划,但它们都使用nested loop left joins
and 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)
我的问题是我想用 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 OR conditions2 OR conditions3 OR conditions4 OR conditions5 OR conditions 6)
LIMIT 10;
不幸的是,这导致查询计划大量增加,该计划似乎不再使用我的索引(相反,选择执行hash left join
而不是一个nested loop left join
,并进行各种sequence scans
超过之前使用的index scans
).
Limit (cost=142.62..510755.78 rows=1 width=171) (actual time=30.591..30.986 rows=10 loops=1)
关于 OR 条件的索引,我应该知道什么特别的事情可以改善我的最终查询?
UPDATE:如果我对每个单独的 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)