simpler query still significantly slower

From: twanger(at)smartvia(dot)de
To: pgsql-admin(at)postgresql(dot)org
Subject: simpler query still significantly slower
Date: 2001-05-23 08:15:59
Message-ID: 2144293809.990605915265.JavaMail.root@mouse.unimessage.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message armelle clech 2001-05-23 09:28:35 psql runtime error on Windows 2000
Previous Message Dario Brignardello 2001-05-22 23:01:37 WAL and backup recovery