Nehmen wir an, ich habe eine Reihe von Elementen:
Eine Abfrage kann auf zwei Arten erstellt werden. Erstens:
%Vor%Oder es kann geschrieben werden als:
%Vor%Meine Frage bezieht sich speziell auf PostgreSQL.
In PostgreSQL gibt es normalerweise einen recht kleinen Unterschied bei vernünftigen Listenlängen, obwohl IN konzeptionell viel sauberer ist. Sehr lange AND ... <> ... -Listen und sehr lange NOT IN -Listen zeigen beide eine schreckliche Leistung, wobei AND viel schlechter ist als NOT IN .
Wenn beide in beiden Fällen lang genug sind, dass Sie die Frage überhaupt stellen, sollten Sie stattdessen einen Anti-Join oder Unterabfrage-Ausschluss-Test über eine Werteliste durchführen.
%Vor%oder:
%Vor%(Bei modernen Pg-Versionen werden beide den gleichen Abfrageplan erzeugen).
Wenn die Werteliste lang genug ist (viele Zehntausende von Elementen), kann die Parsing-Abfrage mit erheblichen Kosten beginnen. An diesem Punkt sollten Sie in Erwägung ziehen, eine TEMPORARY -Tabelle zu erstellen, COPY in die auszuschließenden Daten aufzunehmen, möglicherweise einen Index dafür zu erstellen und dann einen der oben genannten Ansätze für die temporäre Tabelle anstelle des CTE zu verwenden.
Demo:
%Vor% Dabei steht exclude für die Liste der Werte, die weggelassen werden sollen.
Ich vergleiche dann die folgenden Ansätze für die gleichen Daten mit allen Ergebnissen in Millisekunden:
NOT IN list: 3424.596
AND ... list: 80173.823
VALUES basierend auf JOIN ausschluss: 20.727
VALUES basierter Unterabfrageausschluss: 20.495
JOIN , kein Index für die Ex-Liste: 25.183
... den CTE-basierten Ansatz über dreitausend Mal schneller als die AND -Liste und 130-mal schneller als die NOT IN -Liste macht.
Code hier: Ссылка (Schild deine Augen, ihr folgt diesem Link).
Für diese Datensatzgröße machte das Hinzufügen eines Indexes zur Ausschlussliste keinen Unterschied.
Anmerkungen:
IN Liste erstellt mit SELECT 'IN (' || string_agg(item::text, ',' ORDER BY item) || ')' from exclude;
AND list generiert mit SELECT string_agg(item::text, ' AND item <> ') from exclude; ) NOT IN in <> ALL übersetzt
Also ... Sie können sehen, dass zwischen den Listen IN und AND eine wirklich große Lücke besteht, anstatt einen richtigen Join zu machen. Was mich überraschte war, wie schnell es mit einem CTE war, das eine VALUES Liste benutzte ... das Parsen der VALUES Liste dauerte fast keine Zeit, sie führte die gleiche oder etwas schneller als die Tabelle aus Ansatz in den meisten Tests.
Es wäre schön, wenn PostgreSQL automatisch eine lächerlich lange IN -Klausel oder eine Kette ähnlicher AND -Konditionen erkennt und zu einem intelligenteren Ansatz wie einem Hash-Join oder einem impliziten Umwandeln in einen CTE-Knoten übergeht. Im Moment weiß es nicht, wie das geht.
Siehe auch:
Ich stimme etwas mit der ursprünglich akzeptierten Antwort von @Jayram nicht überein.
Nicht zuletzt ist der Link für SQL Server und widerspricht vielen anderen Artikeln und Antworten. Außerdem gibt es keine Indizes für die Beispieltabelle.
Normalerweise für Unterabfrage-SQL-Konstrukte
<> (oder != ) ist ein skalarer Vergleich NOT IN ist ein linksbasierter Anti-Semi-Join relationaler Operator Einfacher ausgedrückt
NOT IN wird zu einer Form von JOIN, die einen Index (außer PostgreSQL!) verwenden kann != ist oft nicht SARGable und ein Index darf nicht verwendet werden Dies wurde auf dba.se diskutiert: "Die Verwendung von NOT-Logik in Bezug auf Indizes" . Für PostgreSQL, dann diesen erklärten Artikel erklärt das interne mehr (aber nicht für eine Liste von Konstanten mit NOT IN leider).
Wie auch immer, für eine Liste von Konstanten würde ich NOT IN vor <> generell verwenden, weil es einfacher zu lesen ist und aufgrund dessen, was @CraigRinger erklärt hat.
Für eine Unterabfrage ist NOT EXISTS der Weg zu gehen
Tags und Links sql postgresql