From: Pablo Barrón <amentoraz@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Join optimization
Date: Fri, 10 Aug 2007 11:51:47 +0200
Message-ID: <d4d13b4c0708100251s33fad995v5c7b5bb1a5cd341d@mail.gmail.com> (raw)
Hi!
I've been trying to optimize a query in which I join several tables, since
I've seen it takes about 2 seconds, which is way too much.
Well, the query is the following, I'm using LEFT OUTER JOIN just when the
tables can have NULL results, plain JOIN otherwise:
select ="select to_char(a.fecha_publicacion,'dd/MM/yyyy')," +
"c.nombre,date_part('year',CURRENT_TIMESTAMP)-b.ano as
edad," +
"b.alzada,d.nombre,e.nombre,a.precio_a_mostrar,f.nombre," +
"a.destacado,a.visto,h.nombre,m.nombre,g.idprovincia," +
"g.provincia,b.raza_id,b.raza,b.disciplina_id" +
",b.disciplina " +
" from anuncio a JOIN caballo b " +
"ON (a.producto_id=b.id) " +
"JOIN raza c ON (b.raza_id=c.id) " +
" LEFT OUTER JOIN disciplina d " +
"ON (b.disciplina_id=d.id) " +
"LEFT OUTER JOIN nivel_disciplina e " +
"ON (b.disciplina_id=e.disciplina_id " +
"and b.nivel_id=e.nivel) " +
" JOIN anunciante_datos g ON (a.id_anunciante_datos = g.id)"
+
"JOIN provincia f ON (g.idprovincia=f.id) " +
"JOIN categoria h ON (a.categoria_id=h.id) " +
" LEFT OUTER JOIN sexo_caballo m ON " +
"(b.sexo_id=m.id) "+
"WHERE a.id=?";
I'd thought I could pull a trick on the fact that even though this "anuncio"
table (a) is relatively big (a few thousand entries), I really just need to
retrieve one result from it and combine it with the other tables. This
result would be that which matches with the ? in the a.id condition, which
is the Primary Key of this "anuncio" table. The other data is derived from
this specific result in "anuncio" (which btw means "advertisment" in
spanish). For example, "caballo" means "horse" in spanish, and its data is
retrieved as a horse related to the advertisement that sells such a horse.
My idea was to try to cut as soon as possible the few thousands of registers
from the "anuncio" table so that it might make it less costly to make the
query. For instance I tried this, but with no visible results:
" from anuncio a JOIN caballo b " +
"ON (a.id=? AND a.producto_id=b.id) " +
Any ideas on how to critically optimize the query?
Thank you lots =)
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: amentoraz@gmail.com
Subject: Re: Join optimization
In-Reply-To: <d4d13b4c0708100251s33fad995v5c7b5bb1a5cd341d@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