🚘 🤭 ❄️ Schneller POSTGRESQL COUNT machen (*) 👐🏾 🙎🏾 👨‍👦

Es wird oft beanstandet, dass count (*) in PostgreSQL sehr langsam ist.

In diesem Artikel möchte ich Optionen untersuchen, damit Sie das Ergebnis so schnell wie möglich erhalten.

Warum ist count (*) so langsam?

Die meisten Menschen verstehen ohne Probleme, dass die folgende Anfrage langsam ausgeführt wird:

SELECT count(*)
FROM /*   */;

Dies ist schließlich eine komplexe Abfrage, und PostgreSQL muss das Ergebnis berechnen, bevor es weiß, wie viele Zeilen es enthalten wird.

Viele Menschen sind jedoch schockiert, als sie feststellen, dass die folgende Abfrage langsam ist:

SELECT count(*) FROM large_table;

Wenn Sie jedoch noch einmal darüber nachdenken, gilt Folgendes: PostgreSQL muss die Ergebnismenge berechnen, bevor sie gezählt werden kann. Da der „magische Zeilenzähler“ nicht in der Tabelle gespeichert ist (wie in MyISAM MySQL), können Sie die Zeilen nur zählen, indem Sie sie betrachten.

Daher führt count (*) normalerweise sequentielle Tabellenscans durch, was sehr teuer sein kann.

Ist das "" in count () ein Problem?

Das "*" in SELECT * FROM ... gilt für alle Spalten. Daher finden viele Leute, dass die Verwendung von count (*) ineffizient ist, und verwenden stattdessen count (id) oder count (1).

Aber das "*" in count (*) ist völlig anders, es bedeutet einfach "string" und wird überhaupt nicht erweitert (tatsächlich ist es ein "Aggregat mit Null-Argument"). Die Anzahl der Notationen (1) oder die Anzahl (id) ist tatsächlich langsamer als die Anzahl (*), da überprüft werden muss, ob das Argument NULL ist oder nicht (count ignoriert wie die meisten Aggregate die Argumente NULL).

Sie werden also nichts erreichen, wenn Sie das "*" vermeiden.

Mit Index nur scannen

Es ist verlockend, einen kleinen Index und nicht die gesamte Tabelle zu scannen, um die Anzahl der Zeilen zu zählen. Dies ist jedoch in PostgreSQL aufgrund der Multi-Version-Strategie für das Parallelitätsmanagement nicht so einfach. Jede Version der Zeile („Tupel“) enthält Informationen darüber, für welchen Datenbank-Snapshot sie sichtbar ist . Diese (redundanten) Informationen werden jedoch nicht in Indizes gespeichert. Daher reicht es normalerweise nicht aus, die Einträge im Index zu zählen, da PostgreSQL den Tabelleneintrag („Heap-Tupel“) überprüfen muss, um sicherzustellen, dass der Indexeintrag sichtbar ist.

Um dieses Problem zu beheben, hat PostgreSQL eine Sichtbarkeitskarte implementiert , eine Datenstruktur, in der Informationen darüber gespeichert werden, ob alle Tupel in einem Tabellenblock für alle sichtbar sind oder nicht.
Wenn die meisten Blöcke in der Tabelle vollständig sichtbar sind, erfordern Index-Scans keine häufigen Besuche einer Reihe von Tupeln, um die Sichtbarkeit zu bestimmen. Ein solcher Index-Scan wird als "Nur-Index-Scan" bezeichnet, und es ist häufig schneller, einen Index zu scannen, um die Zeilen zu zählen.

Jetzt unterstützt VACUUM die Sichtbarkeitskarte. Stellen Sie daher sicher, dass das automatische Vakuum häufig genug durchgeführt wird, wenn Sie einen kleinen Index verwenden möchten, um die Zählung zu beschleunigen (*).

Pivot-Tabelle verwenden

Ich habe oben geschrieben, dass PostgreSQL die Anzahl der Zeilen in einer Tabelle nicht speichert.

Das Aufrechterhalten einer solchen Zeilenanzahl ist ein großer Aufwand, da dieses Ereignis bei jeder Datenänderung auftritt und sich nicht auszahlt. Das wäre ein schlechtes Geschäft. Da unterschiedliche Anforderungen unterschiedliche Versionen von Zeichenfolgen anzeigen können, muss der Zähler außerdem versioniert werden.

Nichts hindert Sie jedoch daran, einen solchen Zeilenzähler selbst zu implementieren.
Angenommen, Sie möchten die Anzahl der Zeilen in einer mytable verfolgen. Sie können dies wie folgt tun:

START TRANSACTION;
 
CREATE TABLE mytable_count(c bigint);
 
CREATE FUNCTION mytable_count() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP = 'INSERT' THEN
      UPDATE mytable_count SET c = c + 1;
 
      RETURN NEW;
   ELSIF TG_OP = 'DELETE' THEN
      UPDATE mytable_count SET c = c - 1;
 
      RETURN OLD;
   ELSE
      UPDATE mytable_count SET c = 0;
 
      RETURN NULL;
   END IF;
END;$$;
 
CREATE CONSTRAINT TRIGGER mytable_count_mod
   AFTER INSERT OR DELETE ON mytable
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW EXECUTE PROCEDURE mytable_count();
 
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
   FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
 
-- initialize the counter table
INSERT INTO mytable_count
   SELECT count(*) FROM mytable;
 
COMMIT;

Wir machen alles in einer Transaktion, damit keine Datenänderungen bei gleichzeitigen Transaktionen aufgrund einer Ringbedingung „verloren gehen“ können.
Dies wird durch den Befehl CREATE TRIGGER garantiert, der die Tabelle im Modus SHARE ROW EXCLUSIVE sperrt, wodurch alle gleichzeitigen Änderungen verhindert werden.
Der Nachteil ist, dass alle parallelen Datenänderungen warten müssen, bis SELECT count (*) ausgeführt wird.

Dies gibt uns eine sehr schnelle Alternative zu count (*), jedoch auf Kosten der Verlangsamung aller Datenänderungen in der Tabelle. Durch die Verwendung eines verzögerten Einschränkungstriggers wird sichergestellt, dass die Zeilensperre in mytable_count so kurz wie möglich ist, um die Parallelität zu verbessern.

Trotz der Tatsache, dass diese Zählertabelle viele Aktualisierungen erhalten kann, besteht keine GefahrEs gibt kein "Aufblähen der Tabelle" , da dies alles "heiße" Updates (HOT-Updates) sind.

Du brauchst wirklich count (*)

Manchmal ist die beste Lösung, nach einer Alternative zu suchen.

Oft ist die Annäherung gut genug und Sie benötigen nicht die genaue Menge. In diesem Fall können Sie die Punktzahl verwenden, die PostgreSQL zum Planen von Abfragen verwendet:

SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';

Dieser Wert wird sowohl durch Autovakuum als auch durch Autoanalyse aktualisiert und sollte daher niemals 10% überschreiten. Sie können autovacuum_analyze_scale_factor für diese Tabelle reduzieren, damit die Autoanalyse dort häufiger ausgeführt wird.

Schätzen der Anzahl der Abfrageergebnisse

Bisher haben wir untersucht, wie das Zählen von Tabellenzeilen beschleunigt werden kann.

Manchmal müssen Sie jedoch wissen, wie viele Zeilen die SELECT-Anweisung zurückgibt, ohne die Abfrage tatsächlich auszuführen.

Die einzige Möglichkeit, eine genaue Antwort auf diese Frage zu erhalten, besteht darin, die Anfrage zu vervollständigen. Wenn die Note jedoch gut genug ist, können Sie den PostgreSQL-Optimierer verwenden, um sie zu erhalten.

Die folgende einfache Funktion verwendet dynamisches SQL und EXPLAIN , um den Abfrageausführungsplan als Argument zu übergeben, und gibt eine Schätzung der Anzahl der Zeilen zurück:

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
 
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

Verwenden Sie diese Funktion nicht, um nicht vertrauenswürdige SQL-Anweisungen zu verarbeiten, da sie von Natur aus anfällig für SQL-Injection ist.

Schneller POSTGRESQL COUNT machen (*)