Re: simpler query still significantly slower

First thing is that you probably want to use exists instead of
in (see FAQ).  Second thing is, do the two queries gather the
same rows?  There are references to tables that don't seem
to be in from lists, which from lists are those tables in?
Finally, what does explain show for the two queries?

On Wed, 23 May 2001 twanger@smartvia.de wrote:

> 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
>