FROM 子句中表的排列/顺序对提高性能有什么影响吗？
一般来说，FROM 子句中表的排列或顺序对提高查询性能有什么影响吗？我所说的排列是指最小的桌子和最大的桌子。
任何不同的经验/想法/意见/因素也将受到赞赏。
就我而言，我们使用 PostgreSQL v8.2.3。
In general, does arrangement or order of tables in the FROM clause make any difference in improving the performance of the query? By arrangement I mean smallest table and largest table.
Any different experience/ideas/opinions/factors are also appreciated.
In my case, we're using PostgreSQL v8.2.3.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
对于内部连接，它不应该有任何区别 - 优化器将生成以尽可能多的不同顺序执行连接的计划（最多from子句中的geqo_threshold表）。
外连接不是对称的，因此语句中的顺序很重要（尽管实际的执行顺序仍然由服务器决定）。
For inner joins, it should not make any difference- the optimiser will generate plans doing the joins in as many different orders as is possible (up to geqo_threshold tables in the from clause).
Outer joins are not symmetric, so there the ordering in the statement is significant (although the actual execution order is still decided by the server).
您是否使用类似“SELECT FROM table1,table2”之类的内容？
这些表是“隐式”交叉连接的，所以我认为这并不重要。
我要做的第一件事是使用 EXPLAIN 测试两个可能的查询，看看是否有任何区别？
Are you using something like "SELECT FROM table1,table2" ?
The tables are "implicitly" cross-joined, so I don't think it matters.
The first thing I would do is test two possible queries using EXPLAIN, and see if there is any difference?
（通用 SQL 建议，而不是 Postgre 特定的）
它不应该有什么区别 - 优化器应该根据可用的信息构建一个计划，并且 SQL 通常被设计为优化器可以自由地重新排序连接、条件检查等，前提是结果相同。
特定查询可能会导致特定优化器（对于特定数据库、服务器状态、产品版本等）出现问题，但这种情况应该很少遇到。
(Generic SQL advice, rather than Postgre specific)
It shouldn't make a difference - the optimizer should be building a plan based on the information it has available to it, and SQL has generally been designed so that the optimizer is free to reorder joins, condition checks, etc, provided that the result is the same.
It may be that a particular query causes problems for a particular optimizer (for a certain DB, server state, version of product, etc), but it should be rare to encounter such a situation.
如果你写成
FROM foo, bar, baz那么不，这没有什么区别。如果您编写FROM foo JOIN bar ... JOIN baz ...（内部联接），那么如果您少于join_collapse_limit元素；否则，连接顺序是固定的，您可以使用它来手动优化此类内容（但这很少需要）。对于外连接，连接顺序在编写时是固定的，因为这会影响结果。If you write
FROM foo, bar, bazthen no, it doesn't make a difference. If you writeFROM foo JOIN bar ... JOIN baz ...(inner joins), then it doesn't make a difference if you have less thanjoin_collapse_limitelements; otherwise the join order is fixed, which you can use to manually optimize such things (but that is rarely needed). For outer joins, the join order is fixed as it is written, because that affects the results.