PostgreSQL 中跨多个表的索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16402225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Index spanning multiple tables in PostgreSQL
提问by Ewout Kleinsmann
Is it possible in PostgreSQL to place an index on an expression containing fields of multiple tables. So for example an index to speed up an query of the following form:
在 PostgreSQL 中是否可以在包含多个表的字段的表达式上放置索引。例如，用于加速以下形式的查询的索引：
SELECT *, (table1.x + table2.x) AS z
FROM table1
INNER JOIN table2
ON table1.id = table2.id
ORDER BY z ASC
采纳答案by Jakub Kania
No it's not possible to have an index on many tables, also it really wouldn't guarantee speeding up anything since you won't always get an Index Only Scan. What you really want is a materialized viewbut pg doesn't have those either. You can try implementing it yourself using triggers like thisor this.
不，不可能在许多表上都有索引，而且它真的不能保证加速任何事情，因为您不会总是获得仅索引扫描。您真正想要的是物化视图，但 pg 也没有。您可以尝试使用像这样或这样的触发器自己实现它。
Update
更新
As noted by @petter. The materialized views were introduced in 9.3.
正如@petter 所指出的。物化视图是在 9.3 中引入的。
回答by Mike Sherrill 'Cat Recall'
No, that's not possible in anycurrently shipping SQL dbms. Oracle supports bitmap join indexes, but that might not be relevant. It's not clear to me whether you want an index on only the join columns of multiple tables, or whether you want an index on arbitrary columns of joined tables.
不，这在任何当前发布的 SQL dbms中都是不可能的。Oracle 支持位图连接索引，但这可能无关紧要。我不清楚您是否只想要多个表的连接列上的索引，或者您是否想要连接表的任意列上的索引。
To determine the realsource of performance problems, learn to read the output of PostgreSQL's EXPLAIN ANALYZE.
要确定性能问题的真正来源，请学习阅读 PostgreSQL 的EXPLAIN ANALYZE的输出。
