Re: Planner statistics, correlations

[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.