Обсуждение: 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