We have a table with a timestamp attribute (event_time) and a state flag
which usually changes value around the event_time (it goes to 4). Now
we have more than two years of events in the database, and around 5k of
future events.
It is important to frequently pick out overdue events, say:
On 12/01/07, Tobias Brox [EMAIL PROTECTED] wrote:
We have a table with a timestamp attribute (event_time) and a state flag
which usually changes value around the event_time (it goes to 4). Now
we have more than two years of events in the database, and around 5k of
future events.
It is
[Peter Childs - Fri at 08:56:54AM +]
Can you say what state might be rather than what it is not. I'm guess
that state is an int but there is only a limited list of possible
states, if you can say what it might be rather than what it is the
index is more liklly to be used.
explain select
Tobias Brox wrote:
[Peter Childs - Fri at 08:56:54AM +]
Can you say what state might be rather than what it is not. I'm guess
that state is an int but there is only a limited list of possible
states, if you can say what it might be rather than what it is the
index is more liklly to be used.
[Richard Huxton - Fri at 09:17:48AM +]
Try a partial index:
CREATE INDEX my_new_index ON events (event_time)
WHERE state in (1,2,3);
I have that, the index is used and the query is lightning fast - the
only problem is that the planner is using the wrong estimates. This
becomes a real
Tobias Brox wrote:
Maybe it would help to partitionate the table every year?
I thought about partitioning the table by state, putting rows with
state=4 into one partition, and all others to another partition.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
[Heikki Linnakangas - Fri at 10:41:34AM +]
I thought about partitioning the table by state, putting rows with
state=4 into one partition, and all others to another partition.
That sounds like a good idea - but wouldn't that be costly when changing state?
---(end of
Tobias Brox wrote:
[Heikki Linnakangas - Fri at 10:41:34AM +]
I thought about partitioning the table by state, putting rows with
state=4 into one partition, and all others to another partition.
That sounds like a good idea - but wouldn't that be costly when changing state?
In
