Re: simpler query still significantly slower - Mailing list pgsql-admin

From Murat YILDIZ
Subject Re: simpler query still significantly slower
Date
Msg-id 9eg6s1$9bc$1@news.tht.net
Whole thread Raw
In response to simpler query still significantly slower  (twanger@smartvia.de)
List pgsql-admin
use the "explain select  ....."option and paste the outputs for both
queries...

<twanger@smartvia.de> wrote in message
news:2144293809.990605915265.JavaMail.root@mouse.unimessage.net...
> Heyho,
> we tried several versions of a query which give the same results,
> but the one takes 5 secs for the first time and 0.9 secs when i
> execute the query few moments later, while the other takes ~3 secs
> always. How can this be? And how can it be that the second query
> which is in fact simpler than the first takes longer?
>
> Query 1: (5 secs vs 0.9 secs)
>
> select
> distinct
> personen_id
> from
> produktgruppen
> where
> produktgruppen.produktgruppen_id in (
> select
> distinct
> r_gruppen_produkte.produktgruppen_id
> from
> r_gruppen_produkte
> where
>          // this and the next cond are removed in 2nd query
> r_gruppen_produkte.gruppen_id = gruppen.gruppen_id
> and
> gruppen.parent_id=1
> and
> r_gruppen_produkte.gruppen_id = r_personen_bereiche.g_id
> and
> r_personen_bereiche.p_id = 1234
> )
>
> Query 2: (3 secs)
>
> select
> distinct
> personen_id
> from
> produktgruppen
> where
> produktgruppen.produktgruppen_id in (
> select
> distinct
> r_gruppen_produkte.produktgruppen_id
> from
> r_gruppen_produkte
> where
> r_gruppen_produkte.gruppen_id = r_personen_bereiche.g_id
> and
> r_personen_bereiche.p_id = 1234
> )
>
> Our system is Linux 2.2.16, gcc 2.95.2, Postgres 7.0.3 on a Pentium II
450, 128 megs
>
> Thanks in advance
>
> Markus Bertheau
> Cenes Data GmbH
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl



pgsql-admin by date:

Previous
From: "Jeff Barrett"
Date:
Subject: pqReadData() -- backend closed the channel unexpectedly.
Next
From: Bob Himes
Date:
Subject: log files