Re: speeding up big query lookup

From: Alban Hertroys <alban(at)magproductions(dot)nl>
To: "Silvela, Jaime \(Exchange\)" <JSilvela(at)Bear(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: speeding up big query lookup
Date: 2006-08-28 08:56:39
Message-ID: 44F2AFC7.70206@magproductions.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 //

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ragnar 2006-08-28 09:11:44 Re: optimising UNION performance
Previous Message Peter Eisentraut 2006-08-28 08:56:30 Re: optimising UNION performance