Re: Performance problem... - Mailing list pgsql-admin

From Marcin Giedz
Subject Re: Performance problem...
Date
Msg-id 200503161700.59149.marcin.giedz@eulerhermes.pl
Whole thread Raw
In response to Re: Performance problem...  (Scott Marlowe <smarlowe@g2switchworks.com>)
List pgsql-admin
Dnia środa, 16 marca 2005 15:05, Scott Marlowe napisał:
> On Wed, 2005-03-16 at 07:46, Marcin Giedz wrote:
> > Dnia wtorek, 15 marca 2005 18:00, Scott Marlowe napisał:
> >
> > 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 = n.IdPodmiot AND t1.IdTypNazwa = 153 AND
> > t1.Historia = 0 LEFT JOIN firmy.numer AS t3 ON t3.IdPodmiot = t1.IdFirma
> > AND t3.IdTypNumer = 156 AND t3.Historia = 0 LEFT JOIN firmy.numer AS t4
> > ON t4.IdPodmiot = t1.IdFirma AND t4.IdTypNumer = 160 AND t4.Historia = 0
> > LEFT JOIN firmy.numer AS t5 ON t5.IdPodmiot = t1.IdFirma AND
> > t5.IdTypNumer = 155 AND t5.Historia = 0 LEFT JOIN firmy.numer AS t6 ON
> > t6.IdPodmiot = t1.IdFirma AND t6.IdTypNumer = 627 AND t6.Historia = 0
> > LEFT JOIN firmy.numer AS t7 ON t7.IdPodmiot = t1.IdFirma AND
> > t7.IdTypNumer = 848 AND t7.Historia = 0 LEFT JOIN firmy.numer AS t8 ON
> > t8.IdPodmiot = t1.IdFirma AND t8.IdTypNumer = 763 AND t8.Historia = 0
> > LEFT JOIN firmy.numer AS t9 ON t9.IdPodmiot = t1.IdFirma AND
> > t9.IdTypNumer = 762 AND t9.Historia = 0 LEFT JOIN firmy.formaprawna AS
> > t10 ON t10.IdFirma = t1.IdFirma LEFT JOIN slowniki.tslownik AS t11 ON
> > t11.idslownik = t10.IdTypFormaPrawna AND t11.idjezyktyp = 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 changing
> > 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 .....

BR,
Marcin

> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

pgsql-admin by date:

Previous
From: juanmime@ono.com
Date:
Subject: A simple question
Next
From: "Lee Wu"
Date:
Subject: Re: A simple question