sql - 性能差异 : condition placed at INNER JOIN vs WHERE clause

Question

假设我有一张 table order作为

id | clientid | type | amount | itemid | date
---|----------|------|--------|--------|-----------
23 | 258      | B    | 150    | 14     | 2012-04-03
24 | 258      | S    | 69     | 14     | 2012-04-03
25 | 301      | S    | 10     | 20     | 2012-04-03
26 | 327      | B    | 54     | 156    | 2012-04-04

• clientid是返回 client 的外键表
• itemid是返回 item 的外键表
• type只有B或 S
• amount是一个整数

和一张 table processed作为

id | orderid | processed | date
---|---------|-----------|---------
41 | 23      | true      | 2012-04-03
42 | 24      | true      | 2012-04-03
43 | 25      | false     | <NULL>
44 | 26      | true      | 2012-04-05     

我需要从 order 中获取所有行对于相同的 clientid同样date有反对type值。牢记type只能有两个值之一 - B或 S .在上面的示例中，这将是行 23和 24 .

另一个约束是processed中的对应行必须是 true对于 orderid .

到目前为止我的查询

SELECT c1.clientid,
       c1.date,
       c1.type,
       c1.itemid,
       c1.amount,
       c2.date,
       c2.type,
       c2.itemid,
       c2.amount

FROM   order c1
INNER JOIN order c2 ON c1.itemid    =  c2.itemid AND
                       c1.date      =  c2.date   AND
                       c1.clientid  =  c2.clientid AND
                       c1.type     <>  c2.type AND
                       c1.id        <  c2.id

INNER JOIN processed p1 ON p1.orderid   =  c1.id AND
                         p1.processed =  true
INNER JOIN processed p2 ON p2.orderid   =  c2.id AND
                         p2.processed =  true

问题保持processed = true作为连接子句的一部分会减慢查询速度。如果我将它移动到 WHERE 子句，那么性能会好得多。这激起了我的兴趣，我想知道为什么。

主键和相应的外键列被索引，而值列(value、processed 等)没有。

免责声明:我继承了这个DB结构，性能差异大约是6秒。

Answer 1

您看到差异的原因是计划者将执行计划放在一起，这显然因查询而异(可以说，它应该将 2 个查询优化为相同，这可能成为一个错误)。这意味着规划器认为它必须以特定的方式工作才能在每个语句中得到结果。

当您在 JOIN 中执行此操作时，规划器可能必须从表中进行选择，按“True”部分进行过滤，然后连接结果集。我想这是一个大表，因此需要查看大量数据，而且它不能有效地使用索引。

我怀疑如果您在 WHERE 子句中执行此操作，规划器会选择更有效的路径(即基于索引或预过滤的数据集)。

您可以通过在两列上添加索引(不确定 Postgres 是否支持包含的列和多列索引)来使连接工作得一样快(如果不是更快的话)。

简而言之，规划器的问题在于它选择了 2 条不同的路径来获取结果集，其中一条不如另一条高效。没有全表信息和EXPLAIN ANALYZE信息，我们不可能知道原因是什么。

如果您想详细了解特定查询执行此操作的原因，则需要提供更多信息。然而原因是规划者选择了不同的路线。

额外阅读 Material :

http://www.postgresql.org/docs/current/static/explicit-joins.html

只是略读了一下，似乎 postgres 规划器没有重新排序连接来优化它。尝试更改语句中连接的顺序，看看是否能获得相同的性能……只是一个想法。