Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 4CE61476080 for ; Tue, 30 Apr 2002 12:03:26 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id A2820475FD2 for ; Tue, 30 Apr 2002 11:58:04 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 80E6AD60E; Tue, 30 Apr 2002 08:58:09 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 768D95C01; Tue, 30 Apr 2002 08:58:09 -0700 (PDT) Date: Tue, 30 Apr 2002 08:58:09 -0700 (PDT) From: Stephan Szabo To: Cc: Subject: Re: join-performance problem In-Reply-To: Message-ID: <20020430085706.R90531-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200204/365 X-Sequence-Number: 7629 On Tue, 30 Apr 2002 Wolfgang.Fuertbauer@ebewe.com wrote: > Hi, > > I have problem using joins: there are indexes which could be used, but > sequential-scan takes place; Have you run vacuum analyze on the database? Some of those statistics down there look suspiciously like the default statistics (1000 rows in faktzeilen) > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >