Ich habe eine Frage zu einem postgresql 9.2-System, das ungefähr 20s in seiner normalen Form benötigt, aber nur ~ 120ms benötigt, wenn ein CTE verwendet wird.
Ich habe beide Abfragen aus Gründen der Kürze vereinfacht.
Hier ist die normale Form (dauert ungefähr 20s):
%Vor%Hier ist die Erklärung für diese Frage: Ссылка
Das CTE-Formular (ca. 120ms):
%Vor%Hier ist die Erklärung für den CTE: Ссылка
Durch einfaches Verschieben von ORDER BY in den äußeren Teil der Abfrage werden die Kosten um 99% reduziert.
Ich habe zwei Fragen: 1) Gibt es eine Möglichkeit, die erste Abfrage ohne Verwendung eines CTE so zu konstruieren, dass es logisch äquivalenter leistungsfähiger ist und 2) was sagt dieser Leistungsunterschied darüber aus, wie der Planer bestimmt Wie man die Daten holt?
Gibt es zu den obigen Fragen zusätzliche Statistiken oder andere Planungshinweise, die die Performance der ersten Abfrage verbessern könnten?
Bearbeiten: Das Entfernen des Limits führt auch dazu, dass die Abfrage einen Heap-Scan im Gegensatz zu einem Index-Scan rückwärts verwendet. Ohne LIMIT wird die Abfrage in 40 ms abgeschlossen.
Nachdem ich den Effekt von LIMIT gesehen habe, habe ich versucht mit LIMIT 1 , LIMIT 2 , etc. Die Abfrage läuft unter 100ms, wenn LIMIT 1 und 10s + mit LIMIT & gt; 1.
Nachdem wir darüber nachgedacht haben, geht Frage 2 darauf hinaus, warum der Planer in einem Fall einen Index-Scan rückwärts und in einem anderen logisch gleichwertigen Fall einen Bitmap-Heap-Scan + Sortierung verwendet. Und wie kann ich dem Planer "helfen", den effizienten Plan in beiden Fällen zu verwenden?
Aktualisierung:
Ich akzeptierte Craigs Antwort, weil sie die umfassendste und hilfreichste war. Die Art und Weise, wie ich das Problem gelöst habe, war die Verwendung einer Abfrage, die praktisch äquivalent, aber nicht logisch äquivalent war. Am Anfang des Problems lag ein Index-Scan rückwärts des Indexes von modified_at. Um dem Planer mitzuteilen, dass dies keine gute Idee war, füge ich ein Prädikat der Form WHERE modified_at >= NOW() - INTERVAL '1 year' hinzu. Dies beinhaltete genügend Daten für die Anwendung, verhinderte jedoch, dass der Planer den Rückwärts-Index-Scan-Pfad durchging.
Dies war eine Lösung mit viel weniger Auswirkungen, die das Neuschreiben der Abfragen unter Verwendung einer Unterabfrage oder eines CTE verhinderte. YMMV.
Dies ist der Grund, warum dies so ist, mit der folgenden Erklärung bis mindestens 9.3 (wenn Sie dies und eine neuere Version lesen, überprüfen Sie, ob es sich nicht geändert hat):
PostgreSQL optimiert nicht über CTE-Grenzen hinweg. Jede CTE-Klausel wird isoliert ausgeführt, und ihre Ergebnisse werden von anderen Teilen der Abfrage verbraucht. Also eine Abfrage wie:
%Vor% bewirkt, dass die vollständige innere Abfrage ausgeführt wird. PostgreSQL wird die id = 4 Qualifikation nicht in die innere Abfrage "drücken". CTEs sind in dieser Hinsicht "Optimierungszäune", die sowohl gut als auch schlecht sein können; Wenn Sie möchten, können Sie den Planer überschreiben, aber Sie können CTEs nicht als einfache syntaktische Bereinigung für eine tief verschachtelte FROM Unterabfragekette verwenden, wenn Sie Push-down benötigen.
Wenn Sie das obige wie folgt umschreiben:
%Vor% Wenn Sie eine Unterabfrage in FROM anstelle eines CTEs verwenden, wird Pg die Qual in die Unterabfrage verschieben und alles wird gut und schnell ausgeführt.
Wie Sie festgestellt haben, kann dies auch zu Ihrem Vorteil sein, wenn der Abfrageplaner eine schlechte Entscheidung trifft. Es scheint, dass in einem Fall ein Index-Index-Scan der Tabelle immens teurer ist ein Bitmap- oder Index-Scan von zwei kleineren Indizes gefolgt von einem Filter und Sortierung, aber der Planer glaubt nicht, dass es sein wird So plant es die Abfrage den Index zu scannen.
Wenn Sie das CTE verwenden, kann es das ORDER BY nicht in die innere Abfrage schieben, also überschreiben Sie seinen Plan und zwingen ihn dazu, etwas zu verwenden, von dem er glaubt, dass es ein schlechterer Ausführungsplan ist - aber eines, das sich herausstellt viel besser sein.
Es gibt eine unangenehme Problemumgehung, die für diese Situationen verwendet werden kann, die OFFSET 0 hack, aber Sie sollten sie nur verwenden, wenn Sie keine Möglichkeit finden, den Planer dazu zu bringen, das Richtige zu tun - und wenn es sein muss Verwenden Sie es, bitte kochen Sie dies zu einem in sich geschlossenen Testfall und melden Sie es an die PostgreSQL-Mailingliste als einen möglichen Abfrageplaner-Fehler.
Stattdessen empfehle ich zuerst zu sehen, warum der Planer die falsche Entscheidung trifft.
Der erste Kandidat ist stats / schätzt Probleme, und wenn wir Ihren problematischen Abfrageplan betrachten, gibt es einen Faktor von 3500 Fehleinschätzungen der erwarteten Ergebniszeilen. Das ist groß, aber nicht unglaublich groß, obwohl es interessanter ist, dass Sie nur eine Zeile erhalten, wo der Planer einen nicht-trivialen Zeilensatz erwartet. Das hilft uns allerdings nicht viel. Wenn die Anzahl der Zeilen niedriger als erwartet ist, bedeutet dies, dass die Entscheidung, den Index zu verwenden, besser war als erwartet.
Das Hauptproblem sieht so aus, als ob es nicht ist, indem es die kleineren, selektiveren Indizes sierra_kilo und papa_lima verwendet, weil es die ORDER BY sieht und denkt, dass es mehr Zeit beim Rückwärtsspielen spart Index-Scan und vermeiden Sie die Sortierung als es wirklich tut. Das macht Sinn, da nur eine passende Zeile zu sortieren ist! Wenn es die erwarteten 3500 Zeilen hat, dann hat es vielleicht mehr Sinn gemacht, die Sortierung zu vermeiden, obwohl das immer noch ein ziemlich kleines Rowset ist, das nur im Speicher sortiert wird.
Stellen Sie irgendwelche Parameter wie enable_seqscan usw. ein? Wenn du dies tust, lösche sie; sie sind nur für testing und für die Produktion völlig ungeeignet. Wenn Sie nicht enable_ params verwenden, denke ich, dass es sich lohnt, dies in der PostgreSQL-Mailingliste pgsql-perform zu erwähnen. Die anonymisierten Pläne machen dies jedoch ein wenig schwierig, zumal es keine Garantie gibt, dass Bezeichner aus einem Plan auf die gleichen Objekte in dem anderen Plan verweisen, und sie stimmen nicht mit dem überein, was Sie in der Abfrage zu der Frage geschrieben haben. Sie sollten eine korrekt handgemachte Version erstellen, in der alles übereinstimmt, bevor Sie in der Mailingliste nachfragen.
Es besteht eine ziemlich gute Chance, dass Sie die echten Werte für jeden zur Verfügung stellen müssen. Wenn Sie dies nicht in einer öffentlichen Mailing-Liste tun möchten, steht eine weitere Option zur Verfügung . (Ich sollte beachten, dass ich für eines von ihnen arbeite, nach meinem Profil).
Nur ein Schuss in die Dunkelheit, aber was passiert, wenn Sie dies ausführen
%Vor%Tags und Links postgresql common-table-expression