X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 90666D1C96D for ; Wed, 23 Jul 2003 14:29:11 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 32913-01 for ; Wed, 23 Jul 2003 11:28:26 -0300 (ADT) Received: from www.sgbs.de (www.sgbs.de [62.225.75.154]) by svr1.postgresql.org (Postfix) with ESMTP id 70469D1C965 for ; Wed, 23 Jul 2003 11:29:05 -0300 (ADT) From: =?iso-8859-1?q?J=F6rg=20Schulz?= To: pgsql-performance@postgresql.org Subject: different query plan for same select Date: Wed, 23 Jul 2003 16:28:54 +0200 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200307231628.54295.jschulz@sgbs.de> X-Virus-Scanned: by AMaViS X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=USER_AGENT_KMAIL X-Spam-Level: X-Archive-Number: 200307/392 X-Sequence-Number: 2685 I have these two tables with the same data on two different machines (SuSE 8.2 and Gentoo both with 7.3.2): schulz=3D> \d rechnung Table "jschulz.rechnung" Column | Type | Modifiers=20 ----------------+----------+----------- system | smallint | not null jahr | smallint | not null monat | smallint | not null rechnungsnr | integer | not null rechnungsdatum | date | not null kundennr | integer | not null seiten | smallint | not null formularnr | smallint |=20 text | text | not null Indexes: rechnung_pkey primary key btree (system, jahr, rechnungsnr), rechnung_kundennr btree (kundennr), rechnung_rechnungsdatum btree (rechnungsdatum), rechnung_rechnungsnr btree (rechnungsnr) schulz=3D> \d rechnung_zusatz Table "jschulz.rechnung_zusatz" Column | Type | Modifiers=20 -------------+----------+----------- system | smallint | not null jahr | smallint | not null rechnungsnr | integer | not null objektnr | integer |=20 Indexes: rechnung_zusatz_uniq_objektnr unique btree (system, jahr,=20 rechnungsnr, objektnr), rechnung_zusatz_objektnr btree (objektnr) Foreign Key constraints: $1 FOREIGN KEY (system, jahr, rechnungsnr) REFEREN= CES=20 rechnung(system, jahr, rechnungsnr) ON UPDATE NO ACTION ON DELETE NO ACTION schulz=3D>=20 On the SuSE machine an explain gives the following: schulz=3D> explain select system, jahr, rechnungsnr from (rechnung natural = left=20 join rechnung_zusatz) where objektnr=3D1; QUERY PLAN=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 ---------------------------------------------------------------------------= ----------- Hash Join (cost=3D0.00..25.04 rows=3D1000 width=3D20) Hash Cond: ("outer".rechnungsnr =3D "inner".rechnungsnr) Join Filter: (("outer".system =3D "inner".system) AND ("outer".jahr =3D= =20 "inner".jahr)) Filter: ("inner".objektnr =3D 1) -> Seq Scan on rechnung (cost=3D0.00..20.00 rows=3D1000 width=3D8) -> Hash (cost=3D0.00..0.00 rows=3D1 width=3D12) -> Seq Scan on rechnung_zusatz (cost=3D0.00..0.00 rows=3D1 width= =3D12) (7 rows) schulz=3D> On the Gentoo machine the same explain gives: schulz=3D> explain select system, jahr, rechnungsnr from (rechnung natural = left=20 join rechnung_zusatz) where objektnr=3D1; QUERY PLAN=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ------ Merge Join (cost=3D0.00..109.00 rows=3D1000 width=3D20) Merge Cond: (("outer".system =3D "inner".system) AND ("outer".jahr =3D= =20 "inner".jahr) AND ("outer".rechnungsnr =3D "inner".rechnungsnr)) Filter: ("inner".objektnr =3D 1) -> Index Scan using rechnung_pkey on rechnung (cost=3D0.00..52.00 rows= =3D1000=20 width=3D8) -> Index Scan using rechnung_zusatz_uni_objektnr on rechnung_zusatz=20= =20 (cost=3D0.00..52.00 rows=3D1000 width=3D12) (5 Zeilen) schulz=3D> The select on the SuSE machine finishes in about 3 seconds and on the Gentoo machine it doesn't seem to come to an end at all. Each table has about 80.000 rows. I'm not very familar with the output of the explain command but can you tell me why I get two different query plans? J=F6rg