simpler query significantly slower

Поиск
Список
Период
Сортировка
От twanger@smartvia.de
Тема simpler query significantly slower
Дата
Msg-id -1934561133.990447667088.JavaMail.root@mouse.unimessage.net
обсуждение исходный текст
Ответы Re: simpler query significantly slower
Список pgsql-admin
Hi,

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
        gruppen_produkte.produktgruppen_id
    from
        r_gruppen_produkte
    where
        r_gruppen_produkte.gruppen_id = gruppen.gruppen_id             // this and the next cond are removed in 2nd
query
    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
        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

Markus Bertheau
Cenes Data GmbH
Berlin

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

Предыдущее
От: "Miguel Gonzalez"
Дата:
Сообщение: RE: password politic
Следующее
От: Jaume Teixi
Дата:
Сообщение: cannot connect using terminal nor pgaccess