Full Table Scan schneller als Index Scan

[TS]

Hello CK,

ich sortiere mal etwas um:

Aber du brauchst dich ja nicht auf mein Wort zu verlassen. PostgreSQL und MySQL haben das vorzüglich dokumentiert. Lies es dort nach.

Ich vertraue deinem Wort i. d. R. schon und auch meistens gerne :-)
Ich möchte nur deinen Gedankengang verstehen und auch herausfinden, wieso die Bewertung der Frage scheinbar unabhängig von den Randbedingungen stattfinden kann.

Du selber schreibst doch nun hier einige Wenns und Abers. Die Reaktion des Optimizers hängt also augenscheinlich doch von Bedingungen ab.

Aber z. B. zehn Zugriffe über den Index würde schon eine erhebliche Indextiefe (Spezifität) bedeuten, während 10 Zugriffe auf die Daten (die müssen ja totzdem die Buffersize beachten) vielleicht erst 0.1% der Datei erfasst haben.

Du hast das Kriterium missverstanden oder nicht beachtet. Der Index wird ignoriert, wenn entweder das Resultset zu groß wird, so dass ein Index-Scan sich nicht mehr lohnt oder wenn die Tabelle zu klein ist. Wenn du eine große Tabelle hast und ein kleines Resultset (10 Rows sind sehr klein), dann wird der Index benutzt. Niemand hat gesagt, dass ein Index keinen Sinn macht.

Soweit kann ich das nachvollziehen. Allerdings: woher soll das DBMS wissen, ob ein Ergebnis zu groß werden wird. Selbst bei einer so vagen Abfrage "... WHERE <SPALTE> LIKE 'Sch%'" muss das Ergebnis nicht groß werden und erfahrungsgemäß wird hier auch ein Index benutzt, falls er auf <SPALTE> existiert. Anders sieht es bei "... WHERE <SPALTE> LIKE '%sch'" aus. Da wird der Index (üblicherwweise von links nach rechts indiziert) keinen Sinn haben. Ich will aber nicht in Abrede stellen, dass es auch DBMS mit einem Reverse Index (also von rechts nach links indiziert) geben könnte.

Das nur soweit, dass Du verstehst, warum ich auf Nennung des Querys plädiere.

Und in meiner Vorstellung kann ein mit z. B. 128 GB RAM ausgestatter Host die auch nicht "mal eben" komplett für einen einzigen DB-Filezugriff zur Verfügung stellen, sondern muss seine Gunst verteilen. Und da gäbe es als Flaschenhals auch immer noch die maximale Datentransferrate zwischen HDD und RAM.

So funktioniert Caching auch nicht. Gecached wird, was häufig gebraucht wird. Auf einem dedizierten DB-Server ist das oft genug die vollständige Datenbank, wenn die allerdings nicht in den Speicher passt, dann sind das nur die Teile, die am häufigsten gebraucht werden. Caching funktioniert nicht so, dass bei einer einmaligen Anforderung alles in den RAM geladen wird.

Genau deshalb hatte ich ja darauf hingewiesen. Die Vorgehensweise muss das DBMS sehr wohl von den physischen Gegebenheiten abhängig machen, wenn es sinnvoll arbeiten will.

Wenn du aber weiterhin auf sequential read vs random read abziehst: dann hast du immer noch die falsche Prämisse im Kopf. Bei einer großen Tabelle (ich setzte hier deine theoretischen 128gb an) und einem kleinen Resultset (ich setze hier deine 10 Rows an) wird natürlich ein Index verwendet.

Die "10 Rows" bringst Du ins Spiel. Ich sprach von 10 Zugriffen innerhalb des Indexbaumes zur Auffindung eines Treffers, bzw. dann meinetwegen 10*10 für eine Treffermenge.

Plakatives Beispiel: wenn du aber von den 128gb bei einer Abfrage 100gb der Rows zurück bekommen würdest, wird kein sinnvolles DBMS der Welt den Index verwenden.

Wie stellt es fest, dass die Treffermenge so hoch sein wird (siehe auch oben)? Oder wird es mitten in der Ausführung der Abfrage die Strategie umstellen von Indexnutzung auf sequentiellen Scan, wenn z. B. jede zweite Row einen Treffer liefert?

Bei Index-Bäumen gibt es da ja auch immer noch das Problem der Nicht-Balanciertheit...

Oder, alternativ: deine Tabelle belegt gerade mal eine Page, von denen du 10 Rows haben willst, da wäre ein Index-Zugriff völlig bescheuert.

Das ist nachvollziehbar, weil das DBMS wissen sollte, wie groß seine Tabellen sind und ob sie auch on the Fly in den bereitgestellten Teil des RAM passen.

Nach meinem Verständnis hinkt das immer noch.

Das liegt daran, dass du die Kriterien, nach denen das entschieden wird, immer noch nicht verstanden zu haben scheinst.

Das siehst Du falsch.

Ich möchte ja gerade auf den Kriterien, nach denen das DBMS das entscheidet, bestehen. Und dazu gehören meiner Meinung nach auch die von mir geforderten Angaben.

Liebe Grüße
Tom S.