Я ищу способ найти количество строк для всех моих таблиц в Postgres. Я знаю, что могу делать это за одной таблицей за раз:
SELECT count(*) FROM table_name;
но я бы хотел увидеть количество строк для всех таблиц, а затем упорядочить их, чтобы получить представление о том, насколько велики все мои таблицы.
Есть три способа получить этот счет, каждый со своими компромиссами.
Если вам нужен истинный счет, вам нужно выполнить оператор 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. Для основных целей подсчета просто для того, чтобы увидеть, насколько велики вещи в целом, либо должны быть достаточно точными.
Вот решение, которое не требует функций для получения точного подсчета для каждой таблицы:
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().
Чтобы получить оценки, см. ответ Грега Смита.
Чтобы получить точные подсчеты, другие ответы до сих пор преследуются некоторыми проблемами, некоторые из которых серьезны (см. ниже). Вот версия, которая, надеюсь, лучше:
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...) - лучшая альтернатива.
Если вы не против потенциально устаревших данных, вы можете получить доступ к той же статистике, которая используется оптимизатором запросов.
Что-то вроде:
SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;
Хакерский, практичный ответ для людей, пытающихся оценить, какой план 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
[..]
Не уверен, что вам подходит в 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
Я обычно не полагаюсь на статистику, особенно в 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;
Я не помню 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(); должно привести к подсчету строк всех ваших таблиц.
Я сделал небольшую вариацию для включения всех таблиц, а также для непубличных таблиц.
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(); для его вызова.
Надеюсь, вы найдете это полезное. Пол
Простые два шага:
(Примечание: не нужно ничего менять - просто скопируйте и вставьте)
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;
Это сработало для меня
ВЫБЕРИТЕ имя схемы, relname, n_live_tup ОТ pg_stat_user_tables ORDER BY n_live_tup DESC;
Мне нравится 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"
}
Я хотел, чтобы итог от всех таблиц + список таблиц с их количеством. Немного похоже на график производительности, на котором было потрачено больше всего времени
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