Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id BDB76475DAD for ; Tue, 30 Apr 2002 12:44:32 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 9B732475D9E for ; Tue, 30 Apr 2002 12:44:31 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3UGiKF20733; Tue, 30 Apr 2002 12:44:20 -0400 (EDT) To: Wolfgang.Fuertbauer@ebewe.com Cc: pgsql-sql@postgresql.org Subject: Re: join-performance problem In-reply-to: References: Comments: In-reply-to Wolfgang.Fuertbauer@ebewe.com message dated "Tue, 30 Apr 2002 17:27:42 +0200" Date: Tue, 30 Apr 2002 12:44:19 -0400 Message-ID: <20730.1020185059@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200204/366 X-Sequence-Number: 7630 Wolfgang.Fuertbauer@ebewe.com writes: >> explain SELECT b.Kdnr, date_part('year', b.Datum), d.Name, e.Name, a.Menge, >> a.Rabproz, a.Netto - (a.netto * a.rabproz / 100), >> a.Netto * b.kurs - ((a.netto * b.kurs) * a.rabproz / 100), 'RG' >> FROM Faktzeilen a, Fakt b, Artikel c, Kollektion d, ArGruppen e >> where a.Fanr = b.nr >> and c.nr = a.Arnr >> and c.Kollektion = d.Nr >> and (c.Gruppe = e.nr or c.gruppe = 0) >> and b.kdnr = 49736; Anyplace that c.gruppe is 0, this is an unconstrained join to e --- ie, you'll get a row out for *every* row of e. Somehow I doubt that's the behavior you really want. I concur with Stephan's observation that you haven't analyzed. But even when you have, this query doesn't give much traction for the use of indexes on a --- the only constraint that might possibly be used to avoid a complete scan of a is the "b.kdnr = 49736", and that's not even on a. The only hope I can see is if you create an index on b.kdnr; then (if there aren't very many rows matching b.kdnr = 49736), it might be able to pick those up with an indexscan on b and then do an inner indexscan join to a using a.Fanr = b.nr. Your secondary indexes on a look like wastes of space (at least for this query). regards, tom lane