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 ?
Danke schonmal
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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
Geändert von symbiont (17.03.2008 um 13:16 Uhr)
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
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
zunächst Danke für Deine Mühe,
nunalso brechen wir den "flame" hier mal ab ..
- 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
- 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.
- funktioniert der/ein Index wenn ich diesen nicht als Ausdruck verwende - also ohne lower(..) - dann gehts
- 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
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
Geändert von symbiont (17.03.2008 um 14:30 Uhr)
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:
Die Tabelle enthält schlicht die Wörter eines Wörterbuches.Code: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
Dann sollte er allerdings greifen. Hast Du UTF8 oder sowas?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.
Andreas
--
https://www.xing.com/profile/Andreas_Kretschmer7ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
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: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.
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.Code: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)
Gut, erzwingen wir die Nutzung des Index:
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).Code: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)
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
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
den kostenbasierten Optimizer habe ich verstanden - nur hilft mir die Milchmädchenrechnungauch nicht weiter ..
nehmen wir mal diese Query ..
jede Menge "Hilfe" von mir und offensichtliche Anlässe einen Index zu benutzen ..Code:select T."textkey" from archiv9."TX_TEXTKEYS" As T where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^' order by LOWER(T."textkey") desc
nichts Dergleichen passiert:
und nun kommst DuCode: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)
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![]()
Mein Beispiel hatte schon einen Sinn. Probier einfach mal, was als EXPLAIN Ergebnis herauskommt, wenn du den Index mit:
erzwingst.Code:SET enable_seqscan=0;
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
Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler
PostgreSQL Service & Support
Links: Tobias Bauer - IT- und Funkdienstleistungen - Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen - Funkforum
Lesezeichen