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

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

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
>

use the "explain select  ....."option and paste the outputs for both
queries...

<twanger@smartvia.de> wrote in message
news:2144293809.990605915265.JavaMail.root@mouse.unimessage.net...
> 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

Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote in message
news:Pine.BSF.4.21.0105230907460.67638-100000@megazone23.bigpanda.com...
>
> 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?
Actually you should asked sql related questions on
comp.databases.postgresql.sql but as for me I would look still at the
explain aoutput
just try and you will see
for example
explain select * from tabname where field>10
The output will look like :
NOTICE:  QUERY PLAN:

Aggregate  (cost=8.30..8.30 rows=1 width=4)
  ->  Seq Scan on puantajlar  (cost=0.00..7.64 rows=264 width=4)

EXPLAIN

It will give you an opinion about the cost of the query...and wether
sequential scan or index is used....

Murat




>
> 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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster