Re: timestamp indexing

[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