ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Долгий count(*)
21 сообщений из 21, страница 1 из 1
Долгий count(*)
    #39632887
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
Доброго дня, коллеги.

PostgreSQL 10.3

Вопрос достаточно детский, но ничего путного не нашёл. Суть: долго выполняется запрос count(*):

Код: sql
1.
2.
3.
4.
5.
6.
explain analyze select count(*)
from LOG_EXT_SYS l__r
inner join LOG_PROTO p__r
  on l__r.LOG_PROTO_ID = p__r.LOG_PROTO_ID
 where 
  l__r.LOG_PROTO_ID = 3


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Aggregate  (cost=1162474.39..1162474.40 rows=1 width=8) (actual time=12433.244..12433.244 rows=1 loops=1)
  ->  Nested Loop  (cost=0.56..1091539.33 rows=28374023 width=0) (actual time=0.224..9021.681 rows=28626778 loops=1)
        ->  Seq Scan on log_proto p__r  (cost=0.00..1.14 rows=1 width=8) (actual time=0.029..0.037 rows=1 loops=1)
              Filter: ((log_proto_id)::bigint = 3)
              Rows Removed by Filter: 11
        ->  Index Only Scan using fk_log_proto_log_ext_sys_fk on log_ext_sys l__r  (cost=0.56..807797.97 rows=28374023 width=8) (actual time=0.192..5428.806 rows=28626778 loops=1)
              Index Cond: (log_proto_id = 3)
              Heap Fetches: 7468
Planning time: 2.725 ms
Execution time: 12433.469 ms


Поисковые столбцы индексированы, всё время тратиться на "Aggregate"

Смотрел в сторону Loose indexscan, но по-моему это не то...
...
Рейтинг: 0 / 0
19.04.2018, 00:56
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39632913
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
sKot,

первый вопрос: почему вы джойните две таблицы? если там у вас инер джойн да и селект по где ид ровен Х.
...
Рейтинг: 0 / 0
19.04.2018, 07:00
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39632930
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
sKotДоброго дня, коллеги.

PostgreSQL 10.3

Вопрос достаточно детский, но ничего путного не нашёл. Суть: долго выполняется запрос count(*):

Код: sql
1.
2.
3.
4.
5.
6.
explain analyze select count(*)
from LOG_EXT_SYS l__r
inner join LOG_PROTO p__r
  on l__r.LOG_PROTO_ID = p__r.LOG_PROTO_ID
 where 
  l__r.LOG_PROTO_ID = 3


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Aggregate  (cost=1162474.39..1162474.40 rows=1 width=8) (actual time=12433.244..12433.244 rows=1 loops=1)
  ->  Nested Loop  (cost=0.56..1091539.33 rows=28374023 width=0) (actual time=0.224..9021.681 rows=28626778 loops=1)
        ->  Seq Scan on log_proto p__r  (cost=0.00..1.14 rows=1 width=8) (actual time=0.029..0.037 rows=1 loops=1)
              Filter: ((log_proto_id)::bigint = 3)
              Rows Removed by Filter: 11
        ->  Index Only Scan using fk_log_proto_log_ext_sys_fk on log_ext_sys l__r  (cost=0.56..807797.97 rows=28374023 width=8) (actual time=0.192..5428.806 rows=28626778 loops=1)
              Index Cond: (log_proto_id = 3)
              Heap Fetches: 7468
Planning time: 2.725 ms
Execution time: 12433.469 ms


Поисковые столбцы индексированы, всё время тратиться на "Aggregate"

Смотрел в сторону Loose indexscan, но по-моему это не то...
вы неправильно читаете план. (я впрочем тоже его неверно читаю)
время тратится на чтение 28 лямов 5.5сек и свертку нестед лупом еще с 1-й -- с 0-й по 9.-ю сек.
но ещё 3 сек на до-подбор агрегатом --дороговато

в выдаче у вас число "28626778" ? никакой луз не поможет. можете отвакуумиться , или переиндексироваться --7468 раз оно лазало в кучу из иоса. но это поможет временно и не сильно.

а покажите хохмы ради план примерно такой

Код: sql
1.
2.
3.
4.
5.
6.
7.
explain analyze select count(0)
from 
LOG_EXT_SYS l__r
where l__r.LOG_PROTO_ID = 3
/*
AND exists (select 0 from LOG_PROTO p__r where p__r.LOG_PROTO_ID =3)
*/


[src sql]
...
Рейтинг: 0 / 0
19.04.2018, 07:54
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633103
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
Lonepsycho, спасибо, с этим косяком я буду разбираться, для count(*) join не нужен.
Запрос создаётся динамически движком отображения журналов. Точнее создаётся два одинаковых (по джойнам и фильтрам) запроса - один тащит данные (и в нём как раз без джойна не обойтисть), а второй считает общее кол-во строк, попавших в результирующее множество.
Попробовал без джойна.... Даже не знаю что сказать:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
explain analyze select count(*)
from LOG_EXT_SYS l__r
 where 
  l__r.LOG_PROTO_ID = 3
  
Finalize Aggregate  (cost=672839.32..672839.33 rows=1 width=8) (actual time=13932.996..13932.997 rows=1 loops=1)
  ->  Gather  (cost=672839.11..672839.32 rows=2 width=8) (actual time=13932.913..13932.969 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=671839.11..671839.12 rows=1 width=8) (actual time=13930.165..13930.165 rows=1 loops=3)
              ->  Parallel Index Only Scan using fk_log_proto_log_ext_sys_fk on log_ext_sys l__r  (cost=0.56..642282.83 rows=11822510 width=0) (actual time=0.079..7629.352 rows=9542264 loops=3)
                    Index Cond: (log_proto_id = 3)
                    Heap Fetches: 3258
Planning time: 1.487 ms
Execution time: 13945.888 ms
...
Рейтинг: 0 / 0
19.04.2018, 12:50
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633109
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
qwwq, Спасибо :)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
explain analyze select count(0)
from 
LOG_EXT_SYS l__r
where l__r.LOG_PROTO_ID = 3

Finalize Aggregate  (cost=672839.32..672839.33 rows=1 width=8) (actual time=14143.670..14143.670 rows=1 loops=1)
  ->  Gather  (cost=672839.11..672839.32 rows=2 width=8) (actual time=14143.624..14143.656 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=671839.11..671839.12 rows=1 width=8) (actual time=14140.590..14140.591 rows=1 loops=3)
              ->  Parallel Index Only Scan using fk_log_proto_log_ext_sys_fk on log_ext_sys l__r  (cost=0.56..642282.83 rows=11822510 width=0) (actual time=0.083..7611.310 rows=9542264 loops=3)
                    Index Cond: (log_proto_id = 3)
                    Heap Fetches: 3485
Planning time: 1.338 ms
Execution time: 14159.431 ms


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
explain analyze select count(0)
from 
LOG_EXT_SYS l__r
where l__r.LOG_PROTO_ID = 3
AND exists (select 0 from LOG_PROTO p__r where p__r.LOG_PROTO_ID =3)

Aggregate  (cost=878734.16..878734.17 rows=1 width=8) (actual time=69163.492..69163.493 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Seq Scan on log_proto p__r  (cost=0.00..1.14 rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=1)
          Filter: ((log_proto_id)::bigint = 3)
          Rows Removed by Filter: 2
  ->  Result  (cost=0.56..807797.97 rows=28374023 width=0) (actual time=0.176..50665.704 rows=28626792 loops=1)
        One-Time Filter: $0
        ->  Index Only Scan using fk_log_proto_log_ext_sys_fk on log_ext_sys l__r  (cost=0.56..807797.97 rows=28374023 width=0) (actual time=0.147..19830.789 rows=28626792 loops=1)
              Index Cond: (log_proto_id = 3)
              Heap Fetches: 8146
Planning time: 1.557 ms
Execution time: 69163.653 ms
...
Рейтинг: 0 / 0
19.04.2018, 12:55
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633117
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
qwwq, да, в выдаче 28626778.
Это журнал лога, в нём ~40 млн записей, из них 28626778 - протокол SOAP. Данный запрос взят для примера, если взять другой протокол, то картина примерно такая же.
l__r.LOG_PROTO_ID = 3 показал самый долгий результат (т.к. этих записей больше всего), поэтому я и привёл этот запрос в качестве примера.
...
Рейтинг: 0 / 0
19.04.2018, 12:59
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633122
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
sKot,

"паралел" вам отстрелить надо.
или костами загнобить.
...
Рейтинг: 0 / 0
19.04.2018, 13:02
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633134
bdsm_sql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bdsm_sql
Участник
если возвращается столько строк, то нафига индекс по LOG_PROTO_ID вообще нужен.. исподльзуйте партицирование по этому полю
...
Рейтинг: 0 / 0
19.04.2018, 13:14
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633144
bdsm_sql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bdsm_sql
Участник
+не знаю, насколько вам актуально из-за того, что запрос генерируете не вы, но
1. если скорость чтения кол-ва логов важнее скорости вставки логов, то вешайте триггер, который апдейтит таблицу с количеством на каждый LOG_PROTO_ID
2. если информация не нужна актуальная прям на данный момент времени, создайте матвью с группировкой и количеством на каждый LOG_PROTO_ID и апдейтите её раз в час, например
3. если точное кол-во не важно (2237853423 или 2237853689), то вместо count используйте данные статистики из плана по запросу where LOG_PROTO_ID = ?
...
Рейтинг: 0 / 0
19.04.2018, 13:23
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633218
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
bdsm_sql, партиционирование не очень поможет, т.к. работа с таблицей ведётся через механизм журналов, который на партиционирование (а в случае с PostgreSQL - работы с несколькими таблицами вместо одной) не заточен :(
...
Рейтинг: 0 / 0
19.04.2018, 14:45
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633221
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
bdsm_sql,
автор3. если точное кол-во не важно (2237853423 или 2237853689), то вместо count используйте данные статистики из плана по запросу where LOG_PROTO_ID = ?
Идея хороша, буду её думать :)
Спасибо!
...
Рейтинг: 0 / 0
19.04.2018, 14:47
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633228
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
qwwq,
автор"паралел" вам отстрелить надо.
или костами загнобить.
Я дико прошу прощения, но не понял ни первый, ни второй совет:
1. Я так понимаю, надо каким-то образом включить многопоточное выполнение запроса "count(*)..."? Если да, то имеется в виду установку параметра "max_parallel_workers_per_gather" в файле pgsql.conf?
2. Как загнобить костами? Тут я даже гуглить затрудняюсь :)
...
Рейтинг: 0 / 0
19.04.2018, 14:57
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633244
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
sKotqwwq,
автор"паралел" вам отстрелить надо.
или костами загнобить.
Я дико прошу прощения, но не понял ни первый, ни второй совет:
1. Я так понимаю, надо каким-то образом включить многопоточное выполнение запроса "count(*)..."? Если да, то имеется в виду установку параметра "max_parallel_workers_per_gather" в файле pgsql.conf?
2. Как загнобить костами? Тут я даже гуглить затрудняюсь :)
всё вы поняли,
прямо в сессии где замеряете ставите
Код: sql
1.
set max_parallel_workers_per_gather=0;


а зачем гуглить. берете rtfm =>
https://postgrespro.ru/docs/enterprise/10/when-can-parallel-query-be-used

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

или вот тут посмотрите https://habrahabr.ru/post/305662/ парамсы пока не устоялись, но примерный вектор.
...
Рейтинг: 0 / 0
19.04.2018, 15:13
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633250
bdsm_sql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bdsm_sql
Участник
sKotbdsm_sql, партиционирование не очень поможет, т.к. работа с таблицей ведётся через механизм журналов, который на партиционирование (а в случае с PostgreSQL - работы с несколькими таблицами вместо одной) не заточен :(
механизм журналов - это что? некая хрень, которая шлёт запросы? если да, то в 10ке партицирование из коробки, тупое, но под эту задачу подойдёт. работу будет как с одной таблицей, т.е. для приложения без изменений.
если же механизм журналов это какая-то репликация, то там могут быть тонкости
...
Рейтинг: 0 / 0
19.04.2018, 15:20
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633252
bdsm_sql
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
bdsm_sql
Участник
sKotbdsm_sql,
автор3. если точное кол-во не важно (2237853423 или 2237853689), то вместо count используйте данные статистики из плана по запросу where LOG_PROTO_ID = ?
Идея хороша, буду её думать :)
Спасибо!
https://wiki.postgresql.org/wiki/Count_estimate
...
Рейтинг: 0 / 0
19.04.2018, 15:21
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633333
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
qwwq,
c параллелизмом я, собственно, этот мануал по ПостгресПро и нагуглил, а вот с костами, которыми надо гнобить я не знаю куда лезть :) Уж слишком формулировка бандитская...
...
Рейтинг: 0 / 0
19.04.2018, 17:13
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633337
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
bdsm_sql, спасибо, посмотрю партиционирование в 10. Пока я видел только костыли на триггерах и нескольких таблицах.
...
Рейтинг: 0 / 0
19.04.2018, 17:15
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633364
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
sKotqwwq,
c параллелизмом я, собственно, этот мануал по ПостгресПро и нагуглил, а вот с костами, которыми надо гнобить я не знаю куда лезть :) Уж слишком формулировка бандитская...
а голова вам на что ? вы в её кюшаешь ? в многострадальную.
бывает.


по ссылке
авторя общего числа этих процессов значением max_worker_processes. (я немного сдвинул фокус)
попали на набор парамсов для

и на страничке планировщика ищем константы для рулежки
https://postgrespro.ru/docs/enterprise/10/runtime-config-query
авторparallel_setup_cost (floating point)

Задаёт приблизительную стоимость запуска параллельных рабочих процессов. Значение по умолчанию — 1000.
parallel_tuple_cost (floating point)

Задаёт приблизительную стоимость передачи одного кортежа от параллельного рабочего процесса другому процессу. Значение по умолчанию — 0.1.
min_parallel_table_scan_size (integer)

Задаёт минимальный объём данных таблицы, подлежащий сканированию, при котором может применяться параллельное сканирование. Для параллельного последовательного сканирования объём сканируемых данных всегда равняется размеру таблицы, но когда используются индексы, этот объём обычно меньше. Значение по умолчанию — 8 мегабайт (8MB).
min_parallel_index_scan_size (integer)

Задаёт минимальный объём данных индекса, подлежащий сканированию, при котором может применяться параллельное сканирование. Заметьте, что при параллельном сканировании по индексу обычно не затрагивается весь индекс; здесь учитывается число страниц, которое по мнению планировщика будет затронуто при сканировании. Значение по умолчанию — 512 килобайт (512kB).
дальше жевать , или направление понятно ?
но я только про то, что параллельно оно у вас медленнее чем в лоб. с чего бы это. подрихтовать бы.
...
Рейтинг: 0 / 0
19.04.2018, 17:59
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633387
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
qwwq,
спасибо за то что разжевали. Направление понятно, но вот про рихтовку, чтобы в два потока было быстрее чем в один: это тоже константами планировщика делается?
...
Рейтинг: 0 / 0
19.04.2018, 19:25
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633395
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
sKotqwwq,
спасибо за то что разжевали. Направление понятно, но вот про рихтовку, чтобы в два потока было быстрее чем в один: это тоже константами планировщика делается?думаю только железом.

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

оттюнить можно только позывы к переходу в параллельный режим. все остальное -- от алгоритмов и железа.
...
Рейтинг: 0 / 0
19.04.2018, 20:02
| Ответить | Цитировать | Написать  
Долгий count(*)
    #39633468
sKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sKot
Гость
qwwq, bdsm_sql, огромное вам спасибо!
Вы мне реально помогли!

Окончательное решение такое:

Для больших таблиц (>40 млн) и связанных с ними таблиц, я сделал получение кол-ва записей через план запроса ( https://wiki.postgresql.org/wiki/Count_estimate), bdsm_sql, спасибо тебе!
Если кол-во строк полученных из плана <20000, то выполняется обычный count(*).

Нахардкодить в данном случае не получится, т.к. таблицы и джойны к ним, а также "where..." динамические.

В результате журнал логов стал открываться не 12+ секунд, а 0.02-0.06 сек.

Вопрос можно считать закрытым.

P.S. Интересное наблюдение: про получение кол-ва записей из плана запроса я читал и ранее, больше года назад. Но пока кто-то не ткнёт мордой: "вот оно - решение", даже не задумаешься о применении этого способа. И это не единичный случай это система....
...
Рейтинг: 0 / 0
20.04.2018, 03:23
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Долгий count(*)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39633221&tid=1995819]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 30ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 15ms
total:  144ms
созд. / загр.: 144ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]