join-performance problem
От | Wolfgang.Fuertbauer@ebewe.com |
---|---|
Тема | join-performance problem |
Дата | |
Msg-id | OF8B7265D7.366CC910-ONC1256BAB.00540B56@ebewe.co.at обсуждение исходный текст |
Ответы |
Re: join-performance problem
Re: join-performance problem |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: