Meine Indexe werden nicht verwendet...

Ich habe folgende Struktur:
zu einem Kunden können 3 Adressen gehören (Allgemeine, rechnungs und Lieferadresse)

T_KUNDE hat die Felder nadressekey, nadressergkey, nadresselikey in T_ADRESSE wäre das jedesmal nadressekey
Zudem gibts in T_ADRESSE ein SNAME1

In meiner Tabelle sind nun ca. 270000 Daten.

Die Suche dauert trotzdem zu lange… (ca 10 sekunden)

Die Datenbank habe ich auf einem Linuxserver und dort habe ich auch die Abfrage ausgeführt…
suche ich nun den Kunden “SCHAEFER” ergibt sich folgendes:


Sort  (cost=286874.52..286877.13 rows=1044 width=749)
  Sort Key: a.skundeident
  ->  Hash Join  (cost=176480.51..286822.17 rows=1044 width=749)
        Hash Cond: ("outer".nadressekey = "inner".nadressekey)
        ->  Hash Left Join  (cost=122340.03..231596.29 rows=208728 width=550)
              Hash Cond: ("outer".nadressergkey = "inner".nadressekey)
              ->  Hash Left Join  (cost=61170.01..140653.91 rows=208728 width=351)
                    Hash Cond: ("outer".nadresselikey = "inner".nadressekey)
                    ->  Seq Scan on t_kunde a  (cost=0.00..41049.10 rows=208728 width=152)
                          Filter: (nmandantkey = 1)
                    ->  Hash  (cost=52749.01..52749.01 rows=277601 width=199)
                          ->  Seq Scan on t_adresse c  (cost=0.00..52749.01 rows=277601 width=199)
              ->  Hash  (cost=52749.01..52749.01 rows=277601 width=199)
                    ->  Seq Scan on t_adresse d  (cost=0.00..52749.01 rows=277601 width=199)
        ->  Hash  (cost=54137.01..54137.01 rows=1388 width=199)
              ->  Seq Scan on t_adresse b  (cost=0.00..54137.01 rows=1388 width=199)
                    Filter: (upper((sname1)::text) ~~ 'SCHAEFER'::text)

Das Statement ist folgendes:

SELECT IntToChar(a.ROWVERSION),
       a.NKUNDEKEY,
       a.AENDERUNGSDATUM,
       a.BEARBEITER,
       a.ERFASSER,
       a.ERFASSUNGSDATUM,
       a.NADRESSEKEY,
       a.NADRESSELIKEY,
       a.NADRESSERGKEY,
       a.NGROSSHANDELRABATT,
       a.NKUNDENGRUPPEKZ,
       a.NMANDANTKEY,
       a.NMITARBEITERKEY,
       a.NZAHLUNGSKONDITIONKEY,
       a.SBANKINSTITUT,
       a.SBLZIDENT,
       a.SDEBITORIDENT,
       a.SGROSSHANDEL,
       a.SKEINEWERBUNG,
       a.SKONTOINHABER,
       a.SKONTONR,
       a.SKUNDEGESPERRT,
       a.SKUNDEIDENT,
       a.SLSCHEINMITPREIS,
       a.SMWSTAUSWEIS,
       a.SMWSTAUSWEISKUNDE,
       a.SUSTIDENT,
       IntToChar(b.ROWVERSION),
       b.NADRESSEKEY,
       b.AENDERUNGSDATUM,
       b.BEARBEITER,
       b.DTGEBURTSTAG,
       b.ERFASSER,
       b.ERFASSUNGSDATUM,
       b.NADRESSARTKZ,
       b.NANREDEKZ,
       b.NBRIEFANREDEKZ,
       b.NETAGE,
       b.NLANDKZ,
       b.NMANDANTKEY,
       b.NTOURIDENTKZ,
       b.NWERBEGEBIDENTKZ,
       b.SAUFZUG,
       b.SCHKSUM,
       b.SEMAIL,
       b.SMOBILTELEFON,
       b.SNAME1,
       b.SNAME2,
       b.SNAME3,
       b.SORT,
       b.SPLZ,
       b.SPLZPF,
       b.SPOSTFACH,
       b.SSTRASSE,
       b.STELEFAX,
       b.STELEFON1,
       b.STELEFON2,
       b.SWEBADRESSE,
       IntToChar(c.ROWVERSION),
       c.NADRESSEKEY,
       c.AENDERUNGSDATUM,
       c.BEARBEITER,
       c.DTGEBURTSTAG,
       c.ERFASSER,
       c.ERFASSUNGSDATUM,
       c.NADRESSARTKZ,
       c.NANREDEKZ,
       c.NBRIEFANREDEKZ,
       c.NETAGE,
       c.NLANDKZ,
       c.NMANDANTKEY,
       c.NTOURIDENTKZ,
       c.NWERBEGEBIDENTKZ,
       c.SAUFZUG,
       c.SCHKSUM,
       c.SEMAIL,
       c.SMOBILTELEFON,
       c.SNAME1,
       c.SNAME2,
       c.SNAME3,
       c.SORT,
       c.SPLZ,
       c.SPLZPF,
       c.SPOSTFACH,
       c.SSTRASSE,
       c.STELEFAX,
       c.STELEFON1,
       c.STELEFON2,
       c.SWEBADRESSE,
       IntToChar(d.ROWVERSION),
       d.NADRESSEKEY,
       d.AENDERUNGSDATUM,
       d.BEARBEITER,
       d.DTGEBURTSTAG,
       d.ERFASSER,
       d.ERFASSUNGSDATUM,
       d.NADRESSARTKZ,
       d.NANREDEKZ,
       d.NBRIEFANREDEKZ,
       d.NETAGE,
       d.NLANDKZ,
       d.NMANDANTKEY,
       d.NTOURIDENTKZ,
       d.NWERBEGEBIDENTKZ,
       d.SAUFZUG,
       d.SCHKSUM,
       d.SEMAIL,
       d.SMOBILTELEFON,
       d.SNAME1,
       d.SNAME2,
       d.SNAME3,
       d.SORT,
       d.SPLZ,
       d.SPLZPF,
       d.SPOSTFACH,
       d.SSTRASSE,
       d.STELEFAX,
       d.STELEFON1,
       d.STELEFON2,
       d.SWEBADRESSE
FROM T_KUNDE a
     left outer join T_ADRESSE c on a.NADRESSELIKEY = c.NADRESSEKEY
     left outer join T_ADRESSE d on a.NADRESSERGKEY = d.NADRESSEKEY,
     T_ADRESSE b
WHERE a.NMANDANTKEY = 1 AND
      (1 = 1 AND
      Upper(b.SNAME1) LIKE 'SCHAEFER') AND
      b.NADRESSEKEY = a.NADRESSEKEY
ORDER BY sKundeIdent

Wie im EXPLAIN zu sehen arbeitet er nur mit “Seq Scan” dabei habe ich auf allen relevanten Feldern einen Index.
Dies wäre im T_KUNDE auf NMANDANTKEY, NADRESSELIKEY, NADRESSERGKEY, NADRESSEKEY
in T_ADRESSE auf NMANDANTKEY, NADRESSEKEY und SNAME1

Warum habe ich keinen “Index Scan”???

Paßt Dein Index auch zur Suchanfrage, hast Du einen funktionalen Index auf SNAME1? Außerdem, Textsuche nach Bruchstücken im Text kann keinen Index nutzen, Du solltest also z.B. mittels ~ ‘^FOOBAR’ suchen.

Paßt Dein Index auch zur Suchanfrage, hast Du einen funktionalen Index auf SNAME1?

Ich habe als Indextyp “btree”, ist es das was du meinst?
Diesen Indextyp habe ich überall…

Hier z.B.:

CREATE INDEX "t_adresse_idxsname1" ON "public"."t_adresse"
  USING btree ("sname1");



Außerdem, Textsuche nach Bruchstücken im Text kann keinen Index nutzen,

Dann sollten aber doch die Joins der Tabellen trotzdem den Index verwenden oder?

Du solltest also z.B. mittels ~ ‘^FOOBAR’ suchen

Ich experementiere damit mal etwas…



test=# \d words;
   Table "public.words"
 Column | Type | Modifiers
--------+------+-----------
 w      | text |

test=# select count(1) from words;
 count
--------
 318924
(1 row)

test=*# explain select * from words where upper(w) ~ upper('^waldarbeiter')::varchar;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on words  (cost=0.00..6454.86 rows=1595 width=13)
   Filter: (upper(w) ~ '^WALDARBEITER'::text)
(2 rows)

test=*# create index idx_words on words(upper(w) using text_pattern_ops);
CREATE INDEX
test=*# analyse words;
ANALYZE
test=*# explain select * from words where upper(w) ~ upper('^waldarbeiter')::varchar;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Index Scan using idx_words on words  (cost=0.01..8.32 rows=1 width=13)
   Index Cond: ((upper(w) ~>=~ 'WALDARBEITER'::text) AND (upper(w) ~<~ 'WALDARBEITES'::text))
   Filter: (upper(w) ~ '^WALDARBEITER'::text)
(3 rows)

test=*# explain select * from words where upper(w) ~ upper('waldarbeiter')::varchar;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on words  (cost=0.00..6454.86 rows=1 width=13)
   Filter: (upper(w) ~ 'WALDARBEITER'::text)
(2 rows)

Die Tabelle ist mittels

iconv -f latin1 -t utf8 /usr/share/dict/ngerman > words.txt

gefüllt.


Andreas

Nein, weil du einen OUTER JOIN verwendest. Der verlangt alle Zeilen aus der einen Tabelle - warum soll man dafür einen Index bemühen?


Ich experementiere damit mal etwas…

Ich kann dir das am Montag im Detail in München erklären g

Ey Caramba…

create index idx_words on words(upper(w) using text_pattern_ops);

Von dem Index habe ich noch nie was gehört… Ist der auch in der PG-Doku?

Ich habe da was mwerkwürdiges rausgefunden…

Bei folgender Abfrage:

SELECT
	*
FROM
	T_KUNDE a
       left outer join T_ADRESSE b on a.NADRESSEKEY = b.NADRESSEKEY
	left outer join T_ADRESSE c on a.NADRESSELIKEY = c.NADRESSEKEY
	left outer join T_ADRESSE d on a.NADRESSERGKEY = d.NADRESSEKEY
WHERE
	a.NMANDANTKEY = 1 AND
	( upper(b.SNAME1) ~ '^SCHAEFER' )

wird der Index verwendet…

So jedoch nicht:

SELECT
	*
FROM
	T_KUNDE a
	left outer join T_ADRESSE c on a.NADRESSELIKEY = c.NADRESSEKEY
	left outer join T_ADRESSE d on a.NADRESSERGKEY = d.NADRESSEKEY
	left outer join T_ADRESSE b on a.NADRESSEKEY = b.NADRESSEKEY
WHERE
	a.NMANDANTKEY = 1 AND
	( upper(b.SNAME1) ~ '^SCHAEFER' )

Kann man sowas auf die schnelle Erklären oder sind das eher grundlegende Dinge… bisher bin ich immer davon ausgegangen, das ich mein SQL-Statement formulieren kann, wie ich es will und das DBMS die optimierung übernimmt… Jetzt bin ich mir da aber gar nicht so sicher…


@ads: Wäre schön wenn du mal in Koblenz (Köln/Bonn) einen Vortrag halten würdest…

@akretschmer:
meine join condition sieht doch so aus:

FROM T_KUNDE a
left outer join T_ADRESSE c on a.NADRESSELIKEY = c.NADRESSEKEY

Das bedeutet doch soviel wie: Wenn es einen eintrag in c für den Eintrag aus a gibt, lade ihn, ansonsten nicht.
Und genau da sollte dann doch der Index ansetzen… Er schaut halt mit Hilfe des keys in der Indextabelle nach und führt eine indizierte Suche aus…

Du bzw. deine Firma können mich ja dahin einladen :wink:

Klar magst du nen Keks? etwas Milch???

Spass beiseite…
Ist davon auszugehen, dass du mal eine Veranstaltung im Koblenzer Raum machst???

PM mich mal wegen eines Angebots, wenn wir dich für 2 oder 3 Tage “mieten” möchten…