Index nicht verwendet

Fragen und Probleme rund um die Abfragespache SQL.

Index nicht verwendet

Beitragvon symbiont » Mo 17. Mär 2008, 12:55

Hallo allerseits ..

ich habe das Problem, dass in einer meiner Abfragen ein "berechneter Index" nicht verwendet wird.

Die betroffene Tabelle hat nur wenige Felder (2) - und selbst in einer einfachen Abfrage mit like >, >= oder = wird der folgende Index einfach nicht angerührt:

CREATE UNIQUE INDEX idx_textkey
ON myschema."TX_TEXTKEYS"
USING btree
(lower(textkey::text) varchar_pattern_ops);

Eine solche Abfrage sieht beispielsweise so aus:
SELECT
T."id_tky" As Id,
T."textkey"
FROM myschema."TX_TEXTKEYS" As T
WHERE LOWER(T."textkey") like 'test%'


"explain" liefert
Seq Scan on "TX_TEXTKEYS" t (cost ....
Filter: (lower((textkey):text ~~ 'test%'::text)


Ich habe mich bereits durch die Doku von PG gewuehlt und laut dieser habe ich alles richtig gemacht - ich habe auch in diesem Forum schon Beiträge gelesen mit ähnlichem Problem - aber auch die bringen mich nicht weiter ..

any idea ?
Optimizer schuld ?
Ich schuld ?
Ihr schuld ? :o

Danke schonmal
symbiont
Erfahrener Benutzer
 
Beiträge: 1328
Registriert: Mo 17. Mär 2008, 12:45
Wohnort: NULL

Beitragvon akretschmer » Mo 17. Mär 2008, 13:21

symbiont hat geschrieben:Hallo allerseits ..

ich habe das Problem, dass in einer meiner Abfragen ein "berechneter Index" nicht verwendet wird.

Die betroffene Tabelle hat nur wenige Felder (2) - und selbst in einer einfachen Abfrage mit like >, >= oder = wird der folgende Index einfach nicht angerührt:



wie viele Zeilen hat die Tabelle? Was sagt explain analyse?

Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5938
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

Beitragvon symbiont » Mo 17. Mär 2008, 13:50

die tabelle hat momentan testhalber 1 mio datensaetze (generiert) die Werte für die spalte "textkey" heissen 'Test_1' bis eben 'Test_999999' sowie ein paar wenigen weiteren Werten die anders aussehen (z.B. wie Java-Klassennamen)


was explain liefert hatte ich bereits in meine OPost geschrieben - oder möchtest Du was anderes wissen ?

aber hier nochmal frisch aus der Console
mydb=# explain select textkey from myschema."TX_TEXTKEYS" where lower(textkey) like 'test%';
QUERY PLAN
-----------------------------------------------------------------------
Seq Scan on "TX_TEXTKEYS" (cost=0.00..21371.72 rows=999915 width=14)
Filter: (lower((textkey)::text) ~~ 'test%'::text)
(2 Zeilen)
[color=Red]---- NACHTRAG ----

explain analyse select textkey from myschema."TX_TEXTKEYS" where lower(textkey) like 'test%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on "TX_TEXTKEYS" (cost=0.00..21371.72 rows=999915 width=14) (actual time=0.069..2284.524 rows=1000000 loops=1)
Filter: (lower((textkey)::text) ~~ 'test%'::text)
Total runtime: 2969.438 ms
(3 Zeilen)
[/color][color=Red]---- NACHTRAG ----
[/color][color=Black]Dank schonmal

Gruß
Steffen
p.s. ein analye bzw vacuum hab ich auch schon auf die db/tabelle losgelassen - alles ohne erfolg ... irgendwie seltsam
[/color]
symbiont
Erfahrener Benutzer
 
Beiträge: 1328
Registriert: Mo 17. Mär 2008, 12:45
Wohnort: NULL

Beitragvon akretschmer » Mo 17. Mär 2008, 14:47

symbiont hat geschrieben:die tabelle hat momentan testhalber 1 mio datensaetze (generiert) die Werte für die spalte "textkey" heissen 'Test_1' bis eben 'Test_999999' sowie ein paar wenigen weiteren Werten die anders aussehen (z.B. wie Java-Klassennamen)



Wenn Du in einer Tabelle mit 1 Million Zeilen nach 'test%' suchst und in 1 Million Zeilen steht 'test%' drin, was glaubst Du soll dann der Index bringen?

Okay, er könnte erst den Index lesen, feststellen, daß er alle Zeilen der Tabelle braucht, und dann diese lesen. Regelbasierte Optimizer sind in der Tat so stunzdumm, das so zu machen. PG ist schlauer. Es weiß anhand seiner Statistiken, daß (nahezu) überall 'test%' drinsteht.

Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5938
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

hoi hoi

Beitragvon symbiont » Mo 17. Mär 2008, 15:20

zunächst Danke für Deine Mühe,

nun
  1. darfst Du gern von nicht-regelbasierten Optimizern fasziniert bleiben, jedoch ist deren Verhalten wie man hier wieder sieht "vorhersehbarer" (regelbasierten Optimizern) - auch wenn es sicher viele Beispiele gibt, bei denen dieser Versagt ;)
  2. versagt hier Postgres - ganz einfach, weil der Index doch bitte nicht nur zum Filtern verwendet werden soll, sondern auch zum Sortieren - selbst ein "order by" hilft diesem nicht auf die Sprünge. :mad:
  3. funktioniert der/ein Index wenn ich diesen nicht als Ausdruck verwende - also ohne lower(..) - dann gehts :confused:
  4. ist die Verwendung eines Index der "irgendwie" passt IMMER einem full-table-scan vorzuziehen. Der "Aufwand" des Index ist doch bereits getan - er existiert - warum sollte die DB den nicht nutzen :rolleyes:
also brechen wir den "flame" hier mal ab ..
zurück zum Problem ..

Dieser Index auch nicht genutzt, wenn ich nach den "weniger häufig vorkommenden" anderen Werten suche, bzw. das Kriterium soeweit einschränke, dass nur noch wenige Prozent der Million Datensaetze im Resultset über wären.

Ich wollte jetzt hier keine Diskussion über die Sinnhaltigkeit dieser Test-Daten anführen.

Irgendeine andere Idee ?

Danke
Steffen
symbiont
Erfahrener Benutzer
 
Beiträge: 1328
Registriert: Mo 17. Mär 2008, 12:45
Wohnort: NULL

Beitragvon akretschmer » Mo 17. Mär 2008, 16:15

symbiont hat geschrieben:zunächst Danke für Deine Mühe,

nun
  1. darfst Du gern von nicht-regelbasierten Optimizern fasziniert bleiben, jedoch ist deren Verhalten wie man hier wieder sieht "vorhersehbarer" (regelbasierten Optimizern) - auch wenn es sicher viele Beispiele gibt, bei denen dieser Versagt ;)
  2. versagt hier Postgres - ganz einfach, weil der Index doch bitte nicht nur zum Filtern verwendet werden soll, sondern auch zum Sortieren - selbst ein "order by" hilft diesem nicht auf die Sprünge. :mad:
  3. funktioniert der/ein Index wenn ich diesen nicht als Ausdruck verwende - also ohne lower(..) - dann gehts :confused:
  4. ist die Verwendung eines Index der "irgendwie" passt IMMER einem full-table-scan vorzuziehen. Der "Aufwand" des Index ist doch bereits getan - er existiert - warum sollte die DB den nicht nutzen :rolleyes:
also brechen wir den "flame" hier mal ab ..
zurück zum Problem ..


Jo.
Erst einmal, der Aufwand bei einem Index ist nicht nur die Erstellung. Auch das Lesen eines Indexfiles ist Aufwand. Um sequentiell zu lesen, wird der Aufwand als 1 angenommen, bei einem Index ist es ein eher zufälliges lesen random_page_cost, und der ist bei 4. Das ist das, was ein kostenbasierter Optimierer macht - er berechnet verschiedene Pläne und deren Kosten. Diese Kosten sieht man auch im Explain.

So, nun mal eine Vergleichstabelle:

Code: Alles auswählen
test=*# select count(1) from words;
 count
--------
 308860
(1 row)

test=*# \d words;
         Table "public.words"
 Column  |      Type      | Modifiers
---------+----------------+-----------
 w       | text           |
 w_array | character(1)[] |
Indexes:
    "idx_words" btree (lower(w) varchar_pattern_ops)

test=*# explain analyse select * from words where lower(w) like 'andreas%';
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on words  (cost=17.27..3946.65 rows=1544 width=133) (actual time=0.213..0.216 rows=1 loops=1)
   Filter: (lower(w) ~~ 'andreas%'::text)
   ->  Bitmap Index Scan on idx_words  (cost=0.00..17.27 rows=1544 width=0) (actual time=0.161..0.161 rows=1 loops=1)
         Index Cond: ((lower(w) ~>=~ 'andreas'::character varying) AND (lower(w) ~<~ 'andreat'::character varying))
 Total runtime: 0.287 ms


Die Tabelle enthält schlicht die Wörter eines Wörterbuches.

Dieser Index auch nicht genutzt, wenn ich nach den "weniger häufig vorkommenden" anderen Werten suche, bzw. das Kriterium soeweit einschränke, dass nur noch wenige Prozent der Million Datensaetze im Resultset über wären.

Dann sollte er allerdings greifen. Hast Du UTF8 oder sowas?

Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5938
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

@utf 8

Beitragvon symbiont » Mo 17. Mär 2008, 17:00

Sicherlich hast Du auch recht - wir alle haben irgendwie immer recht - man lernt nie aus - und ich wollte auch garnicht die Postgres-Paradigma in Frage stellen, da mir das ja auch nicht weiterhelfen würde.

Ich führe eben nur ein paar Fallstudien durch - und da sind Extremfälle wie dieser garnicht unwichtig - ich habe noch nicht so intensiv mit PG gearbeitet (mit anderen DB schon eher) und da möchte man ja gewisse Zweifel aus dem Weg räumen. Denn prinzipiell stellen diese Tabelle / der Index und die Abfrage zusammen, ein sehr einfaches Problem dar, bei dem ich erwartet hätte, dass die Abfrage in NullKommaNix ausgeführt wird. :)

Zurück zum Problem ..

ja .. die DB ist mit UTF8 codierung erzeugt .. da hab ich aber auch kein Ermessensspielraum ..

Code: Alles auswählen
CREATE DATABASE myDB
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default;



Hilft uns / mir das weiter ?

Danke
Steffen
symbiont
Erfahrener Benutzer
 
Beiträge: 1328
Registriert: Mo 17. Mär 2008, 12:45
Wohnort: NULL

Beitragvon ads » Mo 17. Mär 2008, 17:26

Du solltest vielleicht versuchen, auf der rechten Seite deiner Expression auch ein lower() unterzubringen. Ansonsten wird PostgreSQL feststellen, das links einmal Werte mit lower() abgefragt werden, rechts jedoch nicht. Das könnte genügen, um den zugehörigen Index aussen vor zu lassen.

Bleiben wir beim Thema Kosten:

Gegeben sei eine Tabelle, in der so viele Daten sind, das sie ungefähr einen Block der Datenbank auf der Festplatte füllen.

Testen wir mal, ob der Index auf dem Primary Key verwendet wird.
Code: Alles auswählen
postgres=# EXPLAIN SELECT id, inhalt FROM planer_test WHERE id=1;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on planer_test  (cost=0.00..1.06 rows=1 width=18)
   Filter: (id = 1)
(2 rows)
Offensichtlich nicht. Zu sehen sind Kosten in Höhe von geschätzten 1.06 Plattenzugriffen. Das wäre einmal den Block mit den Daten aus einem zufälligen Block (random read) von der Platte lesen plus etwas Kosten drumherum zum Auswerten.


Gut, erzwingen wir die Nutzung des Index:
Code: Alles auswählen
postgres=# SET enable_seqscan=0;
SET
postgres=# EXPLAIN SELECT id FROM planer_test WHERE id=1;
                                      QUERY PLAN                                     
--------------------------------------------------------------------------------------
 Index Scan using planer_test_id_key on planer_test  (cost=0.00..3.01 rows=1 width=4)
   Index Cond: (id = 1)
(2 rows)
Zu sehen sind Kosten von geschätzten 3.01 Plattenzugriffen. Das wäre einmal das Lesen des Index (random read), dann das Umpositionieren des Kopfes und das Lesen des Blocks mit den Daten (noch einmal random read).

An dem Beispiel kann man sehr gut sehen, warum PostgreSQL trotz vorhandenem Index einen sequentiellen Scan wählt: die geschätzten Gesamtkosten der Anfrage sind mit Nutzung des Index 300% höher als ohne. Da die Daten sowieso gelesen werden müssen, kann das Lesen des Index auch entfallen. Die In-Memory Operationen zum Durchsuchen des gelesenen Blocks sind wesentlich billiger als das mehrfache Lesen von der Festplatte, um genau zu wissen, wo im Block man suchen muss.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören Bild

Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

PostgreSQL Service & Support
ads
Erfahrener Benutzer
 
Beiträge: 4147
Registriert: Mi 8. Feb 2006, 11:58
Wohnort: MD

Scho' klar

Beitragvon symbiont » Mo 17. Mär 2008, 17:47

den kostenbasierten Optimizer habe ich verstanden - nur hilft mir die Milchmädchenrechnung ;) auch nicht weiter ..

nehmen wir mal diese Query ..

Code: Alles auswählen
select
    T."textkey"
from archiv9."TX_TEXTKEYS" As T
where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
order by LOWER(T."textkey") desc
jede Menge "Hilfe" von mir und offensichtliche Anlässe einen Index zu benutzen ..

nichts Dergleichen passiert:
Code: Alles auswählen
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=21689.75..21702.25 rows=5000 width=14) (actual time=1482.371..1482.377 rows=11 loops=1)
   Sort Key: lower((textkey)::text)
   ->  Seq Scan on "TX_TEXTKEYS" t  (cost=0.00..21382.56 rows=5000 width=14) (actual time=17.878..1482.305 rows=11 loops=1)
         Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 1482.433 ms
(5 Zeilen)
und nun kommst Du :)
btw - weiss Postgres aus Statistiken, dass die Daten "der reihe nach" gespeichert sind?, dass man quasi "index-mässig" drauf zugreifen kann? Wenn das der Fall ist, sollte ich die Daten vielleicht nochmal etwas "mischen", auch wenn ich irgendwie nicht einsehen will, dass eine Abfrage auf "gemischte" Daten langsamer sein muss, als auf geordnete.

Gruß
Steffen
p.s. ich will PG nicht schlecht machen - nur mein Problem lösen - du musst das nicht rechtfertigen .. was hattest du denn für Ideen als du nach UTF8 gefragt hast ?
p.p.s. ich hoffe ich langweile Dich nicht :cool:
symbiont
Erfahrener Benutzer
 
Beiträge: 1328
Registriert: Mo 17. Mär 2008, 12:45
Wohnort: NULL

Beitragvon ads » Mo 17. Mär 2008, 17:53

Mein Beispiel hatte schon einen Sinn. Probier einfach mal, was als EXPLAIN Ergebnis herauskommt, wenn du den Index mit:

Code: Alles auswählen
SET enable_seqscan=0;

erzwingst.

Wenn der Index dann immer noch nicht genutzt wird, weisst du, das PostgreSQL mit ihm nichts anfangen kann und der dann falsch definiert sein muss. Wenn der danach genutzt wird, sollten sich die Kostenwerte verändern und höher als bei einem sequentiellen Scan liegen.

Also zeig mal bitte die Ausgabe dieses EXPLAINs hier.


Wenn du übrigends der Meinung bist, das der Planer falsch rechnet und der Index für deinen Fall wirklich schneller sein sollte - dann kannst du die Schwellwerte für diverse Fälle anpassen. Dazu wirf dann bitte einen Blick in die Config. Anpassungen wie die Kosten für Plattenzugriffe und für Sortieroperationen sollten die ersten Einstellungen sein, die du anpassen musst.
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören Bild

Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

PostgreSQL Service & Support
ads
Erfahrener Benutzer
 
Beiträge: 4147
Registriert: Mi 8. Feb 2006, 11:58
Wohnort: MD

Nächste

Zurück zu SQL

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 0 Gäste