Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация GroupAggregate, фильтр, много записей. / 9 сообщений из 9, страница 1 из 1
05.03.2015, 12:30
    #38895790
sherzod_
sherzod_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
Доброго времени!

Коллеги, помогите оптимизировать простейший запрос. Есть таблица с одной колонкой, на ней висит индекс. Такой запрос:

Код: plsql
1.
select count(*) from field group by field


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

Код: plsql
1.
select count(*) from field where field = 'most_frequent_value' group by field


которое в результате выдает бОльшинство записей, при подсчете count индекс (наверное логично) перестает использоваться, и подсчет в общем длится долго. Строк от 500тыс до 10 млн, work_mem выставлял 4GB. Подскажите как можно добиться ускорения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 12:46
    #38895819
sherzod_
sherzod_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
Поправка, во втором запросе индекс конечно тоже используется. Имел в виду что информация о количествах значений в индексе (в связи у утратой актуальности из-за фильтра) не используется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 13:52
    #38895948
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
покажите EXPLAIN (analyze, buffers) SELECT ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 14:11
    #38895980
sherzod_
sherzod_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
LeXa NalBat,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
pgtest=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on) select count(*) from params where (sex = 'female') group by sex;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..6654.21 rows=1 width=6) (actual time=797.462..797.464 rows=1 loops=1)
   Output: count(*), sex
   Buffers: shared hit=545
   ->  Index Only Scan using _params_sex_idx on public.params  (cost=0.00..5660.89 rows=198662 width=6) (actual time=0.485..404.217 rows=198310 loops=1)
         Output: sex
         Index Cond: (params.sex = 'female'::text)
         Heap Fetches: 0
         Buffers: shared hit=545
 Total runtime: 797.545 ms
(9 rows)


Это много потому что таких колонок может быть до 1000.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 14:33
    #38896037
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
sherzod_LeXa NalBat,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
pgtest=# EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on) select count(*) from params where (sex = 'female') group by sex;
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..6654.21 rows=1 width=6) (actual time=797.462..797.464 rows=1 loops=1)
   Output: count(*), sex
   Buffers: shared hit=545
   ->  Index Only Scan using _params_sex_idx on public.params  (cost=0.00..5660.89 rows=198662 width=6) (actual time=0.485..404.217 rows=198310 loops=1)
         Output: sex
         Index Cond: (params.sex = 'female'::text)
         Heap Fetches: 0
         Buffers: shared hit=545
 Total runtime: 797.545 ms
(9 rows)


Это много потому что таких колонок может быть до 1000.

В индексе нет "информация о количествах значений" и никогда не было.
Select count(*) всегда будет линейно замедлятся от того сколько строк надо подсчитать... index only scan там или не index only scan.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 14:47
    #38896065
sherzod_
sherzod_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
Maxim Boguk,

Да сейчас внимательно все просмотрев, я понял, что сделал ошибочный вывод. И в целом неверно сформулировал проблему.
Под "информацией о кол-ве значений" я имел в виду, что ПГ может подсчитать значения не поднимаю саму таблицу в память, а используя только индекс.

Проблема как оказалось в другом. Не буду плодить темы, и опишу ее здесь:
Есть простая таблица с большим кол-вом колонок. Рассмотрим 2 sex и age. Запрос вида

Код: sql
1.
select count(*) from params where sex = 'female' group by sex


использует индекс. А вот такой нет (группировка по отличному от фильтра полю):

Код: sql
1.
select count(*) from params where sex = 'female' group by age


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
 HashAggregate  (cost=57819.56..57819.87 rows=31 width=4) (actual time=1613.279..1613.432 rows=88 loops=1)
   Output: count(*), age
   Buffers: shared hit=50473
   ->  Seq Scan on public.params  (cost=0.00..56826.25 rows=198662 width=4) (actual time=0.078..1126.072 rows=198310 loops=1)
         Output: age, sex
         Filter: ((params.sex)::text = 'female'::text)
         Rows Removed by Filter: 309950
         Buffers: shared hit=50473
 Total runtime: 1614.168 ms
(9 rows)


Индекс по двум полям построить не получится потому, что полей очень много и всевозможных их сочетаний не предусмотреть. Как можно оптимизировать подобные запросы (фильтры по разным полям, группировка по одному)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 16:01
    #38896217
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
sherzod_,

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

PS: смысла в таких запросах нет в 99% случаев. Тем более в online не устаревшей информации такого рода.

--Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.03.2015, 16:03
    #38896233
KRED
KRED
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
sherzod_Maxim Boguk,

Да сейчас внимательно все просмотрев, я понял, что сделал ошибочный вывод. И в целом неверно сформулировал проблему.
Под "информацией о кол-ве значений" я имел в виду, что ПГ может подсчитать значения не поднимаю саму таблицу в память, а используя только индекс.

Проблема как оказалось в другом. Не буду плодить темы, и опишу ее здесь:
Есть простая таблица с большим кол-вом колонок. Рассмотрим 2 sex и age. Запрос вида

Код: sql
1.
select count(*) from params where sex = 'female' group by sex


использует индекс. А вот такой нет (группировка по отличному от фильтра полю):

Код: sql
1.
select count(*) from params where sex = 'female' group by age



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

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

На счот твоей "фундаментальной" проблемы могу предложить пару вариантов:
- табличка с множеством одноколоночных индексов. Postgres сам решит какой индекс лудше. (собирайте статистику :-) ) тут каждый запрос будет вычисляться так быстро как может база одним тредом/ЦПУ.
- elasticsearch может делать что то подобное и парралельно. запросы будут также вычисляться но уже паралельно на нескольких цпу/серверах.
- сделать полное "преагрегирование" твоих данных, по этой теме ищи в инете начиная с "OLAP" и заканчивая hadoop. Тут запросы не считаются, а просто ищутся уже раннее прощитаные ответы и отдаются. Как правило за пару сотых/тысячных секунды.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2015, 17:49
    #38897511
sherzod_
sherzod_
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация GroupAggregate, фильтр, много записей.
KRED, Maxim Boguk

Спасибо за ответы.

Предрасчитать не получится, так как всевозможных множеств записей 2^10млн. Соответственно OLAP тут тоже не поможет. Hadoop не для операционных запросов, с него как раз сейчас слезаем именно по таким типам запросов. Postgres тащит, а для масштабирования - Postgres-XL.

Да сейчас именно так и делается, на каждую колонку индекс. Результаты приемлимые, просто думал, будут лучше, если ПГ будет считать count по индексу, не сканируя таблицу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация GroupAggregate, фильтр, много записей. / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 391ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1998135]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 70ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 58ms
get tp. blocked users:
 2ms
others: 214ms
total:  391ms
x
x
Закрыть


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