Как ускорить подсчет строк в таблице PostgreSQL?

Для очень быстрой оценки:

SELECT reltuples FROM pg_class WHERE relname = 'my_table';

Однако есть несколько предостережений. Во-первых, relname не обязательно уникален в pg_class. Может быть несколько таблиц с одним и тем же relname в нескольких схемах базы данных. Чтобы быть однозначным:

SELECT reltuples::bigint FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;

Если вы не уточняете имя таблицы по схеме, приведение к regclass наблюдает текущую search_path, чтобы выбрать наилучшее соответствие. И если таблица не существует (или не видна) ни в одной из схем в search_path, вы получите сообщение об ошибке. См. Типы идентификаторов объектов в руководстве. .

Приведение к bigint прекрасно форматирует число real, особенно для больших чисел.

Кроме того, reltuples может быть более или менее устаревшим. Есть способы компенсировать это в некоторой степени. См. этот более поздний ответ с новыми и улучшенными параметрами:

• Быстрый способ узнать количество строк таблицы в PostgreSQL

И запрос на pg_stat_user_tables во много раз медленнее (хотя все же намного быстрее полного подсчета), так как это просмотр пары таблиц.

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

SELECT reltuples::bigint AS estimate 
FROM pg_class 
WHERE relname='tableName';

и это очень быстро, результаты не плавающие, но все же близкие оценки.

• reltuples — это столбец из таблицы pg_class, он содержит данные о «количестве строк в таблице. Это только оценка, используемая планировщиком. )
• Каталог pg_class каталогизирует таблицы и большую часть всего остального, что имеет столбцы или иным образом похоже на таблицу. Сюда входят индексы (но см. также pg_index), последовательности, представления, составные типы и некоторые виды специальных отношений (вручную).
• "Почему "SELECT count(*) FROM bigtable;" работает медленно?" : http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F

Помимо запуска COUNT() для индексированного поля (которым, надеюсь, является «id»), следующим лучшим способом было бы фактически кэшировать количество строк в какой-либо таблице с помощью триггера на INSERT. Естественно, вместо этого вы будете проверять кеш.

Для приближения вы можете попробовать это (от https://wiki.postgresql.org/wiki/Count_estimate):

select reltuples from pg_class where relname='tablename';

Вы можете запросить точное значение счетчика в таблице, просто используя триггер AFTER INSERT OR DELETE Что-то вроде этого

CREATE TABLE  tcounter(id serial primary key,table_schema text, table_name text, count serial);

insert into tcounter(table_schema, table_name,count) select 'my_schema', 'my_table', count(*) from my_schema.my_table;

и использовать триггер

CREATE OR REPLACE FUNCTION ex_count()
RETURNS trigger AS
$BODY$
BEGIN
    IF (TG_OP='INSERT') THEN
      UPDATE tcounter set count = count + 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
    ELSIF  (TG_OP='DELETE') THEN
      UPDATE tcounter set count = count - 1 where table_schema = TG_TABLE_SCHEMA::TEXT and table_name = TG_TABLE_NAME::TEXT;
    END IF;
RETURN NEW;
END$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER tg_counter  AFTER INSERT OR DELETE
  ON my_schema.my_table  FOR EACH ROW  EXECUTE PROCEDURE ex_count();

И попросите посчитать

select * from tcounter where table_schema =  'my_schema' and table_name = 'my_table'

это означает, что вы выбираете count(*) один раз для инициализации первой записи

Вы можете получить оценку из системной таблицы "pg_stat_user_tables".

select schemaname, relname, n_live_tup 
from pg_stat_user_tables 
where schemaname = 'your_schema_name'
and relname = 'your_table_name';

Если ваша база данных невелика, вы можете получить оценку всех ваших таблиц, например, предложенную @mike-sherrill-cat-recall. Однако эта команда выведет список всех таблиц.

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

Вывод будет примерно таким:

 schemaname |      relname       | n_live_tup
------------+--------------------+------------
 public     | items              |      21806
 public     | tags               |      11213
 public     | sessions           |       3269
 public     | users              |        266
 public     | shops              |        259
 public     | quantities         |         34
 public     | schema_migrations  |         30
 public     | locations          |          8
(8 rows)