Re: speeding up big query lookup

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alban Hertroys
Sent: Monday, August 28, 2006 4:57 AM
To: Silvela, Jaime (Exchange)
Cc: pgsql-general(at)postgresql(dot)org
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(at)magproductions(dot)nl

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