Быстро подсчитать количество строк по всей таблице / PostgreSQL

2.0.53

Полная версия Контакт Правила FAQ Помощь

Гость

Нов. | Гор. | Избр.

Действия ...

Доб. в избранное
Игнор. тему
Прикреп. тему
Пометить прочит. / непрочит.
Фильтр:
Сообщения автора темы
Сообщение содержит вложения
Сообщение содержит картинки
Сообщение содержит видеоклипы
Сообщение содержит аудиоклипы
Сообщение содержит картинки или видео 18+

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Быстро подсчитать количество строк по всей таблице / 21 сообщений из 21, страница 1 из 1

29.05.2020, 01:17

#39963793

Шамиль Фаридович

Участник

Откуда: Саратов
Сообщения: 512
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Доброго времени суток!
Postgres 12
Как можно оптимизировать запросы вида

Код: plsql

select count(*) from tb1

?

Заглядываю в план, а там последовательное сканирование всей таблицы, которое работает достаточно медленно, не смотря на то, что в таблице есть индексы.

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

Собственно, задача и заключается в том, что бы сравнить количество строк в разных СУБД.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

29.05.2020, 02:19

#39963803

Maxim Boguk

Участник

Откуда: Melbourne, Австралия
Сообщения: 5 148
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

Доброго времени суток!
Postgres 12
Как можно оптимизировать запросы вида

Код: plsql

select count(*) from tb1

Никак (если именно надо точное значение а не оценку от базы).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

29.05.2020, 11:06

#39963937

Павел Лузанов

Участник

Сообщения: 866
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович,

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

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

01.07.2020, 17:08

#39974975

Шамиль Фаридович

Участник

Откуда: Саратов
Сообщения: 512
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Павел Лузанов,

а нельзя ли заставить Постгрес использовать сканирование индекса вместо сканирования таблицы с помощью табличной подсказки?

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

01.07.2020, 17:30

#39974985

Maxim Boguk

Участник

Откуда: Melbourne, Австралия
Сообщения: 5 148
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

1)если очень надо то можно
2)будет скорее всего медленнее чем seq scan потому что индекс будет в random read читаться а не последовательно
(и очень сильно медленнее если таблица не в памяти... и совсем грустно если таблица на механических дисках)

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.

set jit to 0;
set max_parallel_workers_per_gather to 0;

select count(*) from sometable;
  count   
----------
 35905118
(1 row)
Time: 5329.749 ms (00:05.330)

explain analyze select count(*) from sometable;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=482391.19..482391.20 rows=1 width=8) (actual time=7273.647..7273.648 rows=1 loops=1)
   ->  Seq Scan on sometable  (cost=0.00..464441.24 rows=35899904 width=0) (actual time=0.009..5328.837 rows=35905119 loops=1)
 Planning Time: 0.130 ms
  Execution Time: 7273.694 ms 


 set enable_seqscan to 0; 

explain analyze select count(*) from sometable;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=570771.67..570771.68 rows=1 width=8) (actual time=7233.958..7233.958 rows=1 loops=1)
   ->  Index Only Scan using sometable_pk on sometable  (cost=0.11..552821.72 rows=35899904 width=0) (actual time=0.030..5289.288 rows=35905126 loops=1)
         Heap Fetches: 4775
 Planning Time: 0.125 ms
  Execution Time: 7734.004 ms 


 --как на самом деле такие вещи ускоряют если зачем то надо (если оно в памяти иначе скорее всего в диски упрется)
set enable_seqscan to 1;
set max_parallel_workers_per_gather to 16;
set jit to 1;
 

explain analyze select count(*) from sometable;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=159392.14..159392.15 rows=1 width=8) (actual time=1025.826..1025.826 rows=1 loops=1)
   ->  Gather  (cost=159392.06..159392.14 rows=7 width=8) (actual time=1025.652..1035.535 rows=8 loops=1)
         Workers Planned: 7
         Workers Launched: 7
         ->  Partial Aggregate  (cost=159292.06..159292.07 rows=1 width=8) (actual time=996.846..996.846 rows=1 loops=8)
               ->  Parallel Seq Scan on sometable  (cost=0.00..156727.78 rows=5128558 width=0) (actual time=3.694..740.061 rows=4488142 loops=8)
 Planning Time: 0.143 ms
 JIT:
   Functions: 27
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 3.733 ms, Inlining 0.000 ms, Optimization 1.456 ms, Emission 27.305 ms, Total 32.494 ms
  Execution Time: 1036.444 ms

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

02.07.2020, 15:21

#39975489

О-О-О

Гость

Быстро подсчитать количество строк по всей таблице

Maxim Boguk,

Есть два варианта - первый это обычный count(*). Он хорош, если есть индекс для этой таблицы.
Второй вариант это SELECT count(*) *5 from ВАША_ТАБЛИЦА TABLESAMPLE SYSTEM(20)
Он хорош, если нет индекса, но результаты не точные.
Ниже сравнение по скорости обоих методов на SSD диске по таблице, у которой есть индекс (btree).

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

	п_время_старт :=clock_timestamp();
	п_среднее :=(SELECT count(*) *5 from ВАША_ТАБЛИЦА TABLESAMPLE SYSTEM(20));
	п_время_конец :=clock_timestamp();
	RAISE NOTICE E'\r          \n\r\t Быстрый метод: выдал % записей за % сек (при наличии индекса btree НЕ ИСПОЛЬЗОВАТЬ!!!)', п_среднее, (п_время_конец-п_время_старт);
			-- Быстрый метод: выдал 578885 записей за 00:00:00.009071 сек
	
	
	п_время_старт :=clock_timestamp();
	п_среднее :=(SELECT count(*) from ВАША_ТАБЛИЦА);
	п_время_конец :=clock_timestamp();
	RAISE NOTICE E'\r          \n\r\t Правильный метод: выдал % записей за % сек\n', п_среднее, (п_время_конец-п_время_старт);
		-- Правильный метод: выдал 590015 записей за 00:00:00.036199 сек

.
Как можно заметить - отличие по времени всего в 4 раза, но точность страдает.
Если индекса нет, то отличия по времени возрастают.
Более подробно о TABLESAMPLE SYSTEM смотрите в инструкции.
.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

02.07.2020, 16:06

#39975523

Melkij

Участник

Откуда: Санкт-Петербург
Сообщения: 1 447
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

О-О-О

первый это обычный count(*). Он хорош, если есть индекс для этой таблицы

Да не будет он индекс дёргать. Никакого смысла в этом нет потому что в индексе всё равно нет данных о транзакционной видимости строк.

А если точное значение вам не надо - то спросите у планировщика explain'ом или из системного каталого оценочное число строк достаньте.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

03.07.2020, 01:16

#39975738

Шамиль Фаридович

Участник

Откуда: Саратов
Сообщения: 512
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Maxim Boguk,
спасибо за подробный ответ!

Еще вопрос: если один и тот же

Код: plsql

explain analyze select count(*) from sometable

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

Всем: нужно именно точное значение, а не оценка. Индексы есть, в том числе некластеризованный PK по автоинкрементному ключу.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

03.07.2020, 08:54

#39975786

Maxim Boguk

Участник

Откуда: Melbourne, Австралия
Сообщения: 5 148
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

Maxim Boguk,
спасибо за подробный ответ!

Еще вопрос: если один и тот же

Код: plsql

explain analyze select count(*) from sometable

база кеширует данные в 2х уровнях
1)кеш файловой системы (который можно сбросить конечно если очень надо...)
2)кеш на уровне shared buffers (который сбросить можно только рестартом базы)

но идея "для чистоты экспериментов" - она странная... нормальное состояние базы - когда все или почти все данные лежат в памяти а не на дисках, диски какие бы они супебыстрые не были - всегда будут медленные (порядок скорости intel optane > nvme > sata ssd >> сетевой ssd storgage >>> механические диски)
т.е. если исходить из худшего случая когда все данные холодные и лежат на дисках - все и всегда буде тормозить (после рестарта сервера база всегда будет тормозить от минуты до нескольких часов - пока все данные в память не залезут).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

03.07.2020, 09:40

#39975800

Maxim Boguk

Участник

Откуда: Melbourne, Австралия
Сообщения: 5 148
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

Всем: нужно именно точное значение, а не оценка. Индексы есть, в том числе некластеризованный PK по автоинкрементному ключу.

Уж 20+ лет в индустрии и еще НИ РАЗУ не видел задачи где надо было точное значение иметь для таблиц размером больше миллиона строк в сочетании с требованием быстрого подсчета (если число раз в сутки считается - скорость count(*) никого особо не волнует).
Расскажите зачем вы вообще это число считаете и почему вам важно иметь его а)точным б)быстро - ответ "бизнес требует" он не рабочий это ВАША задача объяснить бизнесу что чудес не бывает и предложить альтернативные решения а не базу насиловать ;).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

04.07.2020, 17:38

#39976228

Shweik

Участник

Сообщения: 1 694
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Maxim Boguk

Шамиль Фаридович

Полностью поддерживаю. Ни разу администрируя PostgreSQL с 1998 года я не сталкивался с обоснованной потребностью получения
точного количества строк таблиц заведомо не помещающихся в памяти.
Зато это был великолепный сигнал о уровне разработчика.
Но если всё же мне надо и достаточно часто оценивать количество строк - я обращаюсь например к статистике:

Код: sql

1.
2.
3.
4.
5.
6.
7.

SELECT 
st.relname,n_live_tup,n_dead_tup, 
 pg_size_pretty(pg_relation_size(st.relid) + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) 
FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=st.relid)::int8, 0)) AS size
          FROM pg_stat_all_tables st  JOIN pg_class cl on cl.oid=st.relid
         WHERE schemaname = 'public'
         ORDER BY n_live_tup desc

relnamen_live_tupn_dead_tupsize"_accrged1153"33615605356972"11 GB""_inforg16897"3185364976"36 GB""_inforgchngr16904"3093369476"8924 MB""_accumrg21954"11046405721718"8370 MB""_accrg1120"789907586621"5815 MB"
И самое смешное что "бизнес" вполне удовлетворён этими "точными" хе-хе-хе значениями.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

09.07.2020, 23:20

#39977933

Шамиль Фаридович

Участник

Откуда: Саратов
Сообщения: 512
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Maxim Boguk,

Maxim Boguk

Расскажите зачем вы вообще это число считаете и почему вам важно иметь его точным

С уважением отношусь к вашему опыту и знаниям. Вот вам пример для его увеличения.
Есть приложение, которое реплицирует данные из МС в ПГ, раз в час. За ним следят. Пока только сравнивают количество строк (и это действительно приносило свои плоды - извините, без подробностей)
Даже на холодном буфере select count(*) по таблице из 90М записей в МС отрабатывает за 12с против 16с в ПГ с "горячим" буфером (ибо я банально не знал, как его сбросить).

Далее время выполнения в МС сокращается до 2с (при том, что данные в БД меняются). Я не знаю каким образом этого добились разработчики МС (возможно из-за сканирования индекса вместо всей таблицы, у них вообще довольно шустро работают такие сканы), но это так.Я пробовал играться с количеством воркеров, но быстрее всего работал вариант без указания их количества(то есть 2 по умолчанию).
И это лишь одна таблица. Таким образом время обсчета кардинально отличается в 2х СУБД.

Maxim Boguk

--как на самом деле такие вещи ускоряют если зачем то надо (если оно в памяти иначе скорее всего в диски упрется)

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

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

09.07.2020, 23:28

#39977936

Maxim Boguk

Участник

Откуда: Melbourne, Австралия
Сообщения: 5 148
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

Maxim Boguk,

Maxim Boguk

Расскажите зачем вы вообще это число считаете и почему вам важно иметь его точным

Если за таким решением надо вообще следить в production (и тем более таким странным методом как сверка количества строк) - у вас уже все критически плохо... и тут не базу лечить надо а просто сделать нормальное решение которое сверок не требует... тем более не требует сверок чаще чем раз в сутки например (а если раз в сутки - то производительность более менее пофигу).

Maxim Boguk

Что вы имеет в виду под таблицей в "памяти" - таблица должна быть в кэше, или ее нужно каким-то специальным образом определить?[/quot]
таблица должна быть в кеше.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.07.2020, 00:04

#39977945

mefman

Участник

Сообщения: 1 562
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Maxim Boguk

Да уж. Трешачок. Опять доморощенные кулибины навертели репликацию из велосипедов, костылей и синей изоленты.
Думаю походу выяснится, что МС у него на SSD, например )) А ПЖ еще и на виндузе крутится.

Шамиль Фаридович

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

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

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.07.2020, 07:43

#39977978

О-О-О

Гость

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

Maxim Boguk,
Всем: нужно именно точное значение, а не оценка. Индексы есть, в том числе некластеризованный PK по автоинкрементному ключу.

Ну я бы тогда просто смотрел MAX(автоинкрементному ключу).
При этом имел бы таблицу в которой было бы указано последнее значения (МАХ) автоинкрементного ключа и во втором столбце указал бы сколько дырок/пропусков было до этого ключа = это таблица Х.

Затем от последнего значения в этой таблице до последнего ключа по БД сканировал бы на наличие дырок=У
Затем от МАХ(автоинкрементного ключа) таблицы У отнимал бы кол-во дырок из таблицы Х и количество дырок, которое образовалось за последнее время )с момента последнего анализа).

В итоге - б'ольшая часть таблицы уже проанализирована, а оставшаяся часть анализируется очень быстро - так как есть индекс.
В результате анализ идет грубо только по 0,01% от всего размера таблицы (смысл каждый раз пересчитывать такую гигантскую таблицу, при том, что таблица Х будет занимать всего 1-2 кб, что в вашем случае - вообще ни о чем!).

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.07.2020, 08:50

#39977990

Maxim Boguk

Участник

Откуда: Melbourne, Австралия
Сообщения: 5 148
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

О-О-О

Шамиль Фаридович

Не могу не процитировать "Опять доморощенные кулибины".
Для начала вопрос а как это будет себя вести если есть удаления из таблицы (и тем более если их много и они часто происходят)?

Я еще раз напишу - если возник вопрос быстрого точного частого count(*) по большой таблице - вы что то сильно не так делаете на уровне постановки или решения задачи.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.07.2020, 10:07

#39978010

mefman

Участник

Сообщения: 1 562
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Я вот еще что думаю.
Что это за черезодноместо репликация?
Не может ли иметь место простой факт непроанализированной/невакуумленой таблицы??

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.07.2020, 11:10

#39978028

Шамиль Фаридович

Участник

Откуда: Саратов
Сообщения: 512
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

mefman

Думаю походу выяснится, что МС у него на SSD, например )) А ПЖ еще и на виндузе крутится.

Нет, машины +/- одинаковые. А вот про windows вы правы.
Ткните плиз, где написано, как это сказывается на производительности.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

10.07.2020, 11:31

#39978042

mefman

Участник

Сообщения: 1 562
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

mefman

Думаю походу выяснится, что МС у него на SSD, например )) А ПЖ еще и на виндузе крутится.

Версия PostgreSQL для виндовс написана на от*бись чтоб было.
При написании кода в первую очередь сообщество учитывает тонкости ОС Linux/Unix, их работу с памятью, дисками итд.
А МСскль как раз оптимизирован под венду(было бы странно, если бы не был)
Виндовс версия существует чтоб разработчик который в глаза не видел линуксов мог поставить ПЖ к себе на ноут и там погонять запросики.
Использование связки ПЖ-венда в пром среде строго не рекомендуется.
Ну у таки повторю вопрос:
как работает репликация?
Полный delete + insert, update, как? План запроса с analyze,buffers,timing в студию.
Если в ходе вашей репликации таблица обновляется целиком или почти целиком тормоза запроса - будут вполне объяснимы.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

11.07.2020, 01:16

#39978335

Шамиль Фаридович

Участник

Откуда: Саратов
Сообщения: 512
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

mefman

А есть какая-нибудь статья на эту тему, с сравнительными табличками, диаграммами, чтобы я мог показать ее заказчику?
Если на английском, то в благодарность отправлю чешское - пиво:)

mefman

как работает репликация?

Именно в этой таблице удаление строк не предусмотрено, поэтому идет только вставка и обновление по полю ModifiedDate в источнике.
В среднем за час таким образом добавляется не более 40к строк, то есть меньше 0.05% от размера таблицы.
План запроса вышлю чуть позже, но там ничего особенного - скан в 2 потока.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

11.07.2020, 14:10

#39978414

Dimitry Sibiryakov

Участник

Сообщения: 54 521
Рейтинг: 0 / 0

Быстро подсчитать количество строк по всей таблице

Шамиль Фаридович

Именно в этой таблице удаление строк не предусмотрено, поэтому идет только вставка и обновление по полю ModifiedDate в источнике.

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

Вот проверили вы число записей, а через пару лет обнаружится, что репликация к некоторым числовым данным прибавляет единицу. А некоторые блобы устанавливает в NULL. И что вы с этим будете делать?.. А ведь это были вполне реальные баги вполне конкретного репликатора.

...

Рейтинг:

0 / 0

| Ответить | Цитировать | Написать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&tablet=1&tid=1994601]:	0ms
get settings:	11ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	40ms
get topic data:	13ms
get forum data:	3ms
get page messages:	56ms
get tp. blocked users:	2ms
others:	275ms

total:	422ms