От: Tobias Brox
Тема: Re: timestamp indexing
Дата: ,
Msg-id: 20050531020625.GA26100@tobias.exoweb.net
(см: обсуждение, исходный текст)
Ответ на: Re: timestamp indexing  (Tom Lane)
Ответы: Re: timestamp indexing  (Tobias Brox)
Список: pgsql-performance

Скрыть дерево обсуждения

timestamp indexing  (Tobias Brox, )
 Re: timestamp indexing  (Michael Fuhr, )
  Re: timestamp indexing  (Tobias Brox, )
   Re: timestamp indexing  (Tom Lane, )
    Re: timestamp indexing  (Tobias Brox, )
     Re: timestamp indexing  (Tobias Brox, )
      Re: timestamp indexing  ("Jim C. Nasby", )
       Re: timestamp indexing  (Tobias Brox, )

[Tom Lane - Mon at 01:57:54PM -0400]
> Your followup hasn't shown up here yet,

I'll check up on that and resend it.

> but if the query is written like
>     WHERE timestampcol >= now() - interval 'something'
> then the pre-8.0 planner is not capable of making a good estimate of the
> selectivity of the WHERE clause.

> One solution is to fold the timestamp
> computation to a constant on the client side.

I don't think there are any of that in the production; we always make the
timestamps on the client side.

As to my original problem, I looked up on table clustering on google.
Right, for report performance, we store some aggregates in the table which
are updated several times.  If I've understood it correctly, the row will
physically be moved to the tail of the table every time the attribute is
updated.  I understand that it may make sense to do a full table scan if a
random 10% of the rows should be selected.  Forcing the usage of the index
caused a tiny improvement of performance, but only after running it some few
times to be sure the index got buffered :-)

--
Tobias Brox, Beijing


В списке pgsql-performance по дате сообщения:

От: Tobias Brox
Дата:
Сообщение: Re: Index on a NULL-value
От: "Praveen Raja"
Дата:
Сообщение: very large table