Optimizing date queries in postgresql

Question

I'm having a hard time to optimizing queries on a very big table. Basically all of the them filter the set of results by the date:

SELECT FROM bigtable WHERE date >= '2015-01-01' AND date <= '2016-01-01' ORDER BY date desc;

Adding the following date index actually makes things worse:

CREATE INDEX CONCURRENTLY bigtable_date_index ON bigtable(date(date));

That is, without the index it takes about 1s to run and with it it takes about 10s to run. But with bigger ranges and filtering it is very slow even without that index.

I'm using postgresql 9.4 and I see that 9.5 has some improvements for sorting that might help?

Does BRIN indexes should help in this case?

Answer 1

For an index to be effective, it needs to index the same thing you're filtering by. In this case, you're filtering by date, but you appears to have indexed date(date), so the index can't be used.

Either filter your table using date(date):

SELECT FROM bigtable
WHERE date(date) >= '2015-01-01' AND date(date) <= '2016-01-01'
ORDER BY date(date) desc;

Or index the naked date:

CREATE INDEX CONCURRENTLY bigtable_date_index ON bigtable(date);