Jak znaleźć liczbę wierszy dla wszystkich swoich tabel w Postgres

Szukam sposobu, aby znaleźć liczbę wierszy dla wszystkich moich tabel w Postgres. Wiem, że mogę zrobić ten jeden stół na raz za pomocą:

SELECT count(*) FROM table_name;

ale chciałbym zobaczyć liczbę wierszy dla wszystkich tabel, a następnie uporządkować według tego, aby dowiedzieć się, jak duże są wszystkie moje tabele.

Istnieją trzy sposoby na obliczenie tego rodzaju, każdy z własnymi kompromisami.

Jeśli chcesz prawdziwej liczby, musisz wykonać instrukcję SELECT, taką jak ta, której użyłeś dla każdej tabeli. Wynika to z faktu, że PostgreSQL przechowuje informacje o widoczności wiersza w samym wierszu, a nie gdziekolwiek indziej, więc każda dokładna liczba może dotyczyć tylko niektórych transakcji. Otrzymujesz informację o tym, co widzi ta transakcja w momencie jej wykonania. Możesz to zautomatyzować, aby działało z każdą tabelą w bazie danych, ale prawdopodobnie nie potrzebujesz takiego poziomu dokładności lub nie chcesz czekać tak długo.

Drugie podejście zauważa, że ​​moduł gromadzący statystyki śledzi w przybliżeniu, ile wierszy jest „na żywo” (nie usuwanych ani nieaktualnych przez późniejsze aktualizacje) w dowolnym momencie. Ta wartość może być nieco wyłączona przy dużym obciążeniu, ale ogólnie jest dobrym oszacowaniem:

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

Może to również pokazać liczbę martwych wierszy, co samo w sobie jest interesującą liczbą do monitorowania.

Trzecim sposobem jest zwrócenie uwagi na to, że systemowa komenda ANALYZE, która jest regularnie wykonywana przez proces autovacuum od PostgreSQL 8.3 w celu aktualizacji statystyk tabeli, również oblicza oszacowanie wiersza. Możesz pobrać ten w ten sposób:

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;

Trudno powiedzieć, które z tych zapytań jest lepsze. Zwykle podejmuję tę decyzję na podstawie tego, czy są bardziej przydatne informacje, których chcę również użyć w pg_class, czy w pg_stat_user_tables. Dla podstawowych celów liczenia, aby zobaczyć, jak duże są rzeczy w ogóle, oba powinny być wystarczająco dokładne.

Oto rozwiązanie, które nie wymaga funkcji, aby uzyskać dokładną liczbę dla każdej tabeli:

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_xmluruchomi przekazane zapytanie SQL i zwróci XML z wynikiem (liczba wierszy dla tej tabeli). Zewnętrznyxpath() wyodrębni następnie informacje o liczbie z tego xml i skonwertuje je na liczbę

Tabela pochodna nie jest tak naprawdę konieczna, ale xpath()nieco łatwiej ją zrozumieć - w przeciwnym razie całość query_to_xml()musiałaby zostać przekazana do xpath()funkcji.

Aby uzyskać oszacowania, zobacz odpowiedź Grega Smitha .

Aby uzyskać dokładne liczby, inne dotychczasowe odpowiedzi są nękane niektórymi problemami, niektóre z nich poważnymi (patrz poniżej). Oto wersja, która, mam nadzieję, jest lepsza:

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;

Przyjmuje nazwę schematu jako parametr lub publicjeśli nie podano żadnego parametru.

Aby pracować z określoną listą schematów lub listą pochodzącą z zapytania bez modyfikowania funkcji, można ją wywołać z poziomu zapytania takiego:

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;

Daje to wynik 3-kolumnowy ze schematem, tabelą i wierszami.

Oto kilka problemów w innych odpowiedziach, których ta funkcja unika:

• Nazwy tabel i schematów nie powinny być wstrzykiwane do wykonywalnego SQL bez cytowania, ani za pomocą, quote_identani za pomocą bardziej nowoczesnej format()funkcji z jej %Iciągiem formatu. W przeciwnym razie złośliwa osoba może nazwać swoją tabelę, tablename;DROP TABLE other_tablektóra jest całkowicie poprawna jako nazwa tabeli.

• Nawet bez iniekcji SQL i problemów z zabawnymi znakami nazwa tabeli może występować w wariantach różniących się wielkością liter. Jeśli tabela jest nazwana ABCDi inna abcd, SELECT count(*) FROM...musi użyć cytowanej nazwy, w przeciwnym razie pominie ABCDi policzy abcddwa razy. %IFormatu robi to automatycznie.

• information_schema.tableswyświetla niestandardowe typy kompozytów oprócz tabel, nawet gdy typ_tabeli to 'BASE TABLE'(!). W związku z tym nie możemy powtarzać information_schema.tables, w przeciwnym razie ryzykujemy, select count(*) from name_of_composite_typeże to się nie powiedzie. OTOH pg_class where relkind='r'powinien zawsze działać dobrze.

• Typ COUNT () to bigintnie int. Mogą istnieć tabele z ponad 2,15 miliardami wierszy (choć liczenie (*) jest na nich złym pomysłem).

• Nie trzeba tworzyć typu stałego, aby funkcja zwracała zestaw wyników z kilkoma kolumnami. RETURNS TABLE(definition...)jest lepszą alternatywą.

Jeśli nie przeszkadza Ci potencjalnie nieaktualne dane, możesz uzyskać dostęp do tych samych statystyk, które są wykorzystywane przez optymalizator zapytań .

Coś jak:

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

Chwytliwa, praktyczna odpowiedź dla osób, które próbują ocenić, jakiego planu Heroku potrzebują i nie mogą się doczekać odświeżenia wolnego licznika wierszy heroku:

Zasadniczo chcesz uruchomić \dtw psqlskopiuj wyniki do swojego ulubionego edytora tekstu (będzie wyglądać następująco:

 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

), a następnie uruchom wyszukiwanie wyrażeń regularnych i zamień w następujący sposób:

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

do:

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

co da ci coś bardzo podobnego do tego:

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
;

(Musisz usunąć ostatni unioni ręcznie dodać średnik na końcu)

Uruchom psqli gotowe.

            ?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
[..]

Nie jestem pewien, czy odpowiedź w bash jest dla Ciebie akceptowalna, ale FWIW ...

PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
            SELECT   table_name
            FROM     information_schema.tables
            WHERE    table_type='BASE TABLE'
            AND      table_schema='public'
            \""
TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")

for TABLENAME in $TABLENAMES; do
    PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
                SELECT   '$TABLENAME',
                         count(*) 
                FROM     $TABLENAME
                \""
    eval "$PGCOMMAND"
done

Zwykle nie polegam na statystykach, szczególnie w PostgreSQL.

SELECT table_name, dsql2('select count(*) from '||table_name) as rownum
FROM information_schema.tables
WHERE table_type='BASE TABLE'
    AND table_schema='livescreen'
ORDER BY 2 DESC;

CREATE OR REPLACE FUNCTION dsql2(i_text text)
  RETURNS int AS
$BODY$
Declare
  v_val int;
BEGIN
  execute i_text into v_val;
  return v_val;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Nie pamiętam adresu URL, z którego to pobrałem. Ale mam nadzieję, że to powinno ci pomóc:

CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER); 

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 

BEGIN
    FOR t_name IN 
        SELECT 
            c.relname
        FROM
            pg_catalog.pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE 
            c.relkind = ''r''
            AND n.nspname = ''public'' 
        ORDER BY 1 
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname 
            LOOP 
            END LOOP; 

            r.table_name := t_name.relname; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

Wykonanie select count_em_all();powinno dać ci liczbę wierszy wszystkich twoich tabel.

Proste dwa kroki:
(Uwaga: nic nie trzeba zmieniać - wystarczy skopiować wklej)
1. Utwórz funkcję

create function 
cnt_rows(schema text, tablename text) returns integer
as
$body$
declare
  result integer;
  query varchar;
begin
  query := 'SELECT count(1) FROM ' || schema || '.' || tablename;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

2. Uruchom to zapytanie, aby uzyskać liczbę wierszy dla wszystkich tabel

select sum(cnt_rows) as total_no_of_rows from (select 
  cnt_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE') as subq;

lub

Aby uzyskać wiersze, liczy się tabela

select
  table_schema,
  table_name, 
  cnt_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE'
order by 3 desc;

Wprowadziłem niewielką odmianę, aby uwzględnić wszystkie tabele, również dla tabel niepublicznych.

CREATE TYPE table_count AS (table_schema TEXT,table_name TEXT, num_rows INTEGER); 

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 

BEGIN
    FOR t_name IN 
        SELECT table_schema,table_name
        FROM information_schema.tables
        where table_schema !=''pg_catalog''
          and table_schema !=''information_schema''
        ORDER BY 1,2
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name
            LOOP 
            END LOOP; 

            r.table_schema := t_name.table_schema;
            r.table_name := t_name.table_name; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

użyj, select count_em_all();aby to nazwać.

Mam nadzieję, że okaże się to przydatne. Paweł

To zadziałało dla mnie

WYBIERZ schemat, nazwa zmiany, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

Lubię Daniel Verite za odpowiedź . Ale jeśli nie możesz użyć instrukcji CREATE, możesz użyć rozwiązania bash lub, jeśli jesteś użytkownikiem systemu Windows, PowerShell:

# You don't need this if you have pgpass.conf
$env:PGPASSWORD = "userpass"

# Get table list
$tables = & 'C:\Program Files\PostgreSQL\9.4\bin\psql.exe' -U user -w -d dbname -At -c "select table_name from information_schema.tables where table_type='BASE TABLE' AND table_schema='schema1'"

foreach ($table in $tables) {
    & 'C:\path_to_postresql\bin\psql.exe' -U root -w -d dbname -At -c "select '$table', count(*) from $table"
}

Chciałem sumę ze wszystkich tabel + listę tabel z ich liczbą. Trochę jak wykres wydajności, w którym spędzono najwięcej czasu

WITH results AS ( 
  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'
     GROUP BY schemaname, relname, reltuples
)

SELECT * FROM results
UNION
SELECT 'all' AS schemaname, 'all' AS relname, SUM(reltuples) AS "reltuples" FROM results

ORDER BY reltuples DESC

Możesz oczywiście dodać LIMITklauzulę do wyników w tej wersji, aby uzyskać zarówno największych nprzestępców, jak i ogółem.

Jedną z rzeczy, na które należy zwrócić uwagę, jest konieczność pozostawienia go przez chwilę po imporcie masowym. Przetestowałem to, dodając 5000 wierszy do bazy danych w kilku tabelach, używając rzeczywistych danych importu. Pokazywał 1800 rekordów przez około minutę (prawdopodobnie konfigurowalne okno)

Jest to oparte na pracy https://stackoverflow.com/a/2611745/1548557 , więc dziękuję i uznajesz to za zapytanie do użycia w CTE