Recommended way to index a date field in postgres?

Question

I have a few tables with about 17M rows that all have a date column I would like to be able to utilize frequently for searches. I am considering either just throwing an index on the column and see how things go or sorting the items by date as a one time operation and then inserting everything into a new table so that the primary key ascends as the date ascends.

Since these are both pretty time consuming I thought it might be worth it to ask here first for input.

The end goal is for me to load sql queries into pandas for some analysis if that is relevant here.

Answer 1

The index on a date column makes sense when you are going to search the table for a given date(s), e.g.:

select * from test
where the_date = '2016-01-01';
-- or

select * from test
where the_date between '2016-01-01' and '2016-01-31';
-- etc

In these queries there is no matter whether the sort order of primary key and the date column are the same or not. Hence rewriting the data to the new table will be useless. Just create an index.

However, if you are going to use the index only in ORDER BY:

select * from test
order by the_date;

then a primary key integer index may be significantly (2-4 times) faster then an index on a date column.