索引跨越PostgreSQL中的多个表 [英] Index spanning multiple tables in PostgreSQL

问题描述

在PostgreSQL中是否可以在包含多个表的字段的表达式上放置索引。因此，例如，一个索引可以加快以下形式的查询：

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:

SELECT *, (table1.x + table2.x) AS z
FROM table1
INNER JOIN table2
ON table1.id = table2.id
ORDER BY z ASC

推荐答案

不，不可能在许多表上都有索引，而且因为您赢了，所以它确实不能保证加速任何操作并非总是会得到仅索引扫描。您真正想要的是一个实例化视图，但是pg也没有。您可以尝试使用触发器像这样 或。

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 view but pg doesn't have those either. You can try implementing it yourself using triggers like this or this.

如@petter所述。物化视图在9.3版中引入。

As noted by @petter. The materialized views were introduced in 9.3.

这篇关于索引跨越PostgreSQL中的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！