Как найти количество строк для всех ваших таблиц в Postgres

Я ищу способ найти количество строк для всех моих таблиц в Postgres. Я знаю, что могу делать это за одной таблицей за раз:

SELECT count(*) FROM table_name;

но я бы хотел увидеть количество строк для всех таблиц, а затем упорядочить их, чтобы получить представление о том, насколько велики все мои таблицы.

Ответы

Ответ 1

Есть три способа получить этот счет, каждый со своими компромиссами.

Если вам нужен истинный счет, вам нужно выполнить оператор SELECT, как тот, который вы использовали против каждой таблицы. Это связано с тем, что PostgreSQL сохраняет информацию о видимости строки в самой строке, а не где-либо еще, поэтому любое точное количество может относиться только к некоторой транзакции. Вы получаете подсчет того, что эта транзакция видит в момент времени, когда она выполняется. Вы можете автоматизировать это, чтобы работать с каждой таблицей в базе данных, но вам, вероятно, не нужен этот уровень точности или вы хотите долго ждать.

Второй подход отмечает, что сборщик статистики отслеживает примерно количество строк в реальном времени (не удаленных или устаревших позже) в любое время. Это значение может быть немного уменьшено при активной активности, но, как правило, хорошая оценка:

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

Это также может показать вам, сколько строк мертво, что само по себе является интересным номером для мониторинга.

Третий способ заключается в том, что команда ANALYZE, выполняемая автоматически в процессе автоаккумуляции, как и PostgreSQL 8.3 для обновления статистики таблицы, также вычисляет оценку строки. Вы можете захватить это так:

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;

Какой из этих запросов лучше использовать, трудно сказать. Обычно я принимаю это решение на основе того, есть ли более полезная информация, которую я также хочу использовать внутри pg_class или внутри pg_stat_user_tables. Для основных целей подсчета просто для того, чтобы увидеть, насколько велики вещи в целом, либо должны быть достаточно точными.

Ответ 2

Вот решение, которое не требует функций для получения точного подсчета для каждой таблицы:

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 выполнит переданный SQL-запрос и вернет XML с результатом (количество строк для этой таблицы). Внешний xpath() затем извлечет информацию о счете из этого xml и преобразует ее в число

Производная таблица на самом деле не нужна, но сделать xpath() немного легче понять, иначе целая query_to_xml() должна быть передана функции xpath().

Ответ 3

Чтобы получить оценки, см. ответ Грега Смита.

Чтобы получить точные подсчеты, другие ответы до сих пор преследуются некоторыми проблемами, некоторые из которых серьезны (см. ниже). Вот версия, которая, надеюсь, лучше:

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;

В качестве параметра берется имя схемы или public, если параметр не указан.

Чтобы работать с определенным списком схем или списком, поступающим из запроса без изменения функции, его можно вызвать из запроса, подобного этому:

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;

Это создает вывод из 3 столбцов с указанием схемы, таблицы и строк.

Теперь вот некоторые вопросы в других ответах, которые эта функция избегает:

Имена таблиц и схем не должны вводиться в исполняемый SQL без кавычек, либо с помощью quote_ident, либо с более современной функцией format() со строкой формата %I. В противном случае какой-либо злонамеренный человек может назвать свою таблицу tablename;DROP TABLE other_table, которая идеально подходит для имени таблицы.
Даже без проблем с SQL-инъекциями и смешными символами имя таблицы может существовать в вариантах, отличающихся в зависимости от случая. Если таблица имеет имя ABCD и еще один ABCD, SELECT count(*) FROM... должен использовать цитированное имя, иначе оно пропустит ABCD и посчитает ABCD дважды. Формат %I делает это автоматически.
information_schema.tables перечисляет пользовательские составные типы в дополнение к таблицам, даже если table_type 'BASE TABLE' (!). Как следствие, мы не можем выполнять итерацию на information_schema.tables, иначе мы рискуем иметь select count(*) from name_of_composite_type, и это потерпит неудачу. OTOH pg_class where relkind='r' должен всегда работать нормально.
Тип COUNT() - bigint, а не int. Таблицы с более чем 2,15 миллиардами строк могут существовать (однако при этом количество ошибок (*) - это плохая идея).
Нельзя создавать постоянный тип для функции, возвращающей набор результатов с несколькими столбцами. RETURNS TABLE(definition...) - лучшая альтернатива.

Ответ 4

Если вы не против потенциально устаревших данных, вы можете получить доступ к той же статистике, которая используется оптимизатором запросов.

Что-то вроде:

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

Ответ 5

Хакерский, практичный ответ для людей, пытающихся оценить, какой план Heroku им нужен, и не может дождаться обновления счетчика медленных рядов героку:

В основном вы хотите запустить \dt в psql, скопируйте результаты в ваш любимый текстовый редактор (он будет выглядеть так:

 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

), затем запустите поиск по регулярному выражению и замените его следующим образом:

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

чтобы:

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

что даст вам нечто очень похожее на это:

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
;

(Вам нужно будет удалить последний union и добавить точку с запятой в конце вручную)

Запустите его в psql и все готово.

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

Ответ 6

Не уверен, что вам подходит в bash, но 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

Ответ 7

Я обычно не полагаюсь на статистику, особенно в 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;

Ответ 8

Я не помню URL-адрес, откуда я его собрал. Но надеюсь, что это вам поможет:

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;

Выполнение select count_em_all(); должно привести к подсчету строк всех ваших таблиц.

Ответ 9

Я сделал небольшую вариацию для включения всех таблиц, а также для непубличных таблиц.

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;

используйте select count_em_all(); для его вызова.

Надеюсь, вы найдете это полезное. Пол

Ответ 10

Простые два шага:
(Примечание: не нужно ничего менять - просто скопируйте и вставьте)
1. создать функцию

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. Запустите этот запрос, чтобы получить количество строк для всех таблиц

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;

или же

Чтобы получить количество строк в табличном виде

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;

Ответ 11

Это сработало для меня

ВЫБЕРИТЕ имя схемы, relname, n_live_tup ОТ pg_stat_user_tables ORDER BY n_live_tup DESC;

Ответ 12

Мне нравится Daniel Vérité answer. Но когда вы не можете использовать оператор CREATE, вы можете либо использовать bash решение, либо, если вы пользователь Windows, один из них:

# 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"
}

Ответ 13

Я хотел, чтобы итог от всех таблиц + список таблиц с их количеством. Немного похоже на график производительности, на котором было потрачено больше всего времени

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

Вы, конечно, могли бы добавить предложение LIMIT к результатам в этой версии, чтобы получить как самых крупных преступников n, так и общее количество.

Одна вещь, на которую следует обратить внимание, это то, что вам нужно немного подождать после массового импорта. Я проверил это, просто добавив 5000 строк в базу данных в нескольких таблицах, используя реальные данные импорта. Он показывал 1800 записей в течение минуты (вероятно, настраиваемое окно)

Это основано на fooobar.com/info/25867/... работе, поэтому спасибо вам и признание за запрос, который будет использоваться в CTE