wieder mal ein index problem

oh mein gott - er koennte ja auch einfach den index nehmen (sort key) … das ende nehmen - oder den anfang - je nach richtung … und mir diesen einen datensatz in null, nix ms liefern …

und nu?

Das sagst du ihm aber nicht.


und nu?

Und nun habe ich keine Lust mehr, deine Fragen zu beantworten, da du uneinsichtig bist. Ändere deine Konfiguration, pass diese an dein System und deine Notwendigkeiten an.
Danach kannst du mit neuen Ergebnissen noch mal nachfragen, vorher habe ich keine Lust, dir alles erneut zu erklären.

natuerlich … limit 1 … und die order expression ist exakt die index expression …
spätestens hier sollte dir auffallen, dass das erste bzw. letzte tupel aus dem index zu ermitteln + aus der table zu lesen sicher keine 8 sekunden wert sein kann …

eher verweigert man mein problem zu verstehen - wahrscheinlich ist es wieder eines der probleme die es garnicht geben darf weil PG alles richtig macht

Hmm - das wollte ich vermeiden - erst kürzlich schriebst du selbst etwas abfällig ?! ueber eine moegliche zerkonfiguration meiner PG installation - die ist noch total jungfräulich - ich will das auch nicht aendern.

Lust hin - lust her - ich hoffte, dass man hier lust hat probleme zu erötern - aber dazu muss man sie erstmal verstehen wollen …

Lust hab ich auch bald keine mehr, mir geht es da nicht anders als ads. Aber vielleicht hilft Dir das ja weiter:

test=*# create table foo (f float);
CREATE TABLE
Zeit: 38,397 ms
test=*# insert into foo select random() from generate_series(1,300000);
INSERT 0 300000
Zeit: 991,728 ms
test=*# create index idx_foo on foo(f);
CREATE INDEX
Zeit: 958,012 ms
test=*# analyse foo;
ANALYZE
Zeit: 27,202 ms
test=*# explain select f from foo order by f limit 10;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Limit  (cost=0.00..0.44 rows=10 width=8)
   ->  Index Scan using idx_foo on foo  (cost=0.00..13111.09 rows=300000 width=8)
(2 Zeilen)

PG nutzt also sehr wohl einen Index, wenn es paßt. Sogar wenn das Select ohne WHERE ist. Ob und evtl. warum bei Dir nicht können wir ohne ein EXPLAIN ANALYSE und dem kompletten SQL und der Index-Definition nur erraten. Dazu hab ich keine Lust mehr.

Andreas

schoen dass PG fuer deinen testfall den index nutzt … meinen nutzt er nicht - obwohl ich exakt die index-expression im order-by verwende … nutze ich diese z.B. in einem where - dann wird der index durchaus verwendet, nur nicht in meinem oben beschriebenen fall … wenn er dich denn interessieren wuerde, haettest du bereits mitbekommen, dass es sich um eine index-expression handelt - mit einem lower(feld) varchar_pattern_ops … nach genau lower(feld) versuch ich dann auch zu sortieren …

ist aber auch egal - offenbar habt ihr einfach auch keinen schimmer woran es liegt - nur mal wieder muffensausen, dass irgendwas mit dem heiligen PG nicht in ordnung sein koennte …

ich denke naemlich, dass auch dir inzwischen aufgefallen sein muesste, dass eure hinweise, “aufwand den index zu lesen zu aufwand die table zu lesen” spaetestens bei limit 1 und sortierung der index vorrang haben muesste, da PG wissen sollte, dass er nur am anfang oder ende des indexes schauen muesste welches tupel dort steht … dafuer sind 8 sek einfach nicht gerechtfertigt …

ich hatte nicht vor hier rumzuflamen - aber ihr unterstellt mir einfach unruehmliches - das wohl eher an anderen ufern zu suchen waere …

einerseits empfiehlt man in der config rumzukritzeln - dann ist das plötzlich wieder total pfui - und wenn ihr nicht weiter wisst fangt ihr an dumme sprueche zu klopfen …

von dir AK war sowas ja zu erwarten - von ADS bin ich etwas enttaeuscht

viel spass noch …

Hallo,

um wieder etwas sachlicher zu werden…

Ich glaube dein Problem liegt in der Indexerstellung mit varchar_pattern_ops.

Aus dem sehr feinen Manual(http://www.postgresql.org/docs/8.3/interactive/indexes-opclass.html):

[…]
*

The operator classes text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops, and name_pattern_ops support B-tree indexes on the types text, varchar, char, and name, respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the server does not use the standard “C” locale. As an example, you might index a varchar column like this:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

Note that you should also create an index with the default operator class if you want queries involving ordinary comparisons to use an index. Such queries cannot use the xxx_pattern_ops operator classes. It is allowed to create multiple indexes on the same column with different operator classes. If you do use the C locale, you do not need the xxx_pattern_ops operator classes, because an index with the default operator class is usable for pattern-matching queries in the C locale.

[…]

Zu beachten ist der 2. Abschnitt.

Getestet habe ich das Ganze mit der Testdb dellstore2 aus dem Sample Databases Project auf pgfoundry(PgFoundry: Sample Databases: Project Info)
Hier habe ich die Tabelle customers benutzt und entsprechende Indize auf die Spalte firstname gesetzt.

Leider wissen wir NICHTS über deine Tabellenstruktur oder dein query, sodaß es bei dir trotzdem etwas gaaanz anderes sein kann.

HTH und ruhig Blut
hkocam

da bin ich dabei

da glaubst du richtig … habe deinen gedanken mal verfolgt … und unter ORDER BY in der Doku findet sich ganz unten - da wo ich meist schon 2 Bier getrunken habe (ich lese langsam) noch eine bemerkung:

Actually, PostgreSQL uses the default B-tree operator class for the expression’s data type to determine the sort ordering for ASC and DESC. Conventionally, data types will be set up so that the < and > operators correspond to this sort ordering, but a user-defined data type’s designer could choose to do something different.
was zunächst mal mein Problem erklärt.

ich glaube ich habe sehr oft geschrieben, dass es sich um eine triviale tabelle handelt und die abfrage ohne joins / ohne where-clause auskommt - es ging allein um die sortierung - die den index misachtet - kostenbetrachtungen halfen da nicht weiter - es war voellig unlogisch, dass es billiger sein soll, 300.000 tupels zu “sortieren” als den index zu laden - wo man vor dem Fetchen noch annehmen darf, dass der client eh nicht alle “braucht” …

jetzt kann ich erstmal ein wenig weiter experimentieren …

danke

Hi,

du hast aber an einer Stelle ein entscheidendes DETAIL angegeben, eben die genaue Angabe zur Indexerzeugung.
Wenn du da auch nur gesagt (und nicht gezeigt) hättest, das du einen Index auf lower(field) gesetzt hast, hätte ich dir nicht helfen können.

Daher ist es so wichtig immer das komplete Szenario darzustellen und eben am besten mit dem vollständigen Code den man benutzt hat.
Dazu müssen auch keine sensiblen Daten deines Kunden rausgerückt werden, da du ein auf das nötigste reduzierte Beispiel deines Problemfalles dann mit öffentlichen Beispieldaten darstellst.
In den meisten Fällen sind es eben nicht die Daten, die relvant bei einem solchen Problem sind.

Es hätte mich auch sehr gewundert, wenn du da tatsächlich eine Bug gefunden hättest, die Userbasis von PG ist tatsächlich größer als man meint, da wäre so ein Faixpas sicher aufgefallen.
Dein Usecase (case-insensitiver Text) ist sogar so populär, dass in der kommenden Version 8.4 wahrscheinlich ein eigener Datentyp entweder im Core oder als contrib-Modul zur Verfügung gestellt wird, siehe Re: PATCH: CITEXT 2.0 v3


Gerne :~)

Es gab hier ja noch das Thema Standard-Konfiguration:
Es gibt da ein paar Dos und Don’t s.
Du solltest z.B. nur wenn du wirklich weißt was du tust an den Kostenparametern(z.B. random_page_cost) des Planners herumspielen.
Andererseits solltest du die Parameter die die Speicherverwaltung von PG steuern auf einem modernen System unbedingt anpassen.

Eine recht aktuelle Anleitung zu dem Thema gibt es hier:
Tuning Your PostgreSQL Server - PostgreSQL Wiki

Weiterhin viel Spass mit dem (wirklich) fortschrittlichsten OpenSource-DBMS der Welt

hkocam

na das lower ist hier nicht so entscheidend … ok der rueckgabetyp von lower ist text … wenn ich lust zu frickeln haette, koennte ich in der order-expression den typ natuerlich auf varchar casten … dann muss ich diesen quatsch aber sicher auch in meinem order-by so angeben - was ich wieder schrecklich finden werde :slight_smile: das ding ist einfach nur, dass order-by die default-operator-classes verwendet … und für text scheint < bzw > nicht zu greifen (also den index) … ich hab irgendwo aber tatsaechlich ergoogelt, dass das wohl ein bug ? fuer 8.3 ist weils wohl in 8.4 wo man dran arbeitet WIEDER funktionieren soll … hmm … so sicher bin ich mir da aber nicht … also wenn ich den index als text_pattern_ops erzeuge, greift der index auch nicht - obwohl der return-typ von lower eben text ist … hmm das muss ich mal verfolgen

kannst ja mal probieren … index mit text_pattern_ops anlegen … lt. order by doku sollte dieses ja die default operator-classes fuer die index-expression zutreffen - also text - und demnach der index bei order by verwendet werden … is aber nicht so … hmm … es sei denn ich vertu mich … und obiges text_pattern_ops ist nicht default des typs text :slight_smile:

wir werden sehen …
es ist gut, dass ich nun das problem kenne - nicht gut ist aber noch keine zufriedenstellende loesung zu haben - in den sauren apfel zu beissen, mir einen zweiten index - nur wegen der sortierung zu erzeugen mag ich im moment noch nicht …

ich denke mit diesem operator-klassen unsinn (den ich aber nicht komplett verteufeln mag) hat man keinen so guten griff getan … ich stolper da nicht zum ersten mal drueber - andere wohl auch nicht - vielleicht muss das noch ein bisschen eleganter gedeien … so dass man wenigsten bei standard-szenarios wie meinen nicht auf die schnauze faellt.

sach ich doch :slight_smile:

mit verlaub - das macht mir angst :slight_smile:

ich moechte aus wirtschaftlichen gruenden gaenzlich drauf verzichten … wenn der client die parameter aendern kann (locale, charset etc.) - gern - solche werden aber sicher nicht ueber den client steuerbar sein - macht ja auch wenig sinn … fuer mich ist der ideal-fall, wenn ich dem kunden sagen kann - downloaden sie version x.y und installieren sie standard (wenn sie es nicht besser wissen) … fertig …

eben - wenn man mit dem unsinn einmal anfaengt, weiss man nicht mehr wo es aufhoert … am anfang ist es ein parameter … dann ja nur 2 und irgendwann weiss man nicht mehr warum wie irgendwas funktioniert - bzw. nicht.

unbedingt sicher nicht :slight_smile: das ueberlass ich leuten die da lust drauf haben - meine aufgabe sehe ich darin das modell so zu gestalten, dass man ergonomisch mit der software arbeiten kann … solange mir das rumgefriemel nicht irgendwer explizit zahlt wird auch nix gefriemelt. … wie ich schon sagte - seitens des clients helfe ich dem PG gern mal beim “richtig funktionieren” nach … sollte es nicht zu schlim werden … ein bisschen auf platten-platz muss ich auch schauen - zuviele indexe bremsen auch nur … es gibt viele rahmenbedingungen die darueber entscheiden ob eine moegliche loesung uebernommen wird … das hier ist noch nicht geloest

aber auf jeden fall schau ich mir das mal an :wink: man sollte den Feind kennen g

ach hoer doch auf … das beste an der welt ist die wirklichkeit - denn nirgends gibts so gute steaks

Hi,

da habe ich mich wohl missverständlich ausgedrückt, ich meinte die Ntzung einer anderen als der Defaultoperator-Klasse.
Die ist in deinem “CREATE INDEX”-Statement deutlich zu sehen.

Da wäre ein Link interessant.

Freundliche Grüße
hkocam

z.B.

8.3 planner ignore index with text_pattern_ops for eq - pgsql.hackers | Google Groups

so ganz bin ich mit meiner interpretation nicht am ende … da gehts um equals … ich sehe das aber als hinweis - parallele zu meinem problem

Hi,

jep das ist genau das Problem.

In 8.4 wirst du dein Problem nicht mehr haben, vorher war es aber anscheinend aber auch nicht möglich, anders vorzugehen.

[…]
This
optimization was not possible when the pattern opclasses were first introduced,
because we didn’t insist that text equality meant bitwise equality; but we
do now,…
[…]

Als Bug würde ich das Ganze aber auch nicht bezeichnen, eher “Nicht Feature-komplett”.
Oder hattest du Datenverlust durch das Verhalten ?

Neue Feature gibt es bei PG übrigens nur bei einer neuen Major-Version (8.3 => 8.4), d.h. in 8.3.4 wird es nicht enthalten sein.

Freundliche Grüße
hkocam

ob ich das jetzt als bug bezeichne oder nicht hilft mir ja auch nicht weiter … die flames kennt man ja aus der OSS Szenerie - da wird gern alles irgendwie gerechtfertigt, damits nicht so schlimm da steht - ein kritischer fehler ists auf keinen fall … als “feature” wuerd ichs aber auch nicht bezeichen wollen :wink:

ja freilich 8.4 drauf warten muss ich ja nicht … leider ist das thema operator classes etwas spärlich dokumentiert … zumindest aus der perspektive eines DB-Entwicklers … weils ja eigentlich auch eher ein thema für system-entwickler ist / sein sollte.

ich haette ja nun vermutet, dass text_pattern_ops die default opclass fuer typ text ist … bei typ text hab ich auch keine info gefunden. mir ist noch etwas unklar, ob eine “default-opclass” nun einen greifbaren bezeichner hat, oder einfach nur “das ist” was pg halt so macht, wenn man keine opclass bei einem index definiert.

meine idee war naemlich, einfach den index mal mit text_pattern_ops zu erzeugen, weil lower(…) ja als rueckgabe-typ text hat - und ich annahm, dass text_pattern_ops der default ist … aber das brachte aber auch nicht den gewuennschten effekt.

sehr wahrscheinlich bleibt mir (bis mind. 8.4) tatsaechlch nur, 2 indexe zu benutzen, damit sowohl oder by als auch where indiziert ist …

meinst du es koennte sich lohnen den entwicklern anzumerken, dass “das problem” auch bei anderen operatoren auftritt <> … weil order by ist ja nicht der equal-operator … und deren kommentar bezog sich ja nur da drauf …

danke nochmal

Hi,

die Frage ist ja ob du tatsächlich die Abfrage ohne where-Klausel wirlich brauchst.
Du kanst auch überprüfen, ob du mit einem Cursor einen anderen Plan bekommst, PG achtet da schon darauf, ob du deine Daten direkt haben willst(kein cursor ) oder nacheinder verarbeitest(mit cursor).
Ich weiß aber nicht, ob das schon in 8.3 ist oder erst in 8.4.
Fraglich ist aber , ob es hier tatsächlich einen anderen Plan gibt, wenn der Index nicht genutzt werden kann.

Eine andere Alternative wäre es, da deine Tabelle ja recht breit zu sein scheint, die Spalten die du nicht brauchst auszulagern.

Damit reduzierst du die Menge an Daten die PG dann bei einem SeqScan lesen muß.
Wäre das eine Möglichkeit für dich ?

Freundliche Grüße

hkocam

ich nicht … aber die leidliche kundschaft :slight_smile: da ist es ueblich, irgendwelche listen öffnen zu wollen - 10 datensaetze oder 10 mio - voellig egal - da will man dann auch nach allen moeglichen sachen sortieren … ich hab da gerade ein mini-projekt … bei dem ich mir mal dachte, dass ich sowas fuer die handvoll tabellen erlaube mit nem limit 10.000 oder sowas … nur das limit brachte ja auch nix :slight_smile: … ich moecht aber schon nicht ganz so arg mit beliebig vielen indizes rumsauen … die listen sind virtuell - zeigen also nur was man sieht … scrollen muss man natuerlich auch rueckwaerts können … cursor hab ich auch schon in erwägung gezogen … aber noch nicht probiert - weil ich dann mein framework erweitern muesste … aber denkbar ist das. es wuerde ja nicht schlechter, wenn es “mehr” kann - halte es aber fuer eher unwahrscheinlich, da die doku bei order by auf den default operator verweist - das wird der planner ja auch bei cursors nicht anders handhaben … entweder die expression “passt” oder eben bloeder weise nicht - wie hier.

ne sie sind nicht wirklich breit … 10 spalten zu 10 bis maximal 40 zeichen … sie sind eher REL. lang … stammdaten mit 300k datensaetzen … der seq. scan ist ja flott genug - aber ansich nicht akzeptabel … um mal flott die ersten zeilen der liste zu zeigen … auch wenn die user spaeter sicher nie ungefiltert rumscrollen werden - hoere ich sie schon fragen, warum man da nicht scrollen kann, wenn der scroll-balken fehler lach

fuer solchen unfug (weniger support) mach ich mir die muehe