Message-ID: <45A7523C.3000007@archonet.com>
Date: Fri, 12 Jan 2007 09:17:48 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: Tobias Brox <tobias@nordicbet.com>
Cc: Peter Childs <peterachilds@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Planner statistics, correlations
References: <20070112081631.GA10636@oppetid.no>
	<a2de01dd0701120056p37f1906excd71068468e5769a@mail.gmail.com>
	<20070112090500.GB16243@oppetid.no>
In-Reply-To: <20070112090500.GB16243@oppetid.no>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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