Create index on computed value
I have a table with sales
records year
like this:
id year sales
1 2001 10
2 2002 20
3 2003 30
I am joining the table for myself to get sales_difference
from one year to the next:
SELECT s1.*, s1.sales - s2.sales AS sales_difference
FROM sales s1, sales s2
WHERE s1.year = s2.year + 1
This query is quite slow, so I want to create an index on year + 1
. According to the PostgreSQL docs, you can create indexes for expressions like:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
So I try to do this:
CREATE INDEX sales_year_plus_one on sales (year + 1);
which gives me:
ERROR: syntax error at or near "+"
LINE 1: ...sales_year_plus_one on sales (year + 1);
^
Why is this particular expression not allowed?
source to share
You need to enclose your expression in an extra set of parentheses:
CREATE INDEX sales_year_plus_one on sales ((year + 1));
See this excerpt from the documentation:
The syntax for the CREATE INDEX command usually requires 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.
source to share
You can also use the window function to get the same effect without an additional index (and hence the overhead of maintaining that index):
SELECT *, sales - lag(sales) OVER (ORDER BY year) AS sales_difference
FROM sales;
A simple index on year
, which you probably already have, will suffice.
source to share