Speeding up index scans by truncating timestamp?

From: Derrick Rice <derrick(dot)rice(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Speeding up index scans by truncating timestamp?
Date: 2011-02-15 00:13:51
Message-ID: AANLkTik2wvj4nWBCQVSWmpEDT--xH-pOPn-Ma-zorOFC@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hey folks,

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.

Derrick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jasen Betts 2011-02-15 00:47:29 Re: How to create index on only some of the rows
Previous Message Tom Lane 2011-02-15 00:04:33 Re: Alter Default Privileges Does Not Work For Functions