Re: Planner statistics, correlations

Поиск
Список
Период
Сортировка
От Tobias Brox
Тема Re: Planner statistics, correlations
Дата
Msg-id 20070112095655.GA5128@oppetid.no
обсуждение исходный текст
Ответ на Re: Planner statistics, correlations  (Richard Huxton <dev@archonet.com>)
Список pgsql-performance
[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.


В списке pgsql-performance по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: Planner statistics, correlations
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: Planner statistics, correlations