No, you can make this work just fine if you JOIN right. You're way is a more concise way of expressing it, though. Tom's trick SELECT DISTINCT ON (object_id, object_val_type_id) * from object_val ORDER BY object_id DESC, object_val_type_id DESC, observation_date DESC Runs about twice as fast as the GROUP BY ... HAVING, but definitely not as fast as keeping a separate table with only the latest observations, updated by triggers. I'll be testing out the differences in overall performance for my applications. Thanks for the suggestions, Jaime -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alban Hertroys Sent: Monday, August 28, 2006 4:57 AM To: Silvela, Jaime (Exchange) Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] speeding up big query lookup Silvela, Jaime (Exchange) wrote: > 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 I'm not sure this is actually the result you want; doesn't this give you all the unique (object_id, object_val_type_id)'s combined with the max observation_date in the table (as in, not necessarily related to the records listed)? I'd think you want this: SELECT object_id, object_val_type_id, observation_date FROM object_val GROUP BY object_id, object_val_type_id, observation_date HAVING observation_date = max(observation_date) Which'd return a single record with the highest observation_date. Though not strictly necessary, I can imagine you'd want observation_date to be unique, or you could get grouped observations with the same date. Although ordering and limiting is probably faster. I don't think the planner is intelligent enough to know that this would only return the record with the highest observation_date - it may be smart enough to reject ("drop from the result set") found records after finding ones with a higher observation_date (which'd be "interesting" when using cursors) or something along those lines. Hmm... Now I'm all curious; an EXPLAIN'd be interesting... Sorry for the mostly useless post :P Regards, -- Alban Hertroys alban@xxxxxxxxxxxxxxxxx magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq *********************************************************************** 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. ***********************************************************************