From: | "Peter Childs" <peterachilds(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Planner statistics, correlations |
Date: | 2007-01-12 08:56:54 |
Message-ID: | a2de01dd0701120056p37f1906excd71068468e5769a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 12/01/07, Tobias Brox <tobias(at)nordicbet(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tobias Brox | 2007-01-12 09:05:00 | Re: Planner statistics, correlations |
Previous Message | Tobias Brox | 2007-01-12 08:16:31 | Planner statistics, correlations |