pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Marcin Giedz <marcin.giedz@eulerhermes.pl>
Cc: pgsql-admin@postgresql.org
Subject: Re: Performance problem...
Date: Wed, 16 Mar 2005 08:05:29 -0600
Message-ID: <1110981929.28555.179.camel@state.g2switchworks.com> (raw)
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>

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?



view thread (37+ messages)  latest in thread

Message-ID: <1110981929.28555.179.camel@state.g2switchworks.com>
Permalink:  ../1110981929.28555.179.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1110981929.28555.179.camel@state.g2switchworks.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smarlowe@g2switchworks.com, marcin.giedz@eulerhermes.pl
  Subject: Re: Performance problem...
  In-Reply-To: <1110981929.28555.179.camel@state.g2switchworks.com>
  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> <1110981929.28555.179.camel@state.g2switchworks.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