Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 7092F475C40 for ; Tue, 30 Apr 2002 11:28:53 -0400 (EDT) Received: from ebewe.com (unknown [80.120.1.196]) by postgresql.org (Postfix) with ESMTP id EAF6A475FA6 for ; Tue, 30 Apr 2002 11:26:36 -0400 (EDT) Received: from exim by ebewe.com with spam-scanned (Exim 4.02) id 172ZWa-0005wn-00 for pgsql-sql@postgresql.org; Tue, 30 Apr 2002 17:26:41 +0200 Received: from horn.intern.ebewe.at ([172.16.5.1] helo=mail.ebewe.co.at) by ebewe.com with esmtp (Exim 4.02) id 172ZWZ-0005wi-00 for pgsql-sql@postgresql.org; Tue, 30 Apr 2002 17:26:39 +0200 Subject: join-performance problem To: pgsql-sql@postgresql.org X-Mailer: Lotus Notes Release 5.0.9 November 16, 2001 Message-ID: From: Wolfgang.Fuertbauer@ebewe.com Date: Tue, 30 Apr 2002 17:27:42 +0200 X-MIMETrack: Serialize by Router on Unta_Notes_Srv01/EBEWE/AT(Release 5.0.9a |January 7, 2002) at 30.04.2002 17:27:43 MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Scanner: exiscan for exim4 (http://duncanthrax.net/exiscan/) *172ZWZ-0005wi-00*8qVi.HEsHAQ* X-Spam-Status: No, hits=0.8 required=5.0 tests=NO_REAL_NAME,AWL version=2.20 X-Spam-Level: X-Archive-Number: 200204/364 X-Sequence-Number: 7628 Hi, I have problem using joins: there are indexes which could be used, but sequential-scan takes place; see: >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; >NOTICE: QUERY PLAN:996" 9L, 376C written > >Nested Loop (cost=17.08..265.50 rows=30 width=152) > -> Nested Loop (cost=17.08..90.50 rows=5 width=115) > -> Nested Loop (cost=17.08..66.32 rows=5 width=78) > -> Hash Join (cost=17.08..42.14 rows=5 width=51) > -> Seq Scan on faktzeilen a (cost=0.00..20.00 rows=1000 width=35) > -> Hash (cost=17.07..17.07 rows=5 width=16) > -> Index Scan using fakt_tbkundentbfakt_key on fakt b (cost=0.00..17.07 rows=5 width=16) > -> Index Scan using artikel_pkey on artikel c (cost=0.00..4.82 rows=1 width=27) > -> Index Scan using kollektion_pkey on kollektion d (cost=0.00..4.82 rows=1 width=37) > -> Seq Scan on argruppen e (cost=0.00..20.00 rows=1000 width=37) > >EXPLAIN my problem is the sequential scan for tabel faktzeilen (which has 250000 rows); the following indexes exist: primary index: fanr, znr index2: fanr index3: fanr, arnr, arname, pknr can one tell me why index2 is not used for the join? Thanks in advance Wolfgang