simpler query still significantly slower

Поиск
Список
Период
Сортировка
От twanger@smartvia.de
Тема simpler query still significantly slower
Дата
Msg-id 2144293809.990605915265.JavaMail.root@mouse.unimessage.net
обсуждение исходный текст
Ответы Re: simpler query still significantly slower  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список 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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Need help
Следующее
От: Dario Brignardello
Дата:
Сообщение: WAL and backup recovery