Ynt: simpler query still significantly slower - Mailing list pgsql-admin

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