pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (5+ messages)  latest in thread

Message-ID: <OF65A70128.C3997CD6-ONC1256BAD.002B15FA@ebewe.co.at>
Permalink:  ../OF65A70128.C3997CD6-ONC1256BAD.002B15FA@ebewe.co.at/
Also on:    postgresql.org/message-id/OF65A70128.C3997CD6-ONC1256BAD.002B15FA@ebewe.co.at

 · 

reply

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