From: | "Murat YILDIZ" <myildiz(at)bellona(dot)com(dot)tr> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Ynt: simpler query still significantly slower |
Date: | 2001-05-24 06:57:36 |
Message-ID: | 9eibcc$qhb$1@news.tht.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote in message
news:Pine(dot)BSF(dot)4(dot)21(dot)0105230907460(dot)67638-100000(at)megazone23(dot)bigpanda(dot)com(dot)(dot)(dot)
>
> 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(at)smartvia(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Murat YILDIZ | 2001-05-24 11:45:54 | Ynt: WAL and backup recovery |
Previous Message | Tom Lane | 2001-05-23 21:31:29 | Re: WARNING: owner of type appears to be invalid? |