Sometimes we want to find all events that happened in a given year. For example, a count of the sales that happened in the year 2000. It’s frequent to run a correct query that turns out to be slow. Let’s see how to do it properly.
The proper ways
Using one of these way to filter rows based on a year is correct.
A search by year is actually a range search, from the first day of that year to the last:
SELECT count(*) FROM purchase WHERE timestamp => '2000-01-01' AND timestamp < '2001-01-01';
If an index starts with the
timestamp column, this query will take advantage of it properly. But range searches can’t always take advantage of indexes, or can only do it in part.
If the range search is slow in your case, maybe you can turn it into a point search. To do this, we need to build an expressional index on it.
To define an expressional index in PostgreSQL:
CREATE INDEX idx_year ON purchase (year(timestamp), id);
This index will contain the result of
year(timestamp) for each row. At this point, we can write this query:
SELECT count(*) FROM purchase WHERE year(timestamp) = 2000 ORDER BY id;
Internally, this condition will be used:
WHERE idx_year = 2000
This is a point search, and won’t prevent the use of the second part of the index (id in this case) for the
The unoptimal way
The intuitive way to obtain the result is to use the
year() function to get a year from the timestamp.
year() function for filtering rows will prevent the use of an index, if we don’t have an expressional index or an indexed virtual column.
This applies any DBMS, and to any function, except for aggregate functions and window functions. The reasons are:
- DBMSs index columns, not functions or other expressions.
- Even if the index is usable in some cases, the component that decides queries execution strategies doesn’t know anything about functions.
Some DBMSs don’t support expressional indexes, but they allow to create a virtual column / generated field and build an index on it. In a future article we will show how to do this.
Some DBMSs don’t support the
year() function, but they all support some function that can be used to extract an year from a date or timestamp. Check your product documentation if the
year() function is not supported.