Postgresql: Query Planner bei Subselect auf die Sprünge helfen?

[Perry Staltic]

Für mich riecht das irgendwie nach skurriler Mikrooptimierung, die mehr Schaden anrichtet als sie bringt.

Ich sehe das erstmal ähnlich, wobei Mikrooptimierung angesichts von 10-20 Mio Datensätze auch ein wenig "skurril" klingt. Ich finde, da kann man durchaus mal an der ein oder anderen Ecke feilen, bevor man den nächsten Server kauft.
Ich sags glaub ich zum vierten Mal jetzt, der normale Join, mit dem man - nicht mikrooptimiert - anfangen würde, fehlt mir hier und zwar an erster Stelle.
Wenn das nicht zieht, dann meinetwegen auch skurrile Optimierungen.

[hellbringer]

Ich sags glaub ich zum vierten Mal jetzt, der normale Join, mit dem man - nicht mikrooptimiert - anfangen würde, fehlt mir hier und zwar an erster Stelle.

Warum hier kein Join verwendet wird, ist mir auch unklar. Zumindest haben meine Versuche in der Vergangenheit ergeben, dass ein Join fast immer performanter ist als ein Sub-Query. Ich würde eher hier ansetzen, bevor ich Anfange andere Dinge zu optimieren.

[erc]

Zwischen den Zeilen lese ich irgendwie eine unbegründete Angst gegenüber Subqueries. In dem Fall würde ein JOIN sicherlich das Problem lösen, aber nicht weil der besser "optimiert" ist oder ähnliches. Der JOIN würde in dem Fall die Kosten für den derzeitig "ungünstigen" Plan massiv in die höhe treiben. Das ist der Plan, bei dem die Datenbank sagt, dass ist bei den aktuellen Daten der effektivste Weg den Query auszuführen. Das ursächliche Problem ist viel mehr, dass hier scheinbar unterscheidliche Workloads auf der Tabelle stattfinden die den Optimizer "durcheinander" bringen. Wenn man den job_id Index um das Sortierkriterium erweiterten würde, würde der Query immer im ms Bereich laufen. Da ich die Workloads aber nicht kennen, würde ich das nicht als Handlungsempfehlung aussprechen.

[Perry Staltic]

Zwischen den Zeilen lese ich irgendwie eine unbegründete Angst ..

Was mich angeht, würde ich es eher erfahrungsgemäßes Misstrauen nennen, nicht mal Postgres spezifisch.
Leider verstehe ich aber Deine Darstellung des Sachverhalts nicht.
Ein Join ist m.E. der "natürliche", erste Schritt für eine normale Abfrage, danach kommt Optimierung oder Mikrooptimierung oder skurrile Mikrooptimierung, jeweils wenn nötig.

Ist aber wahrscheinlich auch egal, der TE hat wohl fertig.

[Tropi]

Was hat die Anzahl der Queries mit dem Netzwerkverkehr zu tun? Man kann ja pro Request auch mehrere Queries ausführen.

Schon richtig, aber ich brauche ja das Ergebnis der Query, d.h. entweder ich brauche zwei Requests oder ich nehme eben CTE, Subqueries oder was auch immer.

Zum Join: Ja, der Join ist in den meisten Szenarien schneller als die (schlecht geplante) Sub-Query ist aber trotzdem noch Meilen von dem entfernt was zwei separate Queries liefern.

Bei einem eher typischen Fall sieht die Sache so aus:

• Sub-Select: 412 Sekunden
• JOIN: 27 Sekunden
• 2x Select (1. Query für Job ID und die dann als konstanten Wert im 2. Query eingesetzt): 50ms + 50ms = 100ms

D.h. die dritte Möglichkeit ist in dem Fall immer noch um einige Größenordnungen schneller.

Wie sich aber herausgestellt hat, benötigt auch die Variante mit konstantem Wert recht lange bei vielen zugehörigen Einträgen. Der größte Job hat ca. 500k Einträge und die Query:

SELECT some_field FROM rows WHERE job_id = <BigJob> ORDER BY row_id ASC LIMIT 20;

braucht 360 Sekunden, obwohl auf der Bedingung ein Index liegt. (Query Plan ist oben schon gepostet.) Auch wenn das eher selten vorkommt muss ich mir da ohnehin noch was überlegen.

[erc]

Wie sich aber herausgestellt hat, benötigt auch die Variante mit konstantem Wert recht lange bei vielen zugehörigen Einträgen.

Das ist doch grade das Problem. Gibst du ein skalaren Wert als job_id an, kann der Optimizer schon schon schauen wieviel Datensätze getroffen werden. Wenn die Datenbank sieht es werden nur 50 Datensätze getroffen, dann werden die geladen und sortiert. 50 Datensätze zu laden und sortieren ist völlig unproblematisch. Hast du eine job_id mit 500.000 Datensätze ist das nicht mehr so unproblematisch. 500.000 Datensätze laden, sortieren und dann 499.980 Datensätze zu verwerfen ist teuer! Der Optimizer sagt hier, es ist billiger ein Table Scan zu machen, weil der schon passend sortiert ist (row_id ASC) und nach 20 Treffern abzubrechen. Bei der job_id als Subquery schätzt die Datenbank die Anzahl der erwartenden Treffer (~27.000) und entscheidet sich deshalb immer für den Table Scan. Mit einem JOIN zwingst du die Datenbank nur in die andere Variante, die aber je nach job_id auch nicht effektiv ist.
Wenn du hier wirklich Performance brauchst, musst du den job_id Index um das Sortierkriterium erweitern. Dann ist es egal ob 10 oder 10.000.000 Datensätze pro job_id. Bei 20 Million Datensätzen solltest du aber besser 2 mal überlegen, auch bei Postgres!

[akretschmer]

ich sehe jetzt nicht auf Anhieb das explain analyse für die zuletzt genannte Query in #20, aber hast schon mal einen Index auf (job_id, row_id) probiert?

[Tropi]

ich sehe jetzt nicht auf Anhieb das explain analyse für die zuletzt genannte Query in #20, aber hast schon mal einen Index auf (job_id, row_id) probiert?

Auf den Hinweis von erc oben, jetzt, ja. Ergebnis ist jetzt sowohl mit dem Skalar als ID, sowie auch mit Sub-Query top, ca. 50ms. Glaub damit ist das hier dann eh erledigt.

@erc: Warum 2x überlegen? Wegen zusätzlicher Zeit beim Insert oder gibt's einen anderen Grund?

[akretschmer]

ah ja, hatte ich überlesen. Hatten erc und ich wohl denselben Gedanken ...

[Tropi]

Ja, und zwar einen guten.
Danke nochmal euch beiden und auch natürlich den anderen, ist mir natürlich klar, dass es immer schwierig ist sich in den Use-Case von fremden Benutzern und deren Queries reinzuversetzen.

[Perry Staltic]

Hurra!

war doch gar nicht so schwer, mal eine Gegenüberstellung zu machen und zu verraten, wieso Du zu Deiner Query gekommen bist.(hat nur 20 Posts gedauert)

Bei einem eher typischen Fall sieht die Sache so aus:

• Sub-Select: 412 Sekunden
• JOIN: 27 Sekunden
• 2x Select (1. Query für Job ID und die dann als konstanten Wert im 2. Query eingesetzt): 50ms + 50ms = 100ms

D.h. die dritte Möglichkeit ist in dem Fall immer noch um einige Größenordnungen schneller.

Und zum Ergebnis:
500T Datensätze sortieren und dann das Sahnehäubchen davon runterzunehmen ist teuer, da kann ich erc nur Recht geben.
Zusätzliche Indizierung aber auch.

[eagle275]

nur die "Art von teuer" ist unterschiedlich .. Index verbrät Speicher, der auf einem vernünftigen Server nicht weh tut, ohne Index kostet es Zeit .. und keiner wartet gern Minuten, bis die Webseite oder Anwendung das gewünschte Ergebnis präsentiert ... also verbrate lieber Speicher

[Perry Staltic]

nur die "Art von teuer" ist unterschiedlich .. ... also verbrate lieber Speicher

Ja, das ist ok, aber Speicher ist nicht das einzige. Indexpflege kostet auch Zeit beim Insert (und fallweise auch beim Update)

[akretschmer]

Depends, siehe HOT-Updates. Und als weiterer Punkt: es vergrößert das WAL. Zumindest bei Streaming Replication, nicht bei logical Replication.

[erc]

@erc: Warum 2x überlegen? Wegen zusätzlicher Zeit beim Insert oder gibt's einen anderen Grund?

Ja, Ressourcen spielen da natürlich auch eine Rolle. Es geht aber mehr um das Vorgehen allgemein. Ab einer gewissen Größenordung ist dieses "getreibene" optimieren kein gutes Zeichen. Mach dir in vorherein klar was die Fragen sind, wie die Datenstruktur dazu aussehen muss und was die Konsequenzen deiner Entscheidung sind. Kommen neue Fragen dazu, oder ändern sich die Fragen, überdenk auch die Datenstruktur.
Für mich klingt das Thema stark nach einer Archiv/Protokoll/Log/Datenmüll-Tabelle und du bringst auf diese jetzt OLTP Workload. Das skaliert nicht gut.