Re: [GENERAL] speeding up big query lookup

2006-08-29 Thread Silvela, Jaime \(Exchange\)
queries suggested so far. I'll definitely check that book, I've been looking for something like that. Thanks Jaime -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of macgillivary Sent: Monday, August 28, 2006 10:14 PM To: pgsql-general@postgresql.org

Re: [GENERAL] speeding up big query lookup

2006-08-29 Thread Geoffrey
macgillivary wrote: I just finished reading 'The Art of SQL' by Stephane Faroult who has a chapter (ch 6) discussing this very topic. I'd be curious to know any other references, books, folks would recommend when it comes to writing efficient SQL, as well as references on database design. -

Re: [GENERAL] speeding up big query lookup

2006-08-29 Thread macgillivary
Just for fun, another approach since I believe pg supports it: select whateverFields from object_val as outer where (outer.object_id, outer.object_val_type_id,outer.observation_date) IN (select inner.object_id, inner.object_val_type,max(inner.observation_date) from object_val as inner where i

Re: [GENERAL] speeding up big query lookup

2006-08-29 Thread macgillivary
I just finished reading 'The Art of SQL' by Stephane Faroult who has a chapter (ch 6) discussing this very topic. I strongly recommend any developer dealing with databases take a few days to read this narrative. A solution would seem to depend on whether you have many objects which change in meas

Re: [GENERAL] speeding up big query lookup

2006-08-28 Thread Silvela, Jaime \(Exchange\)
-- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alban Hertroys Sent: Monday, August 28, 2006 4:57 AM To: Silvela, Jaime (Exchange) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] speeding up big query lookup Silvela, Jaime (Exchange) wrote: > The obvoious way

Re: [GENERAL] speeding up big query lookup

2006-08-28 Thread Alban Hertroys
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; doe

Re: [GENERAL] speeding up big query lookup

2006-08-27 Thread Tom Lane
"Silvela, Jaime \(Exchange\)" <[EMAIL PROTECTED]> writes: > 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 ne

Re: [GENERAL] speeding up big query lookup

2006-08-26 Thread Ragnar
On fös, 2006-08-25 at 18:34 -0400, Silvela, Jaime (Exchange) wrote: > 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,

[GENERAL] speeding up big query lookup

2006-08-25 Thread Silvela, Jaime \(Exchange\)
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 t