как узнать количество строк в таблице postgresql

PostgreSQL COUNT функция

Функция COUNT

Функция COUNT в PostgreSQL подсчитывает количество строк или значений, отличных от NULL, для определенного столбца таблицы. Когда звездочка (*) используется с функцией count, возвращается общее количество строк.

Синтаксис:

параметры

Содержание:

Пример функции PostgreSQL COUNT

Иллюстрированная презентация PostgreSQL COUNT ()

PostgreSQL COUNT для определенного столбца

объяснение

В приведенном выше примере показано, что комиссию получают только 3 сотрудника, поскольку функция COUNT игнорировала значения NULL.

PostgreSQL COUNT DISTINCT

объяснение

В приведенном выше примере показано, что только 6 строк возвращают данные из таблицы employee, поскольку используется предложение DISTINCT. Предложение DISTINCT исключает повторение каждого имени проекта и возвращает только один раз.

Иллюстрированная презентация PostgreSQL COUNT DISTINCT

PostgreSQL COUNT с GROUP BY

Пример таблицы: сотрудники

Иллюстрированная презентация PostgreSQL COUNT с GROUP BY

PostgreSQL COUNT с WHERE CLAUSE

Пример таблицы: сотрудники

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

Иллюстрированная презентация PostgreSQL COUNT с WHERE

PostgreSQL COUNT с предложением HAVING

Пример таблицы: сотрудники

Если мы хотим получить те назначения, на которых работают как минимум 5 сотрудников, и получать ежемесячную зарплату ниже 12000, можно использовать следующий SQL.

Иллюстрированная презентация PostgreSQL COUNT с HAVING

PostgreSQL COUNT с GROUP BY и ORDER BY

Пример таблицы: сотрудники

Следующий запрос вернет обозначение, где по крайней мере 5 сотрудников работают с максимальной зарплатой ниже 12000, и количество сотрудников для каждого назначения в порядке убывания.

Предыдущая: Агрегатные функции
Следующий: SUM

Источник

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

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

Можно ли прекратить считать, как только моя постоянная стоимость будет превышена?

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

Вместо точного подсчета ( медленно с большими таблицами):

Вы получите такую ​​близкую оценку ( очень быстро ):

Еще лучше

Или еще лучше

Используйте to_regclass(‘myschema.mytable’) в Postgres 9.4+, чтобы избежать исключений для недопустимых имен таблиц:

TABLESAMPLE SYSTEM (n) в Postgres 9.5+

Это только смотрит на случайный выбор n % ( 1 в примере) блоков и подсчитывает строки в нем. Более крупный образец увеличивает стоимость и уменьшает ошибку, ваш выбор. Точность зависит от большего количества факторов:

В большинстве случаев оценка pg_class будет быстрее и точнее.

Ответ на актуальный вопрос

Во-первых, мне нужно знать количество строк в этой таблице, если общее количество больше некоторой предопределенной константы,

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

Да. Вы можете использовать подзапрос с LIMIT :

Источник

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

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

можно ли прекратить подсчет, как только мое постоянное значение превзошли?

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

7 ответов

подсчет строк в большой таблицы, как известно, медленные в PostgreSQL. Чтобы получить точное число, он должен сделать полный подсчет строк из-за природы MVCC. Есть способ ускорить это драматически если число не должны быть точно как это, кажется, в вашем случае.

вместо точно count (медленно С большой таблицы):

вы получаете близкую оценку, как это (очень быстро):

насколько близка оценка зависит от того, выполняете ли вы ANALYZE

Я сделал это один раз в приложении postgres, запустив:

затем исследуя выход с регулярным выражением или аналогичной логикой. Для простого выбора * первая строка вывода должна выглядеть примерно так:

можно использовать rows=(\d+) значение как приблизительная оценка количества строк, которые будут возвращены, тогда только фактическое SELECT COUNT(*) если оценка, скажем, меньше 1.5 x ваш порог (или любое число, которое вы считаете имеет смысл для вашего приложение.)

в зависимости от сложности вашего запроса, это число может становиться все менее и менее точный. На самом деле, в моем приложении, когда мы добавили соединения и сложные условия, это стало настолько неточным, что было совершенно бесполезно, даже знать, как в пределах 100, сколько строк мы бы вернули, поэтому нам пришлось отказаться от этой стратегии.

но если ваш запрос достаточно прост, что Pg может предсказать в пределах разумной погрешности, сколько строк он вернет, он может сработать на вас.

в Oracle вы можете использовать rownum чтобы ограничить количество возвращаемых строк. Я предполагаю, что подобная конструкция существует и в других SQLs. Таким образом, в приведенном примере вы можете ограничить количество строк, возвращаемых 500001, и применить count(*) затем:

вы можете получить количество по приведенному ниже запросу (без * или любых имен столбцов).

насколько широк текстовый столбец?

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

Если возможно, измените схему, чтобы удалить дублирование текстовых данных. Таким образом, подсчет будет происходить в узком поле внешнего ключа в таблице «много».

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

ваш исходный вопрос не совсем соответствует вашей редактирование. Я не уверен, знаете ли вы, что COUNT при использовании с GROUP BY возвращает количество элементов в группе, а не количество элементов во всей таблице.

для SQL Server (2005 или выше) быстрый и надежный метод:

подробности о sys.dm_db_partition_stats объясняются в MSDN

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

index_id=0-неупорядоченная таблица (куча), а index_id=1-упорядоченная таблица (кластеризованный индекс)

еще более быстрые (но ненадежные) методы детализированы здесь.

Вы можете использовать ниже запроса, чтобы найти количество строк.

используя pg_class:

использование pg_stat_user_tables:

Источник

15 полезных команд PostgreSQL

В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.

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

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

По аналогии с получением размера базы данных размер данных таблицы можно вычислить с помощью соответствующей функции:

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

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

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:

Активность пользователя

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

Работа с данными и полями таблиц

Удаление одинаковых строк

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

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

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

Последний запрос требователен к ресурсам, поэтому будьте аккуратны при его выполнении на рабочем проекте.

7–9 сентября, Онлайн, От 12 000 до 30 000 ₽

Теперь рассмотрим случай, когда повторяются значения полей.

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

Если данные важны, то сначала нужно найти записи с дубликатами:

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

В результате всё прошло без ошибок:

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

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

Второй способ
Получаем имя последовательности, связанной с customer_id :

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит NULL :

Количество уникальных строк по указанному полю:

Использование транзакций

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

Просмотр и завершение исполняемых запросов

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

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

Для того, чтобы прекратить работу запроса, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:

Изменим расположение на другое с помощью команды:

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

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

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

Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

Источник

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

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

Можно ли остановить подсчет, как только мое постоянное значение будет превзойдено?

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

ОТВЕТЫ

Ответ 1

Подсчет строк в больших таблицах, как известно, медленный в PostgreSQL. Чтобы получить точный номер, он должен делать полное количество строк из-за природы MVCC. Существует способ ускорить это резко, если счетчик не должен быть точным, как кажется, в вашем случае. p >

Вместо получения точного счета ( медленный с большими таблицами):

Вы получите близкую оценку (чрезвычайно быстро):

Еще лучше

Или еще лучше

Быстрее, проще, безопаснее, элегантнее. См. Руководство по Типы идентификаторов объектов.

Используйте to_regclass(‘myschema.mytable’) в Postgres 9.4+, чтобы избежать исключений для неверных имен таблиц:

TABLESAMPLE SYSTEM (n) в Postgres 9.5 +

Как @a_horse прокомментировал, новое добавленное предложение для команды SELECT может быть полезно, если статистика в pg_class по какой-то причине не достаточно актуальна. Например:

Это показывает только случайный n% ( 1 в примере) выбор блоков и подсчет строк в нем. Более крупный образец увеличивает стоимость и уменьшает ошибку, ваш выбор. Точность зависит от большего количества факторов:

В большинстве случаев оценка от pg_class будет быстрее и точнее.

Ответ на фактический вопрос

Во-первых, мне нужно знать количество строк в этой таблице, если общее количество count больше некоторой предопределенной константы,

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

Да. Вы можете использовать подзапрос с LIMIT :

Ответ 2

Я сделал это один раз в приложении postgres, запустив:

Затем рассмотрим вывод с помощью регулярного выражения или аналогичной логики. Для простого SELECT * первая строка вывода должна выглядеть примерно так:

В зависимости от сложности вашего запроса это число может становиться все менее и менее точным. Фактически, в моем приложении, когда мы добавляли объединения и сложные условия, он стал настолько неточным, что он был совершенно бесполезным, даже чтобы знать, как в пределах 100 столетий, сколько мы вернули бы, поэтому нам пришлось отказаться от этой стратегии.

Но если ваш запрос достаточно прост, что Pg может предсказать с некоторым разумным пределом ошибки, сколько строк оно вернется, оно может сработать для вас.

Ответ 3

Ответ 4

Насколько широк текстовый столбец?

С GROUP BY не так много можно сделать, чтобы избежать сканирования данных (по крайней мере, сканирование индекса).

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

Альтернативно, создавая сгенерированный столбец с HASH текста, тогда GROUP BY hash column. Опять же, это уменьшение рабочей нагрузки (сканирование через узкий индекс столбца)

Ваш оригинальный вопрос не совсем соответствовал вашим изменениям. Я не уверен, знаете ли вы, что COUNT, когда используется с GROUP BY, вернет количество элементов для каждой группы, а не количество элементов во всей таблице.

Ответ 5

Для SQL Server (2005 или выше) быстрый и надежный метод:

Подробности о sys.dm_db_partition_stats объясняются в MSDN

Запрос добавляет строки из всех частей (возможно) разделенной таблицы.

Более быстрые (но ненадежные) методы подробно описаны здесь.

Ответ 6

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

Использование pg_class:

Использование pg_stat_user_tables:

Источник