sql - 根据计算值创建索引

Question

我有a table 年的销售额记录如下:

id year sales
1  2001 10
2  2002 20
3  2003 30

我正在将表自身连接起来，以便获得从一年到下一年的 sales_difference:

SELECT s1.*, s1.sales - s2.sales AS sales_difference
FROM sales s1, sales s2
WHERE s1.year = s2.year + 1

这个查询运行得相当慢，所以我想在 year + 1 上创建一个索引。根据the PostgreSQL docs您可以在表达式上创建索引，例如:

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

所以我尝试这样做:

CREATE INDEX sales_year_plus_one on sales (year + 1);

这给了我:

ERROR:  syntax error at or near "+"
LINE 1: ...sales_year_plus_one on sales (year + 1);
                                              ^

为什么不允许使用这个特定的表达式？

Answer 1

您需要将表达式括在一组额外的括号中:

CREATE INDEX sales_year_plus_one on sales ((year + 1));

请参阅文档摘录:

The syntax of the CREATE INDEX command normally requires writing parentheses around index expressions, as shown in the second example. The parentheses can be omitted when the expression is just a function call, as in the first example.