wieder mal ein index problem

ich habe einen einfachen indexausdruck:

CREATE INDEX … lower(feld) varchar_pattern_ops;

auf einer tabelle …

wenn ich diese nun nach lower(feld) sortiere, sollte man doch annehmen, dass der obige index verwendet wird …

wird aber nicht … oder er nutzt ihn :confused: - aber irgendwas roedelt trotzdem ewig rum …

“Sort (cost=98078.21…98593.10 rows=205957 width=310) (actual time=24077.375…26426.784 rows=205957 loops=1)”
" Sort Key: (lower((feld)::text))"
" Sort Method: external merge Disk: 73768kB"
" -> Seq Scan on tabelle t (cost=0.00…20764.46 rows=205957 width=310) (actual time=0.054…7000.931 rows=205957 loops=1)"
“Total runtime: 26727.489 ms”

any idea ?

danke

Statistiken aktuell?
Was sagt EXPLAIN ANALYZE?

top-aktuell

hab ich oben bereits gepostet

Nur ist das Datenlesen nach 7 Sekunden beendet und das Sortieren beginnt erst nach 20 Sekunden. Fehlt da ein Teil?

Ausserdem braucht dein Sortieren 73 MB Platz, das könnte gut sein, das dies viel zu groß für die eingestellten RAM-Werte ist und daher auf die (langsamere) Platte ausgelagert wird.

Noch ein Hinweis: hast du eine WHERE Bedingung in deiner Anfrage? Wenn nicht, wird der Index sowieso nicht zum Sortieren genutzt: die Daten wurden schon komplett gelesen, wieso noch zusätzlich den Index lesen.

das ist ne einfache tabelle - zugegeben - mit nicht wenig daten … und die abfrage ist ohne bedingungen - kein join - nix … nur ein order by …

hmm

sowohl die 7 sek als auch die 20 sind viel zu lang … normalerweise sollte PG hier nichts grossartig zu lesen haben … er hat auch nix zu sortieren - denn er hat ja nen index zur verfuegung … das resultset sollte sofort da sein

ich muss aber erstmal in den urlaub :slight_smile:

Wenn du keine WHERE Bedingung angibst, wird die komplette Tabelle gelesen. Wieso soll die Datenbank zusätzlich den Index einlesen, wenn die Daten sowieso in den Speicher müssen?

Du kannst das ja mal mit SET enable_seqscan=OFF probieren aber das Ergebnis dürfte noch schlechter ausfallen.

Btw, das Ergebnis dieser Abfrage ist primär I/O-gebunden, wenn dir das also zu langsam ist, leg dir ein schnelleres System zu.

müssen sie doch nicht … das muessen sie erst wenn man sie fetcht - zum “finden” und sortieren muss (wenn der index nutzbar ist) definitiv nix aus der table gelesen werden

ohne WHERE werden alle gesucht, gefunden, sortiert und so weiter.

Andreas

hmm wieso muss ich etwas “suchen” wenn ich keine bedinung habe … oder etwas nochmal sortieren, wenn ich nen index habe, der ja funktionsbedingt ohnehin sortiert ist … hmm … schade irgendwie … ich glaube MySQL is da schlauer :smiley:

Die Alternative wäre, via Index Tupel für Tupel sortiert zu lesen. Da hast Du dann schön für jeden Tupel einen zufälligen Zugriff auf die DB. Das ist NICHT schlauer.

MySQL hat kein MVCC.

Andreas

Wenn ich ohne WHERE lese, muss die DB sowieso alle Daten lesen. Wieso soll sie zusätzlich noch den Index von der Platte lesen und damit noch mehr Zeit vergeuden?

du vergisst, dass ich von einer sortierten datenmenge spreche???!!!

also - index hernehmen und auf basis dieses indexes mir die daten “einfach” liefern sollte (logischer weise) wesentlich effizienter sein, als die tabelle bei der abfrage zu sortieren …

um random access kommt die DB bei sortiertem fetchen ohnehin nicht drumherum … das faellt nicht ins gewicht.

d.h. der kostenfaktor ist die sortiererei … die kann er sich sparen wenn er den index nimmt. wenn ich dann z.B. nur die ersten n datensaetze brauche waeren das genau n - x zufaellige zugriffe fuer die tupels … wo muessen jetzt die restlichen 26 sekunden verschwendet werden ?

der job sollte in wenigen ms erledigt sein

Falsch.

d.h. der kostenfaktor ist die sortiererei … die kann er sich sparen wenn er den index nimmt. wenn ich dann z.B. nur die ersten n datensaetze brauche waeren das genau n - x zufaellige zugriffe fuer die tupels

Davon steht aber nix in Deinem Select

… wo muessen jetzt die restlichen 26 sekunden verschwendet werden ?

der job sollte in wenigen ms erledigt sein

Nein. Explain explaint Dir sogar, warum.

Andreas

Doch - indem die Daten sequentiell und nicht random gelesen und dann (billiger) im Speicher sortiert werden.


d.h. der kostenfaktor ist die sortiererei

Nein, der Kostenfaktor - laut deiner Konfiguration - ist das Lesen der Daten, nicht das Sortieren. Du kannst jeden dieser Parameter in der Konfig einstellen, kannst also genau angeben, was wieviel kostet. Damit kannst du das ganze auch deinen Wünschen anpassen. Ob das dann jedoch mit der Realität übereinstimmt, ist eine andere Sache.



wenn ich dann z.B. nur die ersten n datensaetze brauche waeren das genau n - x zufaellige zugriffe fuer die tupels … wo muessen jetzt die restlichen 26 sekunden verschwendet werden ?

Du plenkst und deine Texte lassen deutsche Grosschreibung vermissen. Dadurch sind deine Texte schlecht lesbar. Korrigiere das bitte.

Zurück zum Thema: wenn du der Datenbank sagst, das du bloss die ersten n Datensätze brauchst (über LIMIT), dann wird die Datenbank das in ihre Kalkulation einbeziehen. Wenn du jedoch pauschal alle Ergebnisse aus der Tabelle abfragst und bloss einen kleinen Teil wirklich abrufst, bist du selber Schuld. Die Datenbank kann dir helfen - aber sie muss auch wissen, was sie machen soll.

sequenziell lesen → index … dann liefern → random-access auf die daten - wieviel steht dort noch nicht fest … es ist ja nicht gerade unueblich, dass ein client daten “scant” … oder eine liste nur das zeigt was man sieht - listen kann man scrollen …[/quote]


Ich hoffte diesen quatsch vermeiden zu koennen :slight_smile: … eine vernuenftige standard-konfig vorzufinden … ich schaus mir mal an

och …

das sollte bei diesem problem keine verbesserung bringen - da er ja alle tupel anschauen muss um zu wissen, welche “die ersten” waeren … wuerde er den index nehmen, wäre das natuerlich easy … weil voellig klar “wo die sind”

noe - absolut richtig - leider aber nicht bei PG

ich rede die ganze zeit vom sortieren … nur darum geht es …


mein “warum” bezieht sich aber auf das “warum” … das PG meint, was es meint hilft mir nicht weiter

Doch, PG weiss in etwa vorher, wieviele Daten da zurückkommen werden. Diese Daten werden ebenfalls mit in den Plan einbezogen.

Wenn der Client Daten “scannt”, dann ist das eine serielle Abfrage über alle Daten. Wenn der Client die ersten 20 haben will, kann ich im Index nachschauen, welche die ersten 20 sind. Das ist ein dicker Unterschied und der macht sich in vielen Fällen im Ausführungsplan bemerkbar. Ich habe aber keine Lust, dich noch häufiger darauf hinzuweisen. Wenn du die falschen Anfragen schickst, wirst du weniger gute Ergebnisse erzielen.

Du kannst gern mal ausprobieren, wie der Plan ausschaut, wenn der Index in jedem Fall hinzugenommen wird. SET enable_seqscan=off sollte dir da weiterhelfen. Nur bezweifle ich immer noch, dass das viel Sinn macht. Schliesslich müsste die Liste der Daten anhand der Liste im Index sortiert werden bzw. umgekehrt. Und ob ich nun pro Datensatz im Index nachschaue, an welcher Stelle der steht und dann Einträge dadurch hin und herschiebe oder ob ich die Daten, die ich schon im Speicher habe, einmal sauber durchsortiere …

Zur Standardkonfiguration: die passt in vielen Fällen, wobei fraglich ist, wieviel Änderungen du bisher schon dran vorgenommen hast, um die Bedingungen auf deiner Maschine abzubilden. Also Speicherwerte bekannt geben ect.

obwohl limit keine loesung fuer mein problem ist - hier mal das explain auf die tabelle mit order by … incl limit 1 … kein where, kein join

"Limit  (cost=80249.52..80249.52 rows=1 width=95) (actual time=8232.067..8232.068 rows=1 loops=1)"
"  ->  Sort  (cost=80249.52..81338.88 rows=435744 width=95) (actual time=8232.063..8232.063 rows=1 loops=1)"
"        Sort Key: (lower((field)::text))"
"        Sort Method:  top-N heapsort  Memory: 17kB"
"        ->  Seq Scan on table t  (cost=0.00..78070.80 rows=435744 width=95) (actual time=0.027..6985.505 rows=435741 loops=1)"
"Total runtime: 8232.165 ms"

jetzt erklaere mir doch mal wieso der 8 sec benoetigen muss

die pg-config wurde von mir bisher nicht veraendert - das will ich eigentlich auch weiterhin nicht wirklich

Gerne.
Das Lesen der Daten von der Platte benötigt 6,9 von insgesamt 8,2 Sekunden, das macht rund 85% der Gesamtzeit.
Das Sortieren und alles drum und dran benötigt die restliche Zeit, das macht nur 15 %.

Oh Wunder, das deckt dich sogar annähernd mit den vorher errechneten Kosten - die Datenbank hat also gar nicht so sehr daneben gelegen mit dem letztendlich ausgewählten Plan.

Das sequentielle Lesen kostet bei weitem die meiste Zeit, random Zugriff wäre nur noch langsamer. Ich würde sagen: kauf dir bessere Festplatten.


Btw., bezüglich Konfiguration: die Standardconfig ist auf kleine Datenbanken ausgelegt und darauf, nicht alle Systemressourcen nur für PostgreSQL in Beschlag zu nehmen. Wenn du eine größere Datenbank darauf fahren willst, musst du zumindest einige grundlegende Einstellungen verändern.