Getting associated columns with max() without subquery

From: César Antonio León Mansilla <cesar(dot)leon(dot)2006(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Getting associated columns with max() without subquery
Date: 2007-02-04 23:30:08
Message-ID: d947ad9f0702041530n4babaa44jcd79261b01a147a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi, this is my first post, sorry for my english, I'm chilean and my first
language is spanish.
I don't know if somebody got this solved, but here we go:

I was surfing the net for the fastest query that let me to
get the max movement from a production
plus associated columns related to this production, everywhere I got "use a
subquery to get the
max movement", so, I did write:

select mov.cod_produccion, mov.cod_ubicacion, mov.ind_salida,
mov.cod_movimiento as max_movimiento
from producciones pro
inner join movimientos mov
on mov.cod_produccion=pro.cod_produccion and mov.cod_movimiento=
(select max(mov2.cod_movimiento) from movimientos mov2 where
mov2.cod_produccion=pro.cod_produccion)
where mov.cod_ubicacion=5 and not mov.ind_salida

that was a good answer but too slow... :(
My database has 71727 rows in "producciones" table and 112266 rows in
"movimientos" table, then the
response time for this query was 31531ms, getting 587 rows as result.
Looking the help, and understanding which is the difference between WHERE
and HAVING, I try to optimize that
query to get a better response time and got it:

select mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida, max(
mov2.cod_movimiento) as max_movimiento
from producciones pro
inner join movimientos mov1
on mov1.cod_produccion=pro.cod_produccion
inner join movimientos mov2
on mov2.cod_produccion=pro.cod_produccion
group by mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida,
mov1.cod_movimiento
having mov1.cod_movimiento=max(mov2.cod_movimiento) and
mov1.cod_ubicacion=5 and not mov1.ind_salida
order by mov1.cod_produccion

the response time now was 297ms, even with "order by", getting the same 587
rows as result.

the table fields are:

movimientos
------------------
cod_movimiento (pk) (serial)
cod_produccion (fk) (int4)
cod_ubicacion (fk) (int4)
fec_movimiento (timestamp)
ind_salida (bool)

producciones
--------------------
cod_produccion (pk) (serial)
cod_dia_laboral (fk) (int4)
cod_producto (fk) (int4)
fec_produccion (timestamp)
pso_produccion (numeric(10,2))

My test computer is:
Hardware: Sempron 2400+, 512Mb. RAM, 80Gb. 7200RPM.
Software: Windows XP Professional, PostgreSQL (of course :).

This look like standard SQL and would work in any DBMS, so my question is:
Are really those querys getting
the same results?

Thank in advance for your answer.

Good bye.
Greeting from Chile.

César A. León Mansilla./

Browse pgsql-sql by date

  From Date Subject
Next Message sneumann 2007-02-05 16:02:21 PL/pgsql declaration of string / bit / number with given (variable!) length
Previous Message Ivo Rossacher 2007-02-04 23:26:42 Re: [SQL] Question regarding multibyte.