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