Date: Fri, 12 Jan 2007 10:56:55 +0100
From: Tobias Brox <tobias@nordicbet.com>
To: Richard Huxton <dev@archonet.com>
Cc: Tobias Brox <tobias@nordicbet.com>, Peter Childs <peterachilds@gmail.com>,
	pgsql-performance@postgresql.org
Subject: Re: Planner statistics, correlations
Message-ID: <20070112095655.GA5128@oppetid.no>
References: <20070112081631.GA10636@oppetid.no>
	<a2de01dd0701120056p37f1906excd71068468e5769a@mail.gmail.com>
	<20070112090500.GB16243@oppetid.no> <45A7523C.3000007@archonet.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <45A7523C.3000007@archonet.com>
Organization: Group Nordicbet
User-Agent: Mutt/1.5.9i

[Richard Huxton - Fri at 09:17:48AM +0000]
> 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 problem when doing joins and more complex queries.

> Now, if that doesn't work you might want to split the query into two...

Hm, that's an idea - to use a two-pass query ... first:

  select max(event_time) from events where state in (1,2,3);

and then use the result:

  select * from events 
  where event_time>? and event_time<now() and state in (1,2,3)

This would allow the planner to get the estimates in the right ballpark
(given that the events won't stay for too long in the lower states), and
it would in any case not be significantly slower than the straight-ahead
approach - but quite inelegant.