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 ;