Wie finden Sie die Zeilenzahl für alle Ihre Tabellen in Postgres

Question

Ich suche nach einer Möglichkeit, die Zeilenzahl für alle meine Tabellen in Postgres zu ermitteln. Ich weiß, dass ich dies für eine Tabelle nach der anderen tun kann mit:

SELECT count(*) FROM table_name;

aber ich würde gerne die Zeilenzahl für alle Tabellen sehen und dann danach ordnen, um eine Vorstellung davon zu bekommen, wie groß meine Tabellen sind.

Answer 1

Es gibt drei Möglichkeiten, diese Art von Zählung zu erhalten, jede mit ihren eigenen Nachteilen.

Wenn Sie eine echte Zählung wünschen, müssen Sie die SELECT-Anweisung wie diejenige, die Sie für jede Tabelle verwendet haben, ausführen. Das liegt daran, dass PostgreSQL Informationen über die Sichtbarkeit von Zeilen in der Zeile selbst und nirgendwo sonst speichert, so dass eine genaue Zählung nur relativ zu einer Transaktion erfolgen kann. Sie erhalten eine Zählung dessen, was diese Transaktion zu dem Zeitpunkt sieht, zu dem sie ausgeführt wird. Sie könnten dies automatisieren, um jede Tabelle in der Datenbank zu überprüfen, aber wahrscheinlich brauchen Sie diese Genauigkeit nicht oder wollen nicht so lange warten.

WITH tbl AS
  (SELECT table_schema,
          TABLE_NAME
   FROM information_schema.tables
   WHERE TABLE_NAME not like 'pg_%'
     AND table_schema in ('public'))
SELECT table_schema,
       TABLE_NAME,
       (xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

Der zweite Ansatz sieht vor, dass der Statistik-Collector grob erfasst, wie viele Zeilen zu jedem Zeitpunkt "live" sind (nicht gelöscht oder durch spätere Aktualisierungen überholt). Dieser Wert kann bei hoher Aktivität ein wenig abweichen, ist aber im Allgemeinen eine gute Schätzung:

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

Das kann Ihnen auch zeigen, wie viele Zeilen tot sind, was an sich schon eine interessante Zahl zur Überwachung ist.

Die dritte Möglichkeit ist, dass der Systembefehl ANALYZE, der seit PostgreSQL 8.3 regelmäßig vom Autovacuum-Prozess ausgeführt wird, um die Tabellenstatistiken zu aktualisieren, ebenfalls eine Zeilenschätzung berechnet. Sie können diese wie folgt abrufen:

SELECT 
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r' 
ORDER BY reltuples DESC;

Welche dieser Abfragen besser geeignet ist, ist schwer zu sagen. Normalerweise treffe ich diese Entscheidung danach, ob es nützlichere Informationen gibt, die ich auch innerhalb von pg_class oder innerhalb von pg_stat_user_tables verwenden möchte. Für grundlegende Zählungszwecke, um zu sehen, wie groß die Dinge im Allgemeinen sind, sollten beide genau genug sein.

Answer 2

Hier ist eine Lösung, die keine Funktionen erfordert, um eine genaue Zählung für jede Tabelle zu erhalten:

select table_schema, 
       table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema, 
         query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public' --<< change here for the schema you want
) t

query_to_xml führt die übergebene SQL-Abfrage aus und gibt ein XML mit dem Ergebnis zurück (die Zeilenzahl für diese Tabelle). Die äußere xpath() extrahiert dann die Zählinformationen aus dieser XML-Datei und wandelt sie in eine Zahl um

Die abgeleitete Tabelle ist nicht wirklich notwendig, macht aber die xpath() etwas leichter zu verstehen - ansonsten ist das Ganze query_to_xml() müsste an das Programm xpath() Funktion.

Answer 3

Um Schätzungen zu erhalten, siehe Die Antwort von Greg Smith .

Um genaue Zahlen zu erhalten, sind die anderen Antworten bisher mit einigen, zum Teil schwerwiegenden Problemen behaftet (siehe unten). Hier ist eine Version, die hoffentlich besser ist:

CREATE FUNCTION rowcount_all(schema_name text default 'public')
  RETURNS table(table_name text, cnt bigint) as
$$
declare
 table_name text;
begin
  for table_name in SELECT c.relname FROM pg_class c
    JOIN pg_namespace s ON (c.relnamespace=s.oid)
    WHERE c.relkind = 'r' AND s.nspname=schema_name
  LOOP
    RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
       table_name, schema_name, table_name);
  END LOOP;
end
$$ language plpgsql;

Sie nimmt einen Schemanamen als Parameter an, oder public wenn kein Parameter angegeben wird.

Um mit einer bestimmten Liste von Schemata oder einer Liste aus einer Abfrage zu arbeiten, ohne die Funktion zu ändern, kann sie innerhalb einer Abfrage wie folgt aufgerufen werden:

WITH rc(schema_name,tbl) AS (
  select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

Dies erzeugt eine dreispaltige Ausgabe mit dem Schema, der Tabelle und der Anzahl der Zeilen.

Nun gibt es einige Probleme in den anderen Antworten, die durch diese Funktion vermieden werden:

• Tabellen- und Schemanamen sollten nicht ohne Anführungszeichen in ausführbares SQL eingefügt werden, entweder mit quote_ident oder mit dem moderneren format() Funktion mit ihrer %I Formatzeichenfolge. Andernfalls könnte eine böswillige Person ihre Tabelle so nennen tablename;DROP TABLE other_table was als Tabellenname durchaus zulässig ist.

• Auch ohne die Probleme mit SQL-Injection und komischen Zeichen können Tabellennamen in Varianten existieren, die sich durch Groß- und Kleinschreibung unterscheiden. Wenn eine Tabelle benannt ist ABCD und eine weitere abcd die SELECT count(*) FROM... muss einen Namen in Anführungszeichen verwenden, sonst wird ABCD und zählen abcd zweimal. Die %I des Formats geschieht dies automatisch.

• information_schema.tables listet zusätzlich zu den Tabellen auch benutzerdefinierte zusammengesetzte Typen auf, selbst wenn table_type auf 'BASE TABLE' (!). Infolgedessen können wir keine Iteration auf information_schema.tables sonst riskieren wir, dass select count(*) from name_of_composite_type und das würde scheitern. OTOH pg_class where relkind='r' sollte immer gut funktionieren.

• Der Typ von COUNT() ist bigint , nicht int . Es können Tabellen mit mehr als 2,15 Milliarden Zeilen vorhanden sein (eine Zählung(*) für diese Tabellen ist jedoch eine schlechte Idee).

• Damit eine Funktion eine Ergebnismenge mit mehreren Spalten zurückgeben kann, muss kein permanenter Typ erstellt werden. RETURNS TABLE(definition...) ist eine bessere Alternative.

Answer 4

Die praktische Lösung für Leute, die herausfinden wollen, welchen Heroku-Plan sie brauchen und nicht darauf warten können, dass der langsame Zeilenzähler von Heroku aktualisiert wird:

Grundsätzlich möchten Sie Folgendes ausführen \dt en psql Kopieren Sie das Ergebnis in Ihren bevorzugten Texteditor (es sieht dann so aus:

 public | auth_group                     | table | axrsosvelhutvw
 public | auth_group_permissions         | table | axrsosvelhutvw
 public | auth_permission                | table | axrsosvelhutvw
 public | auth_user                      | table | axrsosvelhutvw
 public | auth_user_groups               | table | axrsosvelhutvw
 public | auth_user_user_permissions     | table | axrsosvelhutvw
 public | background_task                | table | axrsosvelhutvw
 public | django_admin_log               | table | axrsosvelhutvw
 public | django_content_type            | table | axrsosvelhutvw
 public | django_migrations              | table | axrsosvelhutvw
 public | django_session                 | table | axrsosvelhutvw
 public | exercises_assignment           | table | axrsosvelhutvw

), dann führen Sie eine Regex-Suche und Ersetzung wie folgt durch:

^[^|]*\|\s+([^|]*?)\s+\| table \|.*$

zu:

select '\1', count(*) from \1 union/g

das Ihnen etwas sehr Ähnliches wie dieses Ergebnis liefert:

select 'auth_group', count(*) from auth_group union
select 'auth_group_permissions', count(*) from auth_group_permissions union
select 'auth_permission', count(*) from auth_permission union
select 'auth_user', count(*) from auth_user union
select 'auth_user_groups', count(*) from auth_user_groups union
select 'auth_user_user_permissions', count(*) from auth_user_user_permissions union
select 'background_task', count(*) from background_task union
select 'django_admin_log', count(*) from django_admin_log union
select 'django_content_type', count(*) from django_content_type union
select 'django_migrations', count(*) from django_migrations union
select 'django_session', count(*) from django_session
;

(Sie müssen die letzte union und fügen Sie das Semikolon am Ende manuell hinzu)

Führen Sie es ein psql und Sie sind fertig.

            ?column?            | count
--------------------------------+-------
 auth_group_permissions         |     0
 auth_user_user_permissions     |     0
 django_session                 |  1306
 django_content_type            |    17
 auth_user_groups               |   162
 django_admin_log               |  9106
 django_migrations              |    19
[..]

Answer 5

Wenn Sie sich nicht an möglicherweise veralteten Daten stören, können Sie auf die gleichen Statistiken zugreifen, die auch der Abfrageoptimierer verwendet .

Etwa so:

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;