Re: join-performance problem

On 30.04.2002 18:44:19 pgsql-sql-owner wrote:
>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.

you're absolutly right! I fixed that (by having an record in c with nr 0 -
what
is ok for the application)

>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.

OK;
created an index on b.kdnr (fakt_kunde_key) and here is the analyses:

Hash Join  (cost=482.82..704.62 rows=80 width=93) ->  Hash Join  (cost=481.78..702.17 rows=80 width=79)       ->  Hash
Join (cost=480.44..699.43 rows=80 width=59)             ->  Seq Scan on artikel c  (cost=0.00..155.77 rows=4977
 
width=18)             ->  Hash  (cost=480.24..480.24 rows=80 width=41)                   ->  Nested Loop
(cost=0.00..480.24rows=80 width=41)                         ->  Index Scan using fakt_kunde_key on fakt b
 
(cost=0.00..55.11 rows=13 width=16)                         ->  Index Scan using faktzeilen_pkey on
faktzeilen a  (cost=0.00..31.82 rows=10 width=25)       ->  Hash  (cost=1.27..1.27 rows=27 width=20)             ->
SeqScan on argruppen e  (cost=0.00..1.27 rows=27
 
width=20) ->  Hash  (cost=1.03..1.03 rows=3 width=14)       ->  Seq Scan on kollektion d  (cost=0.00..1.03 rows=3
width=14)

EXPLAIN

can you pleas explain me why c is now scanned sequential?

>Your secondary indexes on a
>look like wastes of space (at least for this query).

they are relevant for other queries; this is only a part from a *large*
ordering / invoicing-system I want to port from Access.

Thanks and Best regards
Wolfgang