Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 2BEE49FB5BA for ; Fri, 10 Aug 2007 06:51:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 79066-06 for ; Fri, 10 Aug 2007 06:51:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.184]) by postgresql.org (Postfix) with ESMTP id F360F9FB76D for ; Fri, 10 Aug 2007 06:51:49 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i10so1016702mue for ; Fri, 10 Aug 2007 02:51:48 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=eUDxpDSgSLOUv0LiUV/R/UEapmqVw1Nb7Z/AaIRAvZgugyizWoGbVYKLq1V6CcUUmjXcQFeBmOKWf+iRxRpws1R60F/F0Ttb57SrCvR9yonEkCcsgEuAxeTatzp3skKGBFnyfkkqP960bJa2QVWuod6t8+qcnF7v8pOylyrwpoA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=PnFVb/E5BDv2fX9cBb1eneM9vGFg+mnfB6bL4TiQqGL8JWxuIyXhALszYvSg0h/L33ftVRzNbK1NIPHZP6UbjUnKFFfeXuGOBcXYqs32V0UY4o6POX5fbgPCORsNKlz/rD2RM/cK7F0uGkR0/DLe79ZUdjWUEddmHJgXmhDDBe4= Received: by 10.82.174.20 with SMTP id w20mr3521913bue.1186739507965; Fri, 10 Aug 2007 02:51:47 -0700 (PDT) Received: by 10.82.190.12 with HTTP; Fri, 10 Aug 2007 02:51:47 -0700 (PDT) Message-ID: Date: Fri, 10 Aug 2007 11:51:47 +0200 From: "=?ISO-8859-1?Q?Pablo_Barr=F3n?=" To: pgsql-sql@postgresql.org Subject: Join optimization MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9614_14913636.1186739507943" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/77 X-Sequence-Number: 29015 ------=_Part_9614_14913636.1186739507943 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 =) ------=_Part_9614_14913636.1186739507943 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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 =)

------=_Part_9614_14913636.1186739507943--