Обсуждение: simpler query significantly slower

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

twanger@smartvia.de writes:
>     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

This SQL seems rather seriously confused.  It looks to me like you will
get back one copy of each distinct produktgruppen_id value appearing in
gruppen_produkte.  Since no gruppen_produkte field is constrained by
WHERE, none will be eliminated.  All that the rest of the query does is
cause a completely useless Cartesian-product join against some subset
of r_gruppen_produkte * gruppen * r_personen_bereiche.

Possibly you meant to select from r_gruppen_produkte.

BTW, PG 7.1 would have complained about the lack of FROM entries for
gruppen_produkte, gruppen, and r_personen_bereiche, which might have
clued you to your error ...

            regards, tom lane

> twanger@smartvia.de writes:
> >     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
> This SQL seems rather seriously confused.  It
> looks to me like you will
> get back one copy of each distinct
> produktgruppen_id value appearing in
> gruppen_produkte.  Since no gruppen_produkte
> field is constrained by
> WHERE, none will be eliminated.  All that the
> rest of the query does is
> cause a completely useless Cartesian-product
> join against some subset
> of r_gruppen_produkte * gruppen *
> r_personen_bereiche.
> Possibly you meant to select from
> r_gruppen_produkte.
> BTW, PG 7.1 would have complained about the
> lack of FROM entries for
> gruppen_produkte, gruppen, and
> r_personen_bereiche, which might have
> clued you to your error ...
>             regards, tom lane

Yeah sorry, you are right, but the typo is only in my posting. The query does actually query r_gruppen_produkte.