Postgres - Langsame einfach join mit where-Klausel

Ich bin mit einigen Schwierigkeiten eine Abfrage optimieren, und hatte gehofft, dass hier jemand möglicherweise ein paar Hinweise.

Ich habe zwei Tabellen:

CREATE TABLE "blog_cached_posts" (
    "id" int4 NOT NULL DEFAULT nextval('blog_cached_posts_id_seq'::regclass),
    "title" varchar(255),
    "content" text,
    "content_encoded" text,
    "published_at" timestamp(6) NULL,
    "written_by" varchar(255),
    "link" varchar(255),
    "blog_id" int4,
    "guid" varchar(255),
    "created_at" timestamp(6) NULL,
    "updated_at" timestamp(6) NULL,
    "is_highlighted_post" bool DEFAULT false
)

Mit einem index auf blog_cached_posts.blog_id

CREATE TABLE "blogs" (
    "id" int4 NOT NULL DEFAULT nextval('blogs_id_seq'::regclass),
    "site_id" int4,
    "image_id" int4,
    "name" varchar(255),
    "description" text,
    "url" varchar(255),
    "rss_feed_url" varchar(255),
    "active" bool DEFAULT true,
    "created_at" timestamp(6) NULL,
    "updated_at" timestamp(6) NULL,
    "date_highlighted" date,
    "highlighted_category_feed_url" varchar(255),
    "position" int4
)

Mit einem index auf blogs.site_id

Dies ist die Abfrage:

SELECT "blog_cached_posts".*
FROM "blog_cached_posts"
join blogs on blogs.id = blog_cached_posts.blog_id
WHERE ((published_at IS NOT NULL) AND (blogs.site_id = 80))
ORDER BY published_at desc
LIMIT 5

Hier ist ein EXPLAIN ANALYZE:

Limit  (cost=9499.16..9499.17 rows=5 width=1853) (actual time=118.538..118.539 rows=5 loops=1)
  ->  Sort  (cost=9499.16..9626.31 rows=50861 width=1853) (actual time=118.536..118.537 rows=5 loops=1)
        Sort Key: blog_cached_posts.published_at
        Sort Method:  top-N heapsort  Memory: 33kB
        ->  Hash Join  (cost=16.25..8654.38 rows=50861 width=1853) (actual time=0.186..82.910 rows=48462 loops=1)
              Hash Cond: (blog_cached_posts.blog_id = blogs.id)
              ->  Seq Scan on blog_cached_posts  (cost=0.00..7930.94 rows=52954 width=1853) (actual time=0.042..56.635 rows=52950 loops=1)
                    Filter: (published_at IS NOT NULL)
              ->  Hash  (cost=13.21..13.21 rows=243 width=4) (actual time=0.135..0.135 rows=243 loops=1)
                    ->  Seq Scan on blogs  (cost=0.00..13.21 rows=243 width=4) (actual time=0.007..0.089 rows=243 loops=1)
                          Filter: (site_id = 80)
Total runtime: 118.591 ms

Gibt es eine Möglichkeit, um dies zu optimieren, jenseits der ~120ms ist es derzeit?

BEARBEITEN

Hier ist, was ich am Ende tun. (Nach dem Lesen der Kommentar von @ypercube)

Ich fügte hinzu, einen index zu blog_cached_posts:

CREATE INDEX \"blog_cached_posts_published_at\" ON \"public\".\"blog_cached_posts\" USING btree(published_at DESC NULLS LAST);
COMMENT ON INDEX \"public\".\"blog_cached_posts_published_at\" IS NULL;

Und ich änderte die wählen auf den folgenden:

SELECT "blog_cached_posts".*
FROM "blog_cached_posts"
join blogs on blogs.id = blog_cached_posts.blog_id
WHERE published_at is not null and blogs.site_id = 80
ORDER BY published_at desc nulls last
LIMIT 5

Dieser brachte die Ausführungszeit auf ~3ms.

Hier ist der neue Ausführungsplan:

Limit  (cost=0.00..3.85 rows=5 width=1849) (actual time=0.027..0.047 rows=5 loops=1)
  ->  Nested Loop  (cost=0.00..39190.01 rows=50872 width=1849) (actual time=0.026..0.046 rows=5 loops=1)
        ->  Index Scan using blog_cached_posts_published_at on blog_cached_posts  (cost=0.00..24175.16 rows=52965 width=1849) (actual time=0.017..0.023 rows=5 loops=1)
              Filter: (published_at IS NOT NULL)
        ->  Index Scan using blogs_pkey on blogs  (cost=0.00..0.27 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=5)
              Index Cond: (blogs.id = blog_cached_posts.blog_id)
              Filter: (blogs.site_id = 80)
Total runtime: 0.086 ms
  • Haben Sie versucht, ändern Sie Ihre index auf blog_cached_posts aus der Spalte blog_id den Spalten blog_id, published_at?
  • Ich würde auch versuchen eine einfache index auf published_at
  • hat u versuchen meine Lösung? Wenn ja, postet bitte die Ergebnisse. thx.
  • Sorry, hatte noch keine Zeit, um zu überdenken Sie das problem noch. Testen Sie Ihre Vorschläge heute 🙂
  • Dein Vorschlag hat funktioniert. Das hinzufügen eines einfachen index für published_at desc nulls last, brachte der Ausführung-Zeit bis zu ~3ms. - Sollten Sie eine Antwort mit der Lösung 🙂
  • sind Sie sicher, dass dies die Lösung war und auch nicht, dass die Abfrage zwischengespeichert wurden? Haben Sie versucht, mit LIMIT 50 oder LIMIT 200 ? Ist es auch schneller?
  • Oh, ich sehe die anderen Ausführungsplan, OK.

InformationsquelleAutor Thomas Dippel | 2011-06-17
  1. 1

    Als ich erwähnte in einem Kommentar, ich würde erstmal versuchen, das hinzufügen einer einfachen index auf published_at. Es scheint, dass, wenn es nicht die ORDER BY und LIMIT 5 Klauseln, die Abfrage wäre ziemlich effizient und alle anderen benötigten Indizes existiert.

    Daher, das hinzufügen eines index auf das Feld, das verwendet wird, für die endgültige Sortierung ist in der Regel sehr effizient.

    Als Dems erklärte in seiner Antwort:

    Da der index ( blog_id, published_at ) ist in einem Zustand, dass ist gut für join, es stellt sich heraus, dass weniger gut für die Art. Aus diesen Gründen können Sie sehen, Wert in beiden Indizes statt (auf blog_id und published_at getrennt.)

    InformationsquelleAutor ypercubeᵀᴹ
  2. 5

    Ihr Problem ist, Sie kann nicht realistisch einen index verwenden, ziehen Sie die benötigten 5 Beiträge geradezu. Hüpfen über index-dos und donts für einen moment.

    (blog_id, published_at) (vorgeschlagen in den Kommentaren) würde wohl helfen, wenn die Abfrage für einen bestimmten blog, aber deine die meisten selektiven Einschränkung, dass die Abfrage, die an den site_id, D. H. auf einer separaten Tabelle zusammen.

    Seq Scan on blogs  (cost=0.00..13.21 rows=243 width=4) (actual time=0.007..0.089 rows=243 loops=1)
  Filter: (site_id = 80)

    Den oben bedeutet, dass entweder Sie haben keinen index auf site_id, oder dass diese insbesondere site_id ist ganz über dem Platz und Postgres geht durch die ganze Tabelle, da es benötigen, um es zu öffnen, unabhängig.

    Dies führt dann zu mehreren blog-ids, und diese werden verwendet, um alle relevanten Beiträge über einen hash-join. Da aber mehrere blogs beteiligt sind, ist der beste PG tun können, ist greifen alle gültigen Beiträge und anschließend top-n-Sortierung, Sie.

    Selbst wenn Sie waren, um es zu ändern, so dass Sie passieren die blog-ids direkt in eine IN() - Klausel, die einen index auf (blog_id, published_at) würde nicht nachgeben, die benötigten Zeilen in der Reihenfolge. So würde es dennoch greifen alle Beiträge für alle relevanten blogs und top-n-Sortierung der Durcheinander.

    Einen Weg, um das problem zu umgehen wäre, um zu ändern Ihr schema leicht:

    CREATE TABLE "blog_cached_posts" (
    "id" int4 NOT NULL DEFAULT nextval('blog_cached_posts_id_seq'::regclass),
    ...
    "published_at" timestamp(6) NULL,
    "site_id" int4,
    "blog_id" int4,
    ...
)

    Beachten Sie die zusätzlichen site_id. Dies erlaubt es, nachträglich einen index erstellen, der auf (site_id, published_at desc nulls last) und schreiben Sie Ihre Abfrage wie:

    SELECT "blog_cached_posts".*
FROM "blog_cached_posts"
WHERE site_id = 80
ORDER BY published_at desc nulls last
LIMIT 5

    Einen alternativen Ansatz, pro Kommentare, ist die Aufrechterhaltung eines latest_site_posts Tabelle mithilfe von Triggern. Sie werden am Ende mit etwas, das ähnlich ist zu den oben genannten Vorschlag, aber mit einer kleineren Tabelle (d.h. weniger Wiederholungen der site_id).

    • Ich bin nicht einverstanden. Zwar ist es wahrscheinlich richtig, dass die meisten selektiven Prädikat ist auf site_id die Veränderung des index enthalten published_at sollte in der Tat eine potentiell massiven Unterschied. Dies wäre, weil nach dem Filtern durch site_id, kann die Verknüpfung hen erreicht werden, indem ein index zum nachschlagen, eher als einen sequenziellen scan.
    • In der Tat. Aber wenn seine blogs enthalten 100k posts jeweils einen index (blog_id, published_at) führen würde abrufen 100k * number of blog Zeilen in der falschen Reihenfolge, die müssen dann top-n sortiert. Durch den Kontrast, indem die zusätzliche site_id Feld, als ich schlage vor, ermöglichen es dem Planer, schnappen Sie sich die top-5 Zeilen direkt.
    • Eigentlich ist die GRENZE 5 ist sichergestellt, dass nur 5 Beiträge sind erforderlich für jeden blog_id. Dein Vorschlag hat sicherlich garantieren die Schnellste mögliche Lösung für die Abfrage, aber wäre ich besorgt, wie der entnormierung, es sei denn, es nachgewiesen werden kann, absolut erforderlich. Nach diesem Muster von de-Normalisierung und Indizierung für alle Abfragen könnte zu einem sehr aufgeblähten schema in der Tat. So dass ich don ' T nicht einverstanden mit Ihren Aussagen, nur, dass ich den Ansatz als eine wenn-muss-muss-option, sondern als eine Präferenz.
    • "Eigentlich ist die GRENZE 5 ist sichergestellt, dass nur 5 Beiträge sind erforderlich für jeden blog_id." Wirklich... schau an, die erklären/analysieren. Es ist eine top-5-Sortierung 50861 Beiträge gezogen von 243 blogs. PG Holen jeweils jeden letzten gültigen post vor der top-n-Sortierung. Und vereinbart, Denormalisierung in der Regel schlecht ist. Aber dies ist einer der Fälle, wo es tatsächlich Sinn macht. (Auf einem separaten Hinweis, eine weitere option für ihn wäre die Aufrechterhaltung einer "latest_posts" - Tabelle mithilfe von Triggern.)
    • Nur weil derzeit die Daten vollständig ungeordnet (mit Bezug auf published_at). Mit den vorgeschlagenen index, den Sie tatsächlich haben mehrere geordnete Listen (eine für jede blog_id). Die top 5 von jedem dieser geordneten Listen kann dann ausgewählt werden für einen merge-sort, was die letzten 5 erforderlich. Der 6. Eintrag in einer der geordneten Listen wird garantiert nie gebraucht. In der Tat ist dies 5 * blog_count anstatt nur 5, aber nicht die n * blog_count Sie können Angst vor.
    • Posten Sie Ihre 'neuesten Beiträge' - option als eine Antwort, es ist sicherlich sinnvoll 🙂
    • Ich wünschte, Sie waren korrekt, aber ich habe nie gesehen, dass die Postgres-Planer so Verhalten, wie Sie vorschlagen... 9.1 könnte dies ändern: Sie hat eine Optimierung für partitionierte Tabellen, so dass das Holen einer max() nicht mehr erfordert einen index-scan unabhängig von Indizes. Aber ich bin misstrauisch, es gilt auch für diesen besonderen Anwendungsfall.
    • +1 nur, weil das Gewicht der information, Erklärung und Meinung nach ist diese Antwort bietet jetzt für die OP.
    • Danke. Btw, s/benötigt man einen index-scan/erfordert einen seq-scan/ in meinem vorherigen Kommentar. (Details) 😛
    • Ich mag die Idee, und es macht sicherlich Sinn. - Leider bin ich den Umgang mit einigen legacy-Anwendungen so ändern Sie das schema ist eine größere Prozedur, als man denken mag 🙂
    • in diesem Fall sollten Sie das Zwischenspeichern der Ergebnisse der aktuellen Abfrage in memcached für ein paar Minuten oder Stunden.
    • Ich bin bereits. Die Daten werden in einer stark zwischengespeichert Rails-app, aber ich wollte sehen, wenn ich könnte, bringen die Ausführungszeit, wenn der cache abgelaufen ist 🙂
    • Nun, in diesem Fall fürchte ich, dass Sie gehen zu müssen, um entweder die vorhandene Tabelle oder einen neuen hinzufügen. 🙂
    • Werfen Sie einen Blick auf mein edit in der Frage-text - fand ich eine andere Lösung.
    • Na toll, Sie hatte keinen index auf, die, mit zu beginnen? 😀
    • Nein - ich hatte immer den Eindruck, dass Sie sollten nicht-index-Datum-Spalten, weil Ihre Zeilen sind wahrscheinlich alle Verschieden. Tja, man lernt jeden Tag etwas neues, werden Sie sagen 🙂
    • +1 für die Gründliche und ausführliche Antwort (und erkennen, dass @Denis war unter dem Eindruck, dass ein index auf published_at bereits existierte, aber hatte sich nicht verbessert peeformance).

    InformationsquelleAutor Denis de Bernardy
  3. 1

    Als pro meinen Kommentar auf Denis' Antwort, und Thomas Müller Kommentar...

    Einen index auf blog_cached_posts ist erforderlich, um zu vermeiden, einen sequentiellen scan dieser Tabelle. Indem die index-cover blog_id, published_at, kann die Abfrage nach der Logik die der folgenden ähnelt...

    1. Filtern Sie die blogs-Tabelle für die site_id (80)

    2. Für jeden Datensatz gibt, beitreten zu blog_cached_posts

    3. Verwenden Sie die blog_id, published_at index zur Identifizierung der Datensätze

    4. Möglich zusammenfassen, dass der index auf sich selbst zu ermöglichen ein schnelles Sortieren

    Da der index befindet sich in einem Zustand, dass ist gut für join, es stellt sich heraus, dass weniger gut für die Sortierung. Aus diesen Gründen können Sie sehen, Wert in beiden Indizes statt (auf blog_id und published_at getrennt.)

    BEARBEITEN

    In einem Kommentar auf Denis' Antwort sagen Sie, dass das hinzufügen einer Spalte, könnte sich als problematisch aufgrund der legacy-apps. Ich denke, der kann ein paar Tipps, die helfen können, es...

    Versuchen Sie, erstellen Sie eine neue Tabelle mit den Feldern, die Sie brauchen, und ersetzen die ursprüngliche Tabelle mit einem Blick auf die neue Tabelle. Die neue Tabelle haben kann, zum Beispiel, default-Werte in den zusätzlichen Spalten.

    Oder erstellen Sie eine mapping-Tabelle mit den fiels müssen Sie beziehen sich gemeinsam für Ihre Anfrage. Diese mapping Tabelle wird dann weiterhin über einen trigger.

    • Dies hat sich nicht geändert, der Zeitpunkt der Ausführung
    • Hat die EXPLAIN-PLAN anzeigen Verwendung des neuen index? Oder haben Sie versucht, die Anwendung zwei Indizes anstelle von einem? Wenn sonst nichts funktioniert, es scheint Ihr stecken bei der änderung des Schemas. Siehe mein edit...
    InformationsquelleAutor MatBailie
  4. 0

    Weil Ihre beste suchen Prädikat site_id, schalten Sie Ihre Tabelle auswählen, um zu wählen Sie aus blogs ersten, so springt er direkt in über den index. Auch, setzen Sie die published_at not null in das join-Zeilen werden abgeschnitten, so bald wie möglich, wie dieses:

    SELECT blog_cached_posts.*
FROM blogs
join blog_cached_posts on blogs.id = blog_cached_posts.blog_id AND published_at IS NOT NULL
WHERE blogs.site_id = 80
ORDER BY published_at desc
LIMIT 5

    Beachten Sie, dass diese Lösung erfordert keine neue Indizes, noch würde es von Ihnen profitieren.

    Bitte lassen Sie uns wissen, wie diese Abfrage führt im Vergleich

    • Diesem Vorschlag wird streng äquivalent von Postgres' Sicht. Seine eigentliche Problem ist, dass er keine glaubwürdige index zu verwenden, um die Vorteile der limit 5 Teil seiner Aussage.
    • In der Tat, dieses milde Umformulierung der SQL-Renditen keine änderung zu den algorithmischen Ausdruck. Grundsätzlich werden bei der Bildung der Planung, postgres würde schon sehen, was du sagst.
    • in meiner Erfahrung, ich habe nicht gefunden, dass es Figuren, die aus. Ich habe fantastische Verbesserungen in der Geschwindigkeit von Abfragen, dies zu tun. Mal sehen, ob er Beiträge, die eine änderung in der Geschwindigkeit mit diesem Ansatz
    • Keine änderungen in der Ausführung Zeit für diese Methode
    InformationsquelleAutor Bohemian
  5. 0
    • verwenden, GRENZE ['er Holen, die begrenzte Daten aus der Tabelle']
    • Insded von blog_cached_posts.* verwenden Sie nur die notwendigen Daten e.ich
      blog_cached_posts.name,blog_cached_posts.E-Mail
    • Vermeiden Sie Unerwünschte "" Anführungszeichen in der select-Anweisung
      WÄHLEN Sie blog_cached_posts.* VON blog_cached_posts
    • Dies scheint nicht die Adresse der OPs-Prinzip Frage : wie verbessern Sie die Abfrage Kosten und so erhalten Sie eine schnellere Ausführung.
    InformationsquelleAutor K6t
Schreibe einen Kommentar