Index nicht verwendet

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 ? :astonished:

Danke schonmal

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

Andreas

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)
---- 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)
---- NACHTRAG ----
Dank schonmal

Gruß
Steffen
p.s. ein analye bzw vacuum hab ich auch schon auf die db/tabelle losgelassen - alles ohne erfolg … irgendwie seltsam

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

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 :wink:
  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. :angry:
  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

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:

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

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. :slight_smile:

Zurück zum Problem …

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

CREATE DATABASE myDB
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default;

Hilft uns / mir das weiter ?

Danke
Steffen

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.

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:

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.

den kostenbasierten Optimizer habe ich verstanden - nur hilft mir die Milchmädchenrechnung :wink: auch nicht weiter …

nehmen wir mal diese Query …

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:

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 :slight_smile:
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 :sunglasses:

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

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.

hab ich gemacht - und schwups - PG nimmt den Index …

leider kann ich dir den Plan nicht zeigen, da ich nun zu hause bin - und die DB nur in der Fma ist - allerdings erinnere ich mich, dass die Query danach auch nicht wirklich “schneller” war - (ca 2 sek) und das bei einem Resultset von gerade mal 11 Datensaetzen … was mich auch nicht befriedigt. Das koennte allerdings wirklich an der unguenstigen Konstellation meiner Testdaten liegen.

Dem Tip mit den Schwellwerten werde ich nachgehen.

Sonst noch irgendwelche Ideen ?

Danke
Steffen

p.s. werde morgen mal das explain fuer den 2. Fall siehe oben hier posten

Zeig erst mal die EXPLAIN Ausgabe, dann vergleichen wir mal, was wirklich ausgeführt wird.

Ach, und bezüglich Testdaten: wenn du meinst, das diese ungünstig sind, der Query Plan wird sich mit der Änderung deiner Daten auch ändern. Also würde ich an deiner Stelle reale Daten nutzen, nicht irgendwelche unzureichenden Testdaten.

Moin moin allerseits …

hier wie angekündigt mit erzwungener Index-Nutzung der Plan

myDB=# set enable_seqscan=0;
SET
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
myDB-# order by LOWER(T."textkey") desc;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using idx_textkey on "TX_TEXTKEYS" t  (cost=0.00..60904.98 rows=5000 width=14) (actual time=1900.755..1947.263 rows=11 loops=1)
   Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 1947.352 ms
(3 Zeilen)

Wenn ich mal davon ausgehe, das “rows” die geschaetzte Groesse des Resultset angibt, dann liegt er damit recht weit daneben - die Query ergibt nur 11 Reihen.

Ich gehe nicht mehr davon aus, dass die DB mist macht, aber ich wuerde gern verstehen wo ich schrauben muss / kann, damit es besser wird. Dazu sei folgendes erwähnt, das Feld “textkey” nach dem hier gesucht wird, ist 255 Zeichen lang - was VIELLEICHT auch nicht so günstig für einen Index ist - Granularität. Das könnte ich sicherlich halbieren - würde ich aber nicht, wenn ihr mir sagt, dass es daran nicht liegt.

Gibt es irgendwo ein Dokument in dem mal ausführlich beschrieben wird, wie die Ausgabe des Plans zu interpretieren ist ? Das was man unter explain bzw. explain analyse findet ist sehr sehr spärlich.

Danke
Steffen

Korrekt verstanden. Hast Du mal analyse gemacht?



Gibt es irgendwo ein Dokument in dem mal ausführlich beschrieben wird, wie die Ausgabe des Plans zu interpretieren ist ? Das was man unter explain bzw. explain analyse findet ist sehr sehr spärlich.

Danke
Steffen

Google mal nach ‘explaining explain’, das gibt es ein PDF. Du kannst auch bei mit bei del.icio.us schauen, findet sich vielleicht auch was.

Andreas

Also folgenden Plan erhalte ich nach einem analyse, allerdings habe ich inzwischen mal die Breite der betroffenen Spalte auf 80 Zeichen reduziert.

myDB=# analyse myDB."TX_TEXTKEYS";
ANALYZE
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
myDB-# order by LOWER(T."textkey") desc;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using idx_textkey on "TX_TEXTKEYS" t  (cost=0.00..47667.87 rows=1 width=14) (actual time=2419.434..2478.060 rows=11 loops=1)
   Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 2478.109 ms
(3 Zeilen)

Weils gerade so spannend ist, habe ich nun die Spaltenbreite wieder auf 255 Zeichen erhöht - ein analyse gefahren und nun sieht das Ergebnis so aus (also genauso):

myDB=# analyse myDB."TX_TEXTKEYS";
ANALYZE
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
archiv9-# order by LOWER(T."textkey") desc;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using idx_textkey on "TX_TEXTKEYS" t  (cost=0.00..48563.38 rows=1 width=14) (actual time=2369.449..2429.085 rows=11 loops=1)
   Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 2429.130 ms
(3 Zeilen)

Zunächst noch Dank für den Hinweis zur Doku - aber noch eine Frage … (bevor ich gleich selbst nachschaue) … unten steht “Total runtime” - ist dies die benötigte Zeit für den Plan? Wenn ja - wird genau dieser Plan auch immer erstellt, wenn die Query ausgeführt wird? Das wäre natürlich dann auch eine Erklärung für die doch enorme Latenz.

Gruß & Dank
Steffen

Erst duch das analyse weiß PG, was für statistische Dinge zu beachten sind.

Zunächst noch Dank für den Hinweis zur Doku - aber noch eine Frage … (bevor ich gleich selbst nachschaue) … unten steht “Total runtime” - ist dies die benötigte Zeit für den Plan? Wenn ja - wird genau dieser Plan auch immer erstellt, wenn die Query ausgeführt wird? Das wäre natürlich dann auch eine Erklärung für die doch enorme Latenz.

Gruß & Dank
Steffen

‘explain analyse’ erstellt den Plan und führt die Abfrage aus. Die Planerstellung ist dabei nur ein Bruchteil der Zeit.
‘explain’ ohne anallyse erstellt lediglich den Plan. Der Vorteil, wenn man ‘analyse’ macht, ist, daß man sowohl geschätzte Werte für die Größe des Resultates als auch reale Werte bekommt. Wenn 5000 geschätzt werden und real nur 11 Datensätze kommen, ist das dann ein recht deutlicher Hinweis auf ein fehlendes analyse der table.

Kompliziert? Nicht wirklich…

Andreas

ja das habe ich dann auch noch gesehen, dass da “actual” steht und wohl die reelle Abfrage gemeint sein muss …

ich hatte wohl ein vacuum gestartet und meinte damit eigentlich das analyse … naja passiert …

wie dem auch sei - ich bin immernoch nicht gluecklich …
nun - nach dem analyse - habe ich das enable_seqscan mal wieder auf 1 gestellt, und wenn man die beiden Plans vergleicht sieht man, dass er den Sequence-Scan tatsächlich performanter schaetzt und damit auch richtig liegt. Warum er allerdings richtig liegt, bleibt mir noch verborgen. Ich wüsste nicht, warum ein Index-Lookup in 1 Mio Zeilen, bei der ich 1 Zeile als Ergebnis erwarte “langsamer” sein soll, als danach zu scannen. Naja gut - wenn man die Index-Daten erst “öffnen” müsste - das kann doch aber nicht wirklich so teuer sein ?! :confused:
Vielleicht sind ja PG-Indices nicht besonders glücklich implementiert. :smiley:

Der braucht wirklich für die 11 Zeilen die im Endeffekt das Ergebnis darstellen ca. 2 sek. (Irgend ein Pentium D Maschinchen mit ausreichend Ram und RedHat-Linux) Auch wenn alles schon im Cache ist.

myDB=# set enable_seqscan=1;
SET
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
myDB-# order by LOWER(T."textkey") desc;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=21369.25..21369.26 rows=1 width=14) (actual time=1561.537..1561.543 rows=11 loops=1)
   Sort Key: lower((textkey)::text)
   ->  Seq Scan on "TX_TEXTKEYS" t  (cost=0.00..21369.24 rows=1 width=14) (actual time=19.174..1561.320 rows=11 loops=1)
         Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 1561.600 ms
(5 Zeilen)

Nochmal ohne seqscan unmittelbar danach:

myDB=# set enable_seqscan=0;
SET
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
myDB-# order by LOWER(T."textkey") desc;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan Backward using idx_textkey on "TX_TEXTKEYS" t  (cost=0.00..48563.38 rows=1 width=14) (actual time=2397.561..2458.389 rows=11 loops=1)
   Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 2458.436 ms
(3 Zeilen)

Wahrscheinlich muss ich mich damit erstmal abfinden … oder?

Gruß & Dank
Steffen

Nicht unbedingt. Ich habe keine echte Anwendung, wo man extensiv in Texten suchen muß, von daher auch nicht so dolle viel Erfahrung damit, aber falls Du das mit der Textsuche wirklich brauchst, dann solltest Du Dich mit tsearch beschäftigen. Das bringt da einiges an Funktionalität - und Geschwindigkeit.

Falls man textsuche nicht wirklich braucht, sollte man sie vermeiden. Für Schlüsselfelder etc. sind INT-Werte deutlich angenehmer.

Andreas

es handelt sich dabei um “Anwendungsbezogene” Schlüssel die als String dargestellt werden. D.h. z.B. ein Java-Klassename an dem dann weitere Informationen (referenziert) haengen koennen … ueber diese textkey tabelle werden solche Namen quasi auf int-keys gemapped - mit dem sich dann selbstverstaendlich besser in der DB arbeiten laesst. Dieser textkey hier ist allerdings der Einstieg - sicherlich werden da keine 1 mio Datensaetze erwartet - eher paar tausend.

Das Projekt steht allerdings am Anfang und da habe ich die gelegenheit gleich mal genutzt und naiv drauf los probiert - paar Werte generiert - und geschaut wie es reagiert - und wie das Forum reagiert - bin zufrieden mit euch - :wink:

allerdings steht das problem “extensiv” in Texten zu suchen noch aus - auch in großen Textfeldern

Gruß
Steffen

Wenn ich mir diesen Beitrag so anschaue, erwartet PG einen Wert zu finden (rows=1) und findet 11 (rows=11), oder? Kommt also dem sehr nahe, was du effektiv haben willst.

Bleibt die Frage, warum trotzdem ein sequentieller Scan gewählt wird.
Kannst du mal den Namen des Index herausfinden und dann die aktuelle Größe dieses Index bestimmen? \d tabellenname sollte dir die ganzen Indexnamen liefern, hier findest du Informationen, wie man die Grö0e bestimmt.

Ich gehe davon aus, das der Index so groß geworden ist, das es sich für PostgreSQL auch nicht mehr lohnt, den Index zu laden - weil ungefähr die selbe Menge Daten auch bei einem sequentiellen Scan anfallen würden. Das Problem könnte also sein, das du zu viele Daten in einem Index hast - und dieser nicht mehr effektiv duchsucht werden kann.