性能差异：条件置于INNER JOIN与WHERE子句之间

Question

说我有一个表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 是一个整数

和一张桌子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 = truejoin子句保留为as会减慢查询速度。如果将其移至WHERE子句，则性能会更好。这激起了我的兴趣，
我想知道为什么 。

主键和相应的外键列被索引，而值列（value，processed等等）未被索引。

免责声明：我继承了此数据库结构，性能差异大约为6秒。

Answer 1

您之所以会看到差异，是因为计划者将执行计划放在一起，这显然取决于查询（可以说，应该将两个查询优化为相同，这可能是一个错误）。
）。这意味着计划者认为必须以特定的方式工作才能获得每个语句中的结果。

当您在JOIN中执行此操作时，计划者可能必须从表中进行选择，通过“
True”部分进行过滤，然后加入结果集。我可以想象这是一个大表，因此需要查看大量数据，并且不能高效地使用索引。

我怀疑如果您在WHERE子句中执行此操作，那么计划者将选择一条效率更高的路由（即，基于索引的数据集或预先过滤的数据集）。

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

简而言之，计划者面临的问题是正在选择2条不同的路线来获得结果集，其中一条路线的效率不如另一条路线。没有完整的表格信息和EXPLAIN
ANALYZE信息，对于我们来说，原因是不可能的。

如果您想知道为什么特定查询执行此操作的细节，则需要提供更多信息。但是，原因是计划者选择了不同的路线。

其他阅读材料：

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

只是略读了一下，似乎postgres规划器并未重新排序联接以对其进行优化。尝试更改语句中的联接顺序，以查看是否获得了相同的性能……只是一个想法。