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
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 |