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 93C23565F7 for ; Wed, 16 Mar 2005 16:05:26 +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 30574-03 for ; Wed, 16 Mar 2005 16:05:23 +0000 (GMT) Received: from mail.eulerhermes.pl (mail.hermes-kredit.pl [217.153.119.83]) by svr1.postgresql.org (Postfix) with ESMTP id 8CAAB565C7 for ; Wed, 16 Mar 2005 16:05:21 +0000 (GMT) Received: from [192.168.49.9] (helo=xterm.podsiec) by mail.eulerhermes.pl with esmtpa (Exim 4.42) id 1DBare-00074b-QL for pgsql-admin@postgresql.org; Wed, 16 Mar 2005 16:55:18 +0100 From: Marcin Giedz Organization: Euler Hermes To: pgsql-admin@postgresql.org Subject: Re: Performance problem... Date: Wed, 16 Mar 2005 17:00:59 +0100 User-Agent: KMail/1.7.1 References: <200503141903.08793.marcin.giedz@eulerhermes.pl> <200503161446.26640.marcin.giedz@eulerhermes.pl> <1110981929.28555.179.camel@state.g2switchworks.com> In-Reply-To: <1110981929.28555.179.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200503161700.59149.marcin.giedz@eulerhermes.pl> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.113 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/247 X-Sequence-Number: 16745 Dnia =C5=9Broda, 16 marca 2005 15:05, Scott Marlowe napisa=C5=82: > On Wed, 2005-03-16 at 07:46, Marcin Giedz wrote: > > Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisa=C5=82: > > > > OK now I know I mys query lasts so long: > > > > 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.numer, t7.MskNumer, t8.Id, t8.numer, t8.MskNumer, > > t9.Id, t9.numer, t9.MskNumer, t10.Id, t10.IdTypFormaPrawna, > > t10.MskFormaPrawna, t11.slowo FROM firmy.adres AS n INNER JOIN > > firmy.nazwa AS t1 ON t1.IdFirma =3D n.IdPodmiot AND t1.IdTypNazwa =3D 1= 53 AND > > t1.Historia =3D 0 LEFT JOIN firmy.numer AS t3 ON t3.IdPodmiot =3D t1.Id= =46irma > > AND t3.IdTypNumer =3D 156 AND t3.Historia =3D 0 LEFT JOIN firmy.numer A= S t4 > > ON t4.IdPodmiot =3D t1.IdFirma AND t4.IdTypNumer =3D 160 AND t4.Histori= a =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 > > > > ...because of DISTINCT :D With DISTINCT it tooks about 0,5 minute to > > search several records ..... without DISTINCT (of course a few doubled > > records occured) but query tooks 10ms ;). On the other hand doubled > > records are annoying ..... is it possible to avoid these without changi= ng > > data in tables? Maybe some other query? > > Can you enclose the whole query in something like: > > select distinct * from (your query here) as a > > and see if that helps? > Yes it works excellent ;) Thanks so much .....=20 BR, Marcin > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org