Проблемы производительности. Особенности подсчета количества строк в таблице данных СУБД Postgres

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

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

На этой встрече вы узнаете:

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

• Почему операция подсчета количества записей (COUNT) в таблице может создавать серьезные проблемы производительности.
• Особенности работы оператора COUNT в СУБД PostgreSQL и почему он может работать медленно даже при наличии индексов.
• Практические варианты оптимизации для случаев, когда необходимо регулярно получать актуальную информацию об объеме данных в таблицах.

С вами Владимир Крючков. Поехали!

Описание класса рассматриваемых задач

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

• Информационные панели: Вывод пользователю общего количества документов (например, «Заказов клиента» или «Реализаций товаров и услуг»).
• Статистика по статусам: Подсчёт количества документов в различных статусах (например, количество отправленных документов ЭДО, готовых к отправке и т.д.).
• Управление задачами: Формирование списка «текущих дел» — количества документов, которые пользователь должен обработать.
• Аналитика и отчётность: Другие сценарии, где явно или косвенно требуется количественная оценка записей в таблице.

Однако при решении таких задач можно столкнуться со сложностями, обусловленными особенностями работы СУБД и наличием ограничений доступа на уровне записей (RLS). В высоконагруженных базах данных это приводит к существенному снижению производительности.

Именно об этих проблемах и способах их решения мы сегодня и поговорим.

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

Анализ проблемы. Часть I

Для получения количества записей в выборке в языке запросов 1С существует оператор КОЛИЧЕСТВО. Рассмотрим пример запроса для подсчёта всех записей в таблице документов "Заказ клиента":

ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК КолВо
ИЗ
    Документ.ЗаказКлиента КАК ЗаказКлиента

В результате выполнения этого запроса мы получим число 832 056. Время выполнения составляет 0,2 с.

Замечание!

Для целей анализа мы отключили возможность распараллеливания запроса, установив в конфигурационном файле СУБД параметр max_parallel_workers_per_gather = 0. Это позволяет упростить представление плана запроса и сразу увидеть общие суммарные затраты на его выполнение, без распределения по потокам.

План данного запроса достаточно простой и состоит из двух узлов, поэтому его можно рассмотреть в текстовом формате:

"Aggregate  (cost=22274.14..22274.15 rows=1 width=8) (actual time=207.545..207.545 rows=1 loops=1)"
"  ->  Index Only Scan using _document581_s_hpk on _document581 t1  (cost=0.42..20193.67 rows=832186 width=0) (actual time=0.111..174.790 rows=832056 loops=1)"
"        Index Cond: (_fld1585 = '0'::numeric)"
"        Heap Fetches: 2702"
"Planning Time: 1.798 ms"
"Execution Time: 207.591 ms"

или после подстановки наименований метаданных 1С:

"Aggregate  (cost=22274.14..22274.15 rows=1 width=8) (actual time=207.545..207.545 rows=1 loops=1)"
"  ->  Index Only Scan using "Документ.ЗаказКлиента.Индекс_20[Ссылка,ОбластьДанныхОсновныеДанные]" on "Документ.ЗаказКлиента" t1  (cost=0.42..20193.67 rows=832186 width=0) (actual time=0.111..174.790 rows=832056 loops=1)"
"        Index Cond: ("ОбластьДанныхОсновныеДанные" = '0'::numeric)"
"        Heap Fetches: 2702"
"Время планирования: 1.798 ms"
"Время выполнения: 207.591 ms"

https://explain.tensor.ru/archive/explain/b322c4edbf6b860c0bba79608420ce45:0:2024-05-20#visio

Рис. 2. Графическое представление плана запроса

Условие отбора по общему реквизиту «Область основных данных» (который для всех записей в нашей базе равен 0) фактически приводит к перебору всех записей таблицы.

Ключевой вывод: чтобы получить точное количество записей в таблице, СУБД необходимо выполнить полное сканирование и перебрать все строки. Как следствие, чем больше записей в таблице, тем медленнее выполняется эта операция.

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

ВЫБРАТЬ
	КОЛИЧЕСТВО(*) КАК КолВо
ИЗ
	РегистрНакопления.ТоварыНаСкладах КАК ТоварыНаСкладах

Результат нам даст уже 12 968 595 строк записей и время выполнения порядка 2,2 с, что практически в 11 раз больше предыдущего результата. Записей больше и время выполнения больше.

"Aggregate  (cost=362170.90..362170.91 rows=1 width=8) (actual time=2254.854..2254.855 rows=1 loops=1)"
"  ->  Index Only Scan using "РегистрНакопления.ТоварыНаСкладах.Индекс_2[НомерСтроки,Регистратор,ОбластьДанныхОсновныеДанные]" on "РегистрНакопления.ТоварыНаСкладах" t1  (cost=0.56..329749.40 rows=12968602 width=0) (actual time=0.053..1757.336 rows=12968595 loops=1)"
"        Index Cond: ("ОбластьДанныхОсновныеДанные" = '0'::numeric)"
"        Heap Fetches: 0"
"Время планирования: 0.798 ms"
"Время выполнения: 2254.947 ms"

https://explain.tensor.ru/archive/explain/ffa35f0f5f320816c7782e11dfd36b38:0:2024-05-20#visio

Рис. 3. Графическое представление плана запроса

Существуют и альтернативные варианты подсчета количества записей, например через функцию сумма.

ВЫБРАТЬ
	СУММА(1) КАК КолВо
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента

План запроса:

"Aggregate  (cost=22274.14..22274.15 rows=1 width=32) (actual time=190.971..190.972 rows=1 loops=1)"
"  ->  Index Only Scan using "Документ.ЗаказКлиента.Индекс_20[Ссылка,ОбластьДанныхОсновныеДанные]" on "Документ.ЗаказКлиента" t1  (cost=0.42..20193.67 rows=832186 width=0) (actual time=0.037..142.427 rows=832056 loops=1)"
"        Index Cond: ("ОбластьДанныхОсновныеДанные" = '0'::numeric)"
"        Heap Fetches: 2702"
"Время планирования: 1.100 ms"
"Время выполнения: 191.008 ms"

Но как вы видите особой разницы в выполнении подобной операции нет.

Анализ проблемы. Часть II

Теперь выполним тот же запрос под пользователем с ограниченными правами доступа и посмотрим, как это повлияет на производительность. Для этого необходимо добавить в запрос ключевое слово РАЗРЕШЕННЫЕ, иначе мы получим ошибку контроля доступа.

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	количество(*) КАК КолВо
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента

1) Будем выполнять запрос с обычным режимом RLS

Получим время выполнения в 554 с с количеством записей 304 тысячи. Но обратите внимание, записи были прочитаны все. Количество прошедших проверку записей (rows) на плане запроса равно 304 174, а количество отброшенных (не прошедших, RRbF) равно 527 882, что в сумме нам дают исходные  832 056 строк.

https://explain.tensor.ru/archive/explain/5dd38c0ae204523f61465cc1bd97d75c:0:2024-05-20#visio

Рис. 4. Графическое представление плана запроса с ограничением RLS

2) План запроса в производительном режиме (выполнили под другим пользователем, но основной идеи не меняет):

https://explain.tensor.ru/archive/explain/d7aa166524ad6a744e59b5c1ee3797c9:0:2024-05-20#visio

Рис. 4. Графическое представление плана запроса с ограничением RLS (производительный режим)

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

Сделаем небольшое заключение: получение точного количества строк (COUNT) — это тяжелая операция, которая в сочетании с RLS создает экстремальную нагрузку на СУБД.

Каждый такой запрос заставляет сервер:

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

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

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

Варианты решения

Вариант решения 1: Уменьшение подсчитываемых данных за счет добавления фильтров

Основной подход к оптимизации запроса с КОЛИЧЕСТВО(*) — это максимальное сокращение количества записей, которые необходимо проверить системе. Самый эффективный способ добиться этого — добавить в запрос явные условия отбора.

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

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	КОЛИЧЕСТВО(*) КАК КолВо
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента
ГДЕ
	ЗаказКлиента.Менеджер = &ТекущийПользователь
	ИЛИ ЗаказКлиента.Автор = &ТекущийПользователь

Выполняем и получаем время выполнения меньше 2,6 с и примерно 6 400 строк.

https://explain.tensor.ru/archive/explain/6c30b59af7508d4caf045971f105e52a:0:2024-05-20#visio

Рис. 5. План запроса в графическом представлении

Из плана запросов следует, что в случае добавления дополнительного ограничения по Автору и Менеджеру, несмотря на то, что происходит последовательный перебор всей таблицы. Сначала накладывается ограничение по "Автору" или "Менеджеру" на таблицу, а только потом вступает фильтр по RLS.

Если выполнить  запрос только с условием по индексированному полю, то выполняться он будет еще эффективнее — за счет того, что будут использоваться только записи соответствующие условию по автору.

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	КОЛИЧЕСТВО(*) КАК КолВо
ИЗ
	Документ.ЗаказКлиента КАК ЗаказКлиента
ГДЕ
	ЗаказКлиента.Автор = &ТекущийПользователь

https://explain.tensor.ru/archive/explain/87eb5cc0cda309fdcad0c91ace7faa97:0:2024-05-20#visio

Рис. 6. План запроса в графическом представлении

Вариант решения 2: Ограничение количества возвращаемых данных (Пейджинг / Признак наличия)

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

• "Есть ли у меня задачи?" (наличие хотя бы одной записи)
• "Какие ближайшие задачи?" (первые 10-100 записей)

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

Мы видим, что пользователю выводится информация о количестве документов в каждом разделе. Если их количество больше 999, то выводится знак "+" (пока писал статью, я подумал над тем, что если перевернуть изображение, то... впрочем не будем об этом). 

Рис. 7. Форма «Текущие дела ЭДО»

И так, давайте перепишем наш исходный запрос количества согласно предложенному выше алгоритму. Проверим есть ли 10 записей в таблице:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	КОЛИЧЕСТВО(Запрос1.ССылка) КАК КолВо
ИЗ
	(ВЫБРАТЬ ПЕРВЫЕ 10
		ЗаказКлиента.Ссылка КАК ССылка
	ИЗ
		Документ.ЗаказКлиента КАК ЗаказКлиента) КАК Запрос1

Сначала выполним запрос под администратором и посмотрим план запроса. Время выполнения составило меньше 1 мс. Это очень и очень быстро. Обратите внимание на текстовом представлении плана запроса на узел получения данных из индексной таблицы документа заказ клиента, что выбирается только 10 строк. Это именно столько строк, сколько мы запрашивали в ограничении ключевым словом ПЕРВЫЕ.

aggregate  (cost=0.51..0.52 rows=1 width=8) (actual time=0.077..0.078 rows=1 loops=1)
  output: count(t2.Ссылка)
  buffers: shared hit=7
  ->  limit  (cost=0.17..0.40 rows=10 width=20) (actual time=0.068..0.073 rows=10 loops=1)
        output: t2.Ссылка
        buffers: shared hit=7
        ->  index only scan using Документ.ЗаказКлиента_s_hpk on public.Документ.ЗаказКлиента t2  (cost=0.17..18945.14 rows=832186 width=20) (actual time=0.067..0.071 rows=10 loops=1)
              output: t2.Ссылка
              index cond: (t2.ОбластьДанныхОсновныеДанные = '0'::numeric)
              heap fetches: 0
              buffers: shared hit=7
planning:
  buffers: shared hit=519 read=2
Время планирования: 1.377 ms
Время выполнения: 0.103 ms

Теперь выполним запрос в обычном режиме получения данных RLS. Время выполнения составило 0.1 с и мы получили 10 строк. Если мы посмотрим на графический план запроса, то увидим, что обработано всего 25 строк из таблицы заказ клиента, причем 15 из них не подошли по условию ограничения RLS. Иными словами система сейчас наложила ограничение только на 25 строк, вместо более чем 850 тысяч, отсюда и такой эффект ускорения.

https://explain.tensor.ru/archive/explain/60a664655712dad2ee637ebb9dbd999f:0:2024-05-21#visio

Рис. 8. Графическое представление плана запросов

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

Рис. 9. Пример работы рассматриваемого метода

Вариант решения 3: Предварительная агрегация (Pre-aggregation)

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

Архитектура решения:

1. Хранилище данных: Создается непериодический регистр сведений "ЗаказыКлиентовВРаботе".
   • Измерения: Пользователь (СправочникСсылка.Пользователи)
   • Ресурсы: Количество (Число)
2. Механизм обновления: Создается обработка, которая регламентно (например, по расписанию) пересчитывает и обновляет данные в регистре.

Вариант кода для заполнения этого регистра сведений приведен ниже:

ВЫБРАТЬ
	СУММА(Запрос1.Количество) КАК Количество,
	Запрос1.Менеджер КАК Менеджер
ИЗ
	(ВЫБРАТЬ
		КОЛИЧЕСТВО(ЗаказКлиента.Ссылка) КАК Количество,
		ЗаказКлиента.Менеджер КАК Менеджер
	ИЗ
		Документ.ЗаказКлиента КАК ЗаказКлиента
	ГДЕ
		ЗаказКлиента.Проведен
		И ЗаказКлиента.Статус В (ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.КОбеспечению), ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.КОтгрузке))
	
	СГРУППИРОВАТЬ ПО
		ЗаказКлиента.Менеджер
	
	ОБЪЕДИНИТЬ ВСЕ
	
	ВЫБРАТЬ
		0,
		Пользователи.Ссылка
	ИЗ
		Справочник.Пользователи КАК Пользователи
	ГДЕ
		Пользователи.ПометкаУдаления = ЛОЖЬ
		И Пользователи.Недействителен = ЛОЖЬ) КАК Запрос1

СГРУППИРОВАТЬ ПО
	Запрос1.Менеджер

В результате для выбора данных из этого регистра нужно будет выполнить простой и быстрый запрос:

ВЫБРАТЬ
	РСЗК.Количество КАК Количество
ИЗ
	РегистрСведений.ЗаказыКлиентовВРаботе КАК РСЗК
ГДЕ 
	РСЗК.Менеджер=&ТекущийПользователь

Заключение

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

1. Природа операции COUNT в СУБД: Для получения точного количества записей СУБД выполняет последовательное сканирование и суммирование строк. Прямая зависимость между объемом данных и временем выполнения делает эту операцию ресурсоемкой на больших таблицах.
2. Критическое влияние RLS: Механизм ограничения доступа на уровне строк (RLS) радикально увеличивает нагрузку. Так как проверка прав применяется к каждой записи, время выполнения запроса растет пропорционально общему количеству записей в таблице, а не только количеству разрешенных.
3. Принцип минимизации данных: Чем меньше записей необходимо обработать для получения ответа, тем выше производительность. Любые условия отбора (WHERE), особенно по индексированным полям, существенно ускоряют выполнение.
4. Приоритетный подход — отказ от точного подсчета: Самой эффективной стратегией является отказ от точного подсчета всех записей в пользу ответа на упрощенные вопросы:
   • «Есть ли данные?» ( ВЫБРАТЬ ПЕРВЫЕ 1 ... )
   • «В таблице до N записей или больше?» ( ВЫБРАТЬ ПЕРВЫЕ 100 ... )
5. Использование ключевого слова ПЕРВЫЕ позволяет СУБД немедленно прекратить сканирование после нахождения требуемого числа записей, что делает этот подход одним из самых распространенных и рекомендуемых в современных высоконагруженных системах.

 

 видео youtube

https://youtu.be/lxydLJynvHg