Postgresql – Retrieving users by their age efficiently

datepostgresql

I have a PostgreSQL database of users and their birthdays and want to get all users in a specific age range:

SELECT * FROM users
WHERE age(birthday) >= 21 AND age(birthday) <= 30; 

How can I achieve that this query stays efficient, even when there are millions of users in the database?
Of course there are more filters, I just don't want to calculate the age for every row.

I do not want to use a materialized view.

Best Answer

You want to move the computation from the column, to the other side of the inequality operators where you have literals rather than a column:

WHERE birthday >= current_date - interval '30 years' AND birthday <= current_date - interval '21 years';

Then a simple index on "birthday" will make it efficient. It might need some fiddling depending on what datatype "birthday" is. I tested it as a timestamp without time zone.