From: César Antonio León Mansilla <cesar.leon.2006@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Getting associated columns with max() without subquery
Date: Sun, 4 Feb 2007 20:30:08 -0300
Message-ID: <d947ad9f0702041530n4babaa44jcd79261b01a147a@mail.gmail.com> (raw)
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./
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cesar.leon.2006@gmail.com
Subject: Re: Getting associated columns with max() without subquery
In-Reply-To: <d947ad9f0702041530n4babaa44jcd79261b01a147a@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox