Probleme beim Erkennen des/der Flaschenhälse.

Hallo,

Ich habe hier folgendes Abfrageergebnis:

QUERY PLAN
Sort  (cost=122274298.82..122274428.21 rows=51757 width=1621) (actual time=669615.386..669615.577 rows=1193 loops=1)
  Sort Key: a.nkvkey
  Sort Method:  quicksort  Memory: 636kB
  ->  Hash Join  (cost=89977330.27..122160909.88 rows=51757 width=1621) (actual time=669575.963..669612.035 rows=1193 loops=1)
        Hash Cond: (a.nkundekey = t_kunde.nkundekey)
        ->  Index Scan Backward using t_kv_pkey on t_kv a  (cost=89957109.48..122107340.30 rows=66392 width=936) (actual time=668279.807..669161.505 rows=1193 loops=1)
              Filter: (((sangebot)::text <> '1'::text) AND (nmandantkey = 1) AND ((hashed SubPlan 1) OR (SubPlan 3)))
              SubPlan 1
                ->  Merge Join  (cost=21588.71..30398.39 rows=31129 width=4) (actual time=302.520..446.108 rows=1089 loops=1)
                      Merge Cond: (innerselect.nkvkey = outerselect.nkvkey)
                      ->  GroupAggregate  (cost=21588.71..25235.59 rows=31129 width=10) (actual time=302.493..444.858 rows=1089 loops=1)
                            Filter: (((sum((innerselect.sposerledigt)::numeric(9,2)))::numeric(9,2) / (count(innerselect.nkvposkey))::numeric(9,2)) < 2::numeric)
                            ->  Sort  (cost=21588.71..21898.21 rows=123797 width=10) (actual time=302.449..334.599 rows=123797 loops=1)
                                  Sort Key: innerselect.nkvkey
                                  Sort Method:  external merge  Disk: 2648kB
                                  ->  Seq Scan on t_kvpos innerselect  (cost=0.00..8999.46 rows=123797 width=10) (actual time=0.014..191.767 rows=123797 loops=1)
                                        Filter: ((sstorno)::text <> '1'::text)
                      ->  Index Scan using t_kv_pkey on t_kv outerselect  (cost=0.00..4241.10 rows=88522 width=4) (actual time=0.022..0.576 rows=1136 loops=1)
              SubPlan 3
                ->  Materialize  (cost=89926633.27..89927248.88 rows=44261 width=4) (actual time=0.002..15.477 rows=104 loops=43172)
                      ->  Seq Scan on t_kv  (cost=9297.77..89926416.00 rows=44261 width=4) (actual time=92.802..667813.198 rows=104 loops=1)
                            Filter: (NOT (SubPlan 2))
                            SubPlan 2
                              ->  Materialize  (cost=9297.77..11019.74 rows=123797 width=4) (actual time=0.002..7.357 rows=62985 loops=44261)
                                    ->  Seq Scan on t_kvpos  (cost=0.00..8689.97 rows=123797 width=4) (actual time=0.004..41.827 rows=123797 loops=1)
        ->  Hash  (cost=15552.99..15552.99 rows=46944 width=685) (actual time=417.485..417.485 rows=46944 loops=1)
              ->  Hash Left Join  (cost=6431.95..15552.99 rows=46944 width=685) (actual time=69.346..341.650 rows=46944 loops=1)
                    Hash Cond: (t_adresse.nanredekz = anrede.nkennzeichenkey)
                    ->  Hash Left Join  (cost=6397.43..14873.58 rows=46944 width=668) (actual time=68.954..313.325 rows=46944 loops=1)
                          Hash Cond: (t_adresse.nbriefanredekz = briefanrede.nkennzeichenkey)
                          ->  Hash Left Join  (cost=6362.91..14663.01 rows=46944 width=651) (actual time=68.548..293.852 rows=46944 loops=1)
                                Hash Cond: (t_adresse.nlandkz = land.nkennzeichenkey)
                                ->  Hash Left Join  (cost=6328.39..13983.01 rows=46944 width=634) (actual time=68.150..268.728 rows=46944 loops=1)
                                      Hash Cond: (t_kunde.nkundengruppekz = kundengruppe.nkennzeichenkey)
                                      ->  Hash Left Join  (cost=6293.87..13772.44 rows=46944 width=617) (actual time=67.684..249.807 rows=46944 loops=1)
                                            Hash Cond: (t_kunde.nzahlungskonditionkey = t_zahlungskondition.nzahlungskonditionkey)
                                            ->  Hash Left Join  (cost=6289.60..13122.69 rows=46944 width=617) (actual time=67.626..224.878 rows=46944 loops=1)
                                                  Hash Cond: (t_kunde.nmitarbeiterkey = t_mitarbeiter.nmitarbeiterkey)
                                                  ->  Merge Left Join  (cost=6283.59..12940.62 rows=46944 width=613) (actual time=67.503..205.688 rows=46944 loops=1)
                                                        Merge Cond: (t_kunde.nadressekey = t_adresse.nadressekey)
                                                        ->  Index Scan using t_kunde_idx_nadressekey on t_kunde  (cost=0.00..2512.89 rows=46944 width=509) (actual time=0.041..17.039 rows=46944 loops=1)
                                                        ->  Index Scan using t_adresse_pkey on t_adresse  (cost=0.00..9661.48 rows=130200 width=108) (actual time=0.035..106.359 rows=128278 loops=1)
                                                  ->  Hash  (cost=4.34..4.34 rows=134 width=8) (actual time=0.098..0.098 rows=134 loops=1)
                                                        ->  Seq Scan on t_mitarbeiter  (cost=0.00..4.34 rows=134 width=8) (actual time=0.020..0.067 rows=134 loops=1)
                                            ->  Hash  (cost=3.01..3.01 rows=101 width=4) (actual time=0.046..0.046 rows=101 loops=1)
                                                  ->  Seq Scan on t_zahlungskondition  (cost=0.00..3.01 rows=101 width=4) (actual time=0.007..0.023 rows=101 loops=1)
                                      ->  Hash  (cost=23.12..23.12 rows=912 width=21) (actual time=0.456..0.456 rows=912 loops=1)
                                            ->  Seq Scan on t_kennzeichen kundengruppe  (cost=0.00..23.12 rows=912 width=21) (actual time=0.012..0.212 rows=912 loops=1)
                                ->  Hash  (cost=23.12..23.12 rows=912 width=21) (actual time=0.387..0.387 rows=912 loops=1)
                                      ->  Seq Scan on t_kennzeichen land  (cost=0.00..23.12 rows=912 width=21) (actual time=0.002..0.157 rows=912 loops=1)
                          ->  Hash  (cost=23.12..23.12 rows=912 width=21) (actual time=0.393..0.393 rows=912 loops=1)
                                ->  Seq Scan on t_kennzeichen briefanrede  (cost=0.00..23.12 rows=912 width=21) (actual time=0.002..0.159 rows=912 loops=1)
                    ->  Hash  (cost=23.12..23.12 rows=912 width=21) (actual time=0.379..0.379 rows=912 loops=1)
                          ->  Seq Scan on t_kennzeichen anrede  (cost=0.00..23.12 rows=912 width=21) (actual time=0.001..0.143 rows=912 loops=1)
Total runtime: 669618.031 ms

In welche der Zeilen sehe ich den Geschwindigkeitsverlust???


Hier das Statement:

explain analyze 

SELECT
  IntToChar(a.ROWVERSION),
  a.NKVKEY,
  a.AENDERUNGSDATUM,
  a.BEARBEITER,
  a.DTKVDATUM,
  a.DTLASTAB,
  a.ERFASSER,
  a.ERFASSUNGSDATUM,
  a.NADRESSELIEFKEY,
  a.NADRESSERECHKEY,
  a.NANZAHLUNGVERRECHNET,
  a.NAVISEKZ,
  a.NETAGE,
  a.NFILIALEKEY,
  a.NGEWANDELTNACH,
  a.NKUNDEKEY,
  a.NLIEFERTERMINKZ,
  a.NMANDANTKEY,
  a.NRECHNUNGKZ,
  a.NTOURKZ,
  a.NWERBEGEBIETKZ,
  a.NZAHLUNGSKONDITIONKEY,
  a.NMITARBEITERKEY,
  a.SAUFZUG,
  a.SANGEBOT,
  a.SBEMERKUNG,
  a.SBESTNRKUNDE,
  a.SDEBITORIDENT,
  a.SFIBUKONTOANGELEGT,
  a.SGUTSCHRIFT,
  a.SKVIDENT,
  a.SLSADRESSEIGNORIEREN,
  a.SLSCHEINMITPREIS,
  a.SMWSTAUSWEIS,
  a.SRGADRESSEIGNORIEREN,
  a.STEILLIEFERUNG,
  a.SUHRZEIT,
  a.SUSEETAGE,
  a.SZAHLUNGSTEXT,
  a.SEASYARCHIVE,
  b.DTGEBURTSTAG,
  b.NADRESSARTKZ,
  b.NADRESSEKEY,
  b.NADRESSELIKEY,
  b.NADRESSERGKEY,
  b.NANREDEKZ,
  b.NBRIEFANREDEKZ,
  b.NETAGE,
  b.NGROSSHANDELRABATT,
  b.NKUNDEKEY,
  b.NKUNDENGRUPPEKZ,
  b.NLANDKZ,
  b.NMANDANTKEY,
  b.NMITARBEITERKEY,
  b.NTOURIDENTKZ,
  b.NWERBEGEBIDENTKZ,
  b.NZAHLUNGSKONDITIONKEY,
  b.SANREDE,
  b.SAUFZUG,
  b.SBANKINSTITUT,
  b.SBLZ,
  b.SBRIEFANREDE,
  b.SDEBITORIDENT,
  b.SEMAIL,
  b.SGROSSHANDEL,
  b.SKONTOINHABER,
  b.SKONTONR,
  b.SKUNDEGESPERRT,
  b.SKUNDEIDENT,
  b.SKUNDENGRUPPE,
  b.SLAND,
  b.SLSCHEINMITPREIS,
  b.SMITARBEITERIDENT,
  b.SMOBILTELEFON,
  b.SMWSTAUSWEIS,
  b.SMWSTAUSWEISKUNDE,
  b.SNAME1,
  b.SNAME2,
  b.SNAME3,
  b.SORT,
  b.SPLZ,
  b.SPLZPF,
  b.SPOSTFACH,
  b.SSTRASSE,
  b.STELEFAX,
  b.STELEFON1,
  b.STELEFON2,
  b.SUSTIDENT,
  b.SWEBADRESSE
FROM
  T_KV a,
  V_KUNDE b
WHERE
  a.NMANDANTKEY = 1 AND
  a.SANGEBOT != '1' AND
  (a.NKVKEY IN (
                 SELECT
                   outerselect.nkvkey
                 FROM
                   t_kv outerselect,
                   (
                     SELECT
                       innerselect.nkvkey,
                       (cast (SUM(cast (innerselect.sposerledigt AS numeric (9,
                       2))) AS numeric (9, 2)) / cast (COUNT(
                       innerselect.nkvposkey) AS numeric (9, 2))) AS nvalue
                     FROM
                       t_kvpos innerselect
                     WHERE
                       innerselect.sstorno <> '1'
                     GROUP BY
                       innerselect.nkvkey
                   ) middleselect
                 WHERE
                   outerselect.nkvkey = middleselect.nkvkey AND
                   middleselect.nvalue < 2
  ) OR
  a.NKVKEY IN (
                SELECT
                  nkvkey
                FROM
                  t_kv
                WHERE
                  nkvkey NOT IN (
                                  SELECT
                                    nkvkey
                                  FROM
                                    t_kvpos
                  )
  )) AND
  b.NKUNDEKEY = a.NKUNDEKEY
ORDER BY
  a.nkvkey DESC ;

Hallo nochmals:

Ich glaube es gefunden zu haben:

Und zwar folgendes Statemet:

EXPLAIN ANALYZE
 SELECT
                  nkvkey
                FROM
                  t_kv
                WHERE
                  nkvkey NOT IN (
                                  SELECT
                                    nkvkey
                                  FROM
                                    t_kvpos );

ergibt folgende Rückgabe


QUERY PLAN
Seq Scan on t_kv  (cost=9297.77..89926416.00 rows=44261 width=4) (actual time=97.415..654160.326 rows=104 loops=1)
  Filter: (NOT (SubPlan 1))
  SubPlan 1
    ->  Materialize  (cost=9297.77..11019.74 rows=123797 width=4) (actual time=0.002..7.463 rows=62985 loops=44261)
          ->  Seq Scan on t_kvpos  (cost=0.00..8689.97 rows=123797 width=4) (actual time=0.003..43.611 rows=123797 loops=1)
Total runtime: 654160.991 ms

In der Tabelle t_kvpos gibt es 123797 Datensätze. Obiges Statement dauert dann kanpp 11 minuten…

Wie kann ich das schneller hinbekommen?

Ziele der obigen Abfrage ist es, die Datensätze aus der Tabelle T_KV rauszufinden, die keine Datensätze in der 1:N Tabelle T_KVPOS haben…



->Seq Scan on t_kv  (cost=9297.77..89926416.00 rows=44261 width=4) (actual time=92.802..667813.198 rows=104 loops=1)

Was mir auffällt: in den Subselects sind etliche CASTs, die möglicherweise die Benutzung von Indexen verhindern. Du solltest versuchen, diese Subselects zu beschleunigen.

Andreas

Danke für die schnelle Antwort.

Die Subselects zu beschleunigen sind (noch) vernachlässigbar. Denn wenn ich aus dem riesigen Statement den folgenden Teil entferne:

SELECT
                  nkvkey
                FROM
                  t_kv
                WHERE
                  nkvkey NOT IN (
                                  SELECT
                                    nkvkey
                                  FROM
                                    t_kvpos );

…dauert die Abfrage nur noch 1,25 sekunden. Das ist absolut in Ordnung!!!
Mit der Abfrage jedoch dauert es 5 minuten. Das wäre nicht OK.

Gibt es einen besseren Weg rauszufinden, welche Kaufverträge keine Positionen haben oder allgemeiner:

Wie ermittle ich schnell welche Datensätze aus einer “Elterntabelle” keine Einträge in einer “Kindertabelle” haben.

Du könnest schauen, ob folgendes schneller geht. Demo mit ähnlichen Tabellen:

test=*# select * from a;
 i
---
 1
 2
 3
 4
 5
 6
 7
 8
 9
(9 rows)

test=*# select * from b;
 i
---
 1
 3
 5
 7
(4 rows)

test=*# select a.i from a except select distinct i from b;
 i
---
 8
 4
 9
 6
 2
(5 rows)

-- oder

test=*# select a.i from a left join b on (a.i=b.i) where b.i is null;
 i                                                                   
---                                                                  
 2                                                                   
 4                                                                   
 6                                                                   
 8                                                                   
 9                                                                   
(5 rows)

Teste das halt mal unter Deinen Bedingungen. Hast Du einen Index auf t_kv.nkvkey ?

Andreas

Gott sei Dank!!!

Folgendes bewirkt eine Verringerung von 11 minuten auf 2,157 sekunden:

select a.i from a except select distinct i from b;

Ich bin dir unendlich Dankbar → Kommenden Montag haben wir Umstellung auf einen Neukunden. Wenn der hätte 11 minuten warten müssen, um seine Kaufverträge zu sehen…

Wohin darf ich meine Rechnung schicken?

Andreas

Wenn du mal ein kleines Progrämmchen brauchst, sag einfach mal bescheid…

.oO( nicht die Antwort, die ich erhofft hatte )

Andreas