X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7AC115654B for ; Wed, 16 Mar 2005 14:05:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83697-02 for ; Wed, 16 Mar 2005 14:05:28 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id A9A155650D for ; Wed, 16 Mar 2005 14:05:26 +0000 (GMT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 16 Mar 2005 08:05:29 -0600 Subject: Re: Performance problem... From: Scott Marlowe To: Marcin Giedz Cc: pgsql-admin@postgresql.org In-Reply-To: <200503161446.26640.marcin.giedz@eulerhermes.pl> References: <200503141903.08793.marcin.giedz@eulerhermes.pl> <200503151717.36288.marcin.giedz@eulerhermes.pl> <1110906024.28555.146.camel@state.g2switchworks.com> <200503161446.26640.marcin.giedz@eulerhermes.pl> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1110981929.28555.179.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 16 Mar 2005 08:05:29 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/239 X-Sequence-Number: 16737 On Wed, 2005-03-16 at 07:46, Marcin Giedz wrote: > Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisa=C5=82: >=20 > OK now I know I mys query lasts so long: >=20 > SELECT DISTINCT t1.Id, t1.IdTypNazwa, t1.IdFirma, t1.Nazwa, t1.NazwaAscii= , > t1.MskNazwa, t3.Id, t3.numer, t3.MskNumer, t4.Id, t4.numer, t4.MskNumer, > t5.Id, t5.numer, t5.MskNumer, t6.Id, t6.numer, t6.MskNumer, t7.Id, t7.num= er, > t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, t9.Id, t9.numer, t9.MskNumer, > t10.Id, t10.IdTypFormaPrawna, t10.MskFormaPrawna, t11.slowo FROM firmy.ad= res > AS n INNER JOIN firmy.nazwa AS t1 ON t1.IdFirma =3D n.IdPodmiot AND > t1.IdTypNazwa =3D 153 AND t1.Historia =3D 0 LEFT JOIN firmy.numer AS t3 O= N > t3.IdPodmiot =3D t1.IdFirma AND t3.IdTypNumer =3D 156 AND t3.Historia =3D= 0 LEFT > JOIN firmy.numer AS t4 ON t4.IdPodmiot =3D t1.IdFirma AND t4.IdTypNumer = =3D 160 > AND t4.Historia =3D 0 LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot =3D t1.= IdFirma > AND t5.IdTypNumer =3D 155 AND t5.Historia =3D 0 LEFT JOIN firmy.numer AS = t6 ON > t6.IdPodmiot =3D t1.IdFirma AND t6.IdTypNumer =3D 627 AND t6.Historia =3D= 0 LEFT > JOIN firmy.numer AS t7 ON t7.IdPodmiot =3D t1.IdFirma AND t7.IdTypNumer = =3D 848 > AND t7.Historia =3D 0 LEFT JOIN firmy.numer AS t8 ON t8.IdPodmiot =3D t1.= IdFirma > AND t8.IdTypNumer =3D 763 AND t8.Historia =3D 0 LEFT JOIN firmy.numer AS = t9 ON > t9.IdPodmiot =3D t1.IdFirma AND t9.IdTypNumer =3D 762 AND t9.Historia =3D= 0 LEFT > JOIN firmy.formaprawna AS t10 ON t10.IdFirma =3D t1.IdFirma LEFT JOIN > slowniki.tslownik AS t11 ON t11.idslownik =3D t10.IdTypFormaPrawna AND > t11.idjezyktyp =3D 2 WHERE n.Miasto ILIKE 'warszawa%' LIMIT 25 >=20 > ...because of DISTINCT :D With DISTINCT it tooks about 0,5 minute to sear= ch=20 > several records ..... without DISTINCT (of course a few doubled records=20 > occured) but query tooks 10ms ;). On the other hand doubled records are=20 > annoying ..... is it possible to avoid these without changing data in tab= les?=20 > Maybe some other query? Can you enclose the whole query in something like: select distinct * from (your query here) as a=20 and see if that helps?