DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=tKrPU/lS2ox03G8JY/57y0zYDyvcIgmTcOUHmR6TX3xZdavckSHgfvbGBofLo4nO669/F43guI4MGRwyGVMiJ2xNB1DrCJTTbi9f5I98CR0s2+qH+6nTqnGWeEV4rS0rC6dJQ6lFJ7PrLDdJkrwBjYrYSXew/YsOn7I7nusCnd8=
Message-ID: <a2de01dd0701120056p37f1906excd71068468e5769a@mail.gmail.com>
Date: Fri, 12 Jan 2007 08:56:54 +0000
From: "Peter Childs" <peterachilds@gmail.com>
Subject: Re: Planner statistics, correlations
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20070112081631.GA10636@oppetid.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20070112081631.GA10636@oppetid.no>

On 12/01/07, Tobias Brox <tobias@nordicbet.com> 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 important to frequently pick out "overdue events", say:
>
>   select * from events where state<>4 and event_time<now()
>
> This query would usually yield between 0 and 100 rows - however, the
> planner doesn't see the correlation betewen state and event_time - since
> most of the events have event_time<now, the planner also assumes most of
> the events with state<>4 has event_time<now, so the expected number of
> rows is closer to 5k.  This matters, because I have a query with joins,
> and I would really benefit from nested loops.
>
> (I've tried replacing "now()" above with different timestamps from the
> future and the past.  I'm using pg 8.2)
>
> Any suggestions?
>

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.

Peter.