From: Wolfgang.Fuertbauer@ebewe.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org, Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: join-performance problem
Date: Thu, 2 May 2002 10:00:47 +0200
Message-ID: <OF65A70128.C3997CD6-ONC1256BAD.002B15FA@ebewe.co.at> (raw)
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.24 rows=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)
-> Seq Scan 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Wolfgang.Fuertbauer@ebewe.com, tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com
Subject: Re: join-performance problem
In-Reply-To: <OF65A70128.C3997CD6-ONC1256BAD.002B15FA@ebewe.co.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox