From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Tobias Brox <tobias(at)nordicbet(dot)com> |
Cc: | Peter Childs <peterachilds(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Planner statistics, correlations |
Date: | 2007-01-12 09:17:48 |
Message-ID: | 45A7523C.3000007@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Tobias Brox wrote:
> [Peter Childs - Fri at 08:56:54AM +0000]
>> 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 * from events where state in (1,2,3) and event_time<now()
>
> also estimates almost 5k of rows.
Try a partial index:
CREATE INDEX my_new_index ON events (event_time)
WHERE state in (1,2,3);
Now, if that doesn't work you might want to split the query into two...
SELECT * FROM events
WHERE state IN (1,2,3) AND event_time < '2007-01-01'::date
UNION ALL
SELECT * FROM events
WHERE state IN (1,2,3) AND event_time >= '2007-01-01'::date AND
event_time < now();
CREATE INDEX my_new_index ON events (event_time)
WHERE state in (1,2,3) AND event_time < '2007-01-01'::date;
CREATE INDEX event_time_state_idx ON events (event_time, state);
You'll want to replace the index/update the query once a year/month etc.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Tobias Brox | 2007-01-12 09:56:55 | Re: Planner statistics, correlations |
Previous Message | Richard Huxton | 2007-01-12 09:06:40 | Re: [HACKERS] unusual performance for vac following 8.2upgrade |