I've got a table of historical events that 10 million rows over 18+ months. Currently there is an index for the event timestamp. I'm wondering if someone can respond to my curiosity regarding the performance of indexes (BTree).
Would creating an index on the timestamp truncated to the *day* make the index more efficient for queries which are interested in events falling in a range of 7+ days?
How much would it hurt when the planner needs to tease out rows which fall within the day range but not the real time range?
Would having both indexes allow the planner to index by the day for entirely-included days and by the actual time for the boundary days?
The performance of the index isn't terrible. It can take up to cost 15,000 (which I assume is 15 seconds) for a query looking at 5 weeks of data. I'd like to improve it if it's simple to do, and changing the index to be on the truncated time seems like it could possibly be useful.