speeding up big query lookup - Mailing list pgsql-general

This is a question on speeding up some type of queries.

I have a very big table that catalogs measurements of some objects over
time. Measurements can be of several (~10) types. It keeps the
observation date in a field, and indicates the type of measurement in
another field.

I often need to get the latest measurement of type A for object X.

The table is indexed by object_id.

The obvoious way to get the latest measurement of type A would be to
join the table against

SELECT object_id, object_val_type_id, max(observation_date)
FROM object_val
GROUP BY object_id, object_val_type_id

But this can take a long time, and needs to be done very often.

Next strategy was to build an auxiliary table that just keeps the last
measurement of each type, for each object. I defined triggers to keep
this table up to date whenever the object_val table was updated.

However I don't trust this as much, and though it helps performance in
reads, I'm not sure it's the best option overall.

I found that Postgres can use indexes on pairs, so I'm going to play
with indexing object_val by (object_id, object_val_type_id), and I'm
sure my original JOIN query will be much faster.

But I'm sure many people have this type of problem. Is there a smarter
way to deal with this? I'd love to be able to choose the row with max
value of some of the fields with just one statement, but I think this
can't be done?

Thanks
Jaime



***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************