ReSQL.ru
     
powered by simpleCommunicator - 2.0.54     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
25 сообщений из 142, страница 4 из 6
  4  все
Тяжелый запрос к связанным таблицам
    #39344398
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstorm,

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

для тестов отключать enable_* параметры очень полезно чтобы перебрать планы для фиксированного запроса и разобраться. на практике это обычно крайняя мера, когда по-другому совсем никак, что бывает редко.
...
Рейтинг: 0 / 0
09.11.2016, 18:10
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344649
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexiusmaxxstorm,

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

для тестов отключать enable_* параметры очень полезно чтобы перебрать планы для фиксированного запроса и разобраться. на практике это обычно крайняя мера, когда по-другому совсем никак, что бывает редко.

Добавил статистики по полю tag_ig также до 1000.
Отключил enable_bitmapscan. Вот что получается:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Aggregate  (cost=960100.02..960100.03 rows=1 width=4)"
"  ->  Nested Loop  (cost=950517.60..960081.03 rows=7593 width=4)"
"        ->  HashAggregate  (cost=950517.16..950528.55 rows=1139 width=4)"
"              Group Key: u0.material_id"
"              ->  Index Scan using san_materialdata_76f094bc on san_materialdata u0  (cost=0.57..949882.85 rows=253723 width=4)"
"                    Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.38 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-02 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-03 23:59:59.999999+03'::timestamp with time zone))"



Почему он пытается агрегировать?
...
Рейтинг: 0 / 0
10.11.2016, 11:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344651
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Отключаю enable_hashagg, получается еще интереснее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
"Aggregate  (cost=994835.97..994835.98 rows=1 width=4)"
"  ->  Nested Loop  (cost=983980.20..994816.75 rows=7688 width=4)"
"        ->  Unique  (cost=983979.76..985264.26 rows=1139 width=4)"
"              ->  Sort  (cost=983979.76..984622.01 rows=256900 width=4)"
"                    Sort Key: u0.material_id"
"                    ->  Index Scan using san_materialdata_76f094bc on san_materialdata u0  (cost=0.57..960896.21 rows=256900 width=4)"
"                          Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.38 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-02 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-03 23:59:59.999999+03'::timestamp with time zone))"
...
Рейтинг: 0 / 0
10.11.2016, 11:21
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344685
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

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

EXPLAIN (analyze, buffers)
...
Рейтинг: 0 / 0
10.11.2016, 12:02
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344745
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
Почему он пытается агрегировать?
патамушта каунт -- агрегатная ф--я.



приведите сам запрос еще раз, а то последнее :

Код: plaintext
Sort >  Unique > Aggregate

наводят на мысли о наличии count(distinct ...)
...
Рейтинг: 0 / 0
10.11.2016, 12:49
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344753
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstormПочему он пытается агрегировать?
патамушта каунт -- агрегатная ф--я.



приведите сам запрос еще раз, а то последнее :

Код: plaintext
Sort >  Unique > Aggregate

наводят на мысли о наличии count(distinct ...)

Код: sql
1.
2.
3.
SELECT COUNT(*) AS "__count" FROM "san_material" 
WHERE ("san_material"."detection_datetime" BETWEEN '2016-11-08T00:00:00+03:00'::timestamptz AND '2016-11-08T23:59:59.999999+03:00'::timestamptz 
AND "san_material"."id" IN (SELECT U0."material_id" FROM "san_materialdata" U0 WHERE (U0."tag_id" IN (602) AND U0."stop" = false)))



Я понимаю, что функция агрегатная, но почему иногда по индексу идет, а иногда битмап хип скан/агрегейт?
Индекс (material_id, tag_id) не уникален, так как значения могут повторяться(несколько одинаковых тегов на один материал). Из-за этого?
...
Рейтинг: 0 / 0
10.11.2016, 12:53
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39358303
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

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

EXPLAIN (analyze, buffers)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
"Aggregate  (cost=834296.62..834296.63 rows=1 width=0) (actual time=4362356.963..4362356.963 rows=1 loops=1)"
"  Buffers: shared hit=2006033 read=1258402 dirtied=4612 written=2"
"  ->  Nested Loop  (cost=824440.20..834288.05 rows=3425 width=0) (actual time=398848.672..4362344.126 rows=7631 loops=1)"
"        Buffers: shared hit=2006033 read=1258402 dirtied=4612 written=2"
"        ->  HashAggregate  (cost=824439.77..824451.42 rows=1165 width=4) (actual time=398191.318..399277.184 rows=669380 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=37 read=547244 dirtied=25"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=6135.79..823677.44 rows=304932 width=4) (actual time=15038.557..396035.338 rows=1150184 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Heap Blocks: exact=543537"
"                    Buffers: shared hit=37 read=547244 dirtied=25"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..6059.56 rows=304932 width=0) (actual time=14790.504..14790.504 rows=1150630 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=4 read=3740"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.43 rows=1 width=4) (actual time=5.917..5.918 rows=0 loops=669380)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-29 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2005996 read=711158 dirtied=4587 written=2"
"Planning time: 0.454 ms"
"Execution time: 4362370.904 ms"



Статистика по полю material_id 1000, но все равно ошибается планировщик. Почему?
...
Рейтинг: 0 / 0
01.12.2016, 11:19
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39358924
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstorm,

покажите результаты выполнения запроса:

Код: sql
1.
select n_distinct from pg_stats where tablename = 'san_materialdata' and attname = 'material_id'



я правильно понял, что в san_materialdata 110М строк и среди них 6М уникальных material_id ?

после того, как добавлялась статистика, analyze san_materialdata делался?
...
Рейтинг: 0 / 0
01.12.2016, 21:10
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359097
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexiusmaxxstorm,

покажите результаты выполнения запроса:

Код: sql
1.
select n_distinct from pg_stats where tablename = 'san_materialdata' and attname = 'material_id'



я правильно понял, что в san_materialdata 110М строк и среди них 6М уникальных material_id ?

после того, как добавлялась статистика, analyze san_materialdata делался?

Результат выполнения:
3.31129e+06

В san_materialdata порядка 150М строк и уникальных material_id 10М.
analyze san_materialdata делался, в том числе и автоматически каждую ночь делается.
...
Рейтинг: 0 / 0
02.12.2016, 09:32
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359232
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

А можно вывод такого запроса глянуть:
Код: sql
1.
SELECT attname,null_frac,n_distinct,correlation FROM pg_stats WHERE tablename='san_materialdata' AND attname IN ('tag_id','material_id');
...
Рейтинг: 0 / 0
02.12.2016, 12:19
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359234
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

А можно вывод такого запроса глянуть:
Код: sql
1.
SELECT attname,null_frac,n_distinct,correlation FROM pg_stats WHERE tablename='san_materialdata' AND attname IN ('tag_id','material_id');



"material_id"; 0;3.31129e+06; 0.719951
"tag_id"; 0;137; -0.0315493
...
Рейтинг: 0 / 0
02.12.2016, 12:20
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359260
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwqприведите сам запрос еще раз, а то последнее :

Код: plaintext
Sort >  Unique > Aggregate

наводят на мысли о наличии count(distinct ...)
Оптимизатор понимает, что ему достаточно выдать уникальные значения и рассматривает `GROUP BY` по подзапросу.
И судя по всему он находит его привлекательным, даже с отключенным HashAgg.
...
Рейтинг: 0 / 0
02.12.2016, 12:51
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359282
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstormРезультат выполнения:
3.31129e+06

В san_materialdata порядка 150М строк и уникальных material_id 10М.
analyze san_materialdata делался, в том числе и автоматически каждую ночь делается.


не сходится что-то оценка планировщика с ожидаемой в строке с HashAggregate. а можно вывод запросов

Код: sql
1.
2.
explain select * from san_materialdata;
explain select * from san_material;



для каждой строки из san_material есть соответствующая запись в san_materialdata ?
...
Рейтинг: 0 / 0
02.12.2016, 13:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359284
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Alexius,

и еще раз план последнего запроса на всякий случай (можно без analyze), может n_distinct изменился и поэтому не сходится.
...
Рейтинг: 0 / 0
02.12.2016, 13:17
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359285
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexius

не сходится что-то оценка планировщика с ожидаемой в строке с HashAggregate. а можно вывод запросов

Код: sql
1.
2.
explain select * from san_materialdata;
explain select * from san_material;



для каждой строки из san_material есть соответствующая запись в san_materialdata ?

"Seq Scan on san_materialdata (cost=0.00..3704419.36 rows=167943136 width=62)"
"Seq Scan on san_material (cost=0.00..2623611.56 rows=12420256 width=1129)"


Не для каждой строки есть запись. Могут быть записи в san_material без тегов(записей в san_materialdata)
...
Рейтинг: 0 / 0
02.12.2016, 13:18
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359320
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
AlexiusAlexius,

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

Сделал еще раз analyze san_materialdata, теперь n_distinct немного поменялся:
Код: sql
1.
2.
"material_id";0;649634;0.726152
"tag_id";0;133;-0.0277582



Старый запрос(за 1 день) идет теперь по индексу:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Aggregate  (cost=1409740.96..1409740.97 rows=1 width=0)"
"  ->  Nested Loop Semi Join  (cost=6776.83..1409720.14 rows=8327 width=0)"
"        ->  Bitmap Heap Scan on san_material  (cost=6776.26..511318.38 rows=159202 width=4)"
"              Recheck Cond: ((detection_datetime >= '2016-11-29 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              ->  Bitmap Index Scan on san_material_detection_datetime_cb0a0120_uniq  (cost=0.00..6736.46 rows=159202 width=0)"
"                    Index Cond: ((detection_datetime >= '2016-11-29 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_material_id_32150950_idx on san_materialdata u0  (cost=0.57..10.50 rows=2 width=4)"
"              Index Cond: ((material_id = san_material.id) AND (tag_id = 602))"
"              Filter: (NOT stop)"


однако, если взять интервал за 2 дня, то картина та же:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0)"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0)"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4)"
"              Group Key: u0.material_id"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0)"
"                          Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
...
Рейтинг: 0 / 0
02.12.2016, 13:51
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359401
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).
...
Рейтинг: 0 / 0
02.12.2016, 15:23
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359439
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).

Первый индекс уже есть, а вот второй надо попробовать. Однако, щас другая проблема, один и тот же запрос отрабатывает с большой разницей по времени, когда читает с диска и из шаред буфера:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=22733.112..22733.112 rows=1 loops=1)"
"  Buffers: shared hit=70660 read=10521"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=7.941..22700.582 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48368"
"        Buffers: shared hit=70660 read=10521"
"Planning time: 0.164 ms"
"Execution time: 22733.139 ms"



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=95.168..95.168 rows=1 loops=1)"
"  Buffers: shared hit=81616"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=0.019..76.548 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48805"
"        Buffers: shared hit=81616"
"Planning time: 0.107 ms"
"Execution time: 95.191 ms"



Неужели прочитать с диска 10521 блоков занимает 22 секунды?
...
Рейтинг: 0 / 0
02.12.2016, 16:04
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359458
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovmaxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).

Вот здесь время равно ошибка планировщика в HashAggregate 677909 строк вместо ожидаемых 4575:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0) (actual time=5094071.793..5094071.793 rows=1 loops=1)"
"  Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0) (actual time=442399.330..5094045.011 rows=15416 loops=1)"
"        Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4) (actual time=441608.133..442743.481 rows=677909 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=7585 read=546810 dirtied=106"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4) (actual time=17118.598..439586.215 rows=1164661 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    Heap Blocks: exact=550599"
"                    Buffers: shared hit=7585 read=546810 dirtied=106"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0) (actual time=16889.923..16889.923 rows=1164661 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=6 read=3790"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4) (actual time=6.857..6.859 rows=0 loops=677909)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2017621 read=717834 dirtied=973"
"Planning time: 0.562 ms"
"Execution time: 5094108.176 ms"
...
Рейтинг: 0 / 0
02.12.2016, 16:14
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359460
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormvyegorovmaxxstorm,

Ну... n_distinct в 5 раз меньше стал, это много! И теперь расчёты совпадают.

Вы ловите неприятный эффект того, что планировщик не знает о том, что `san_material.material_id` и `san_material.detection_datetime` коррелируют.

Тут несколько вариантов как "вправить мозг":
- `CREATE INDEX CONCURRENTLY i_san_materialdata_tag_n_id ON san_materialdata(tag_id, material_id)` -- чтобы подзапрос быстрее отрабатывал и лучше планировался.
- `CREATE INDEX CONCURRENTLY i_san_material_dtime_id ON san_material(detection_time, id)` -- планировщик сможет точнее оценить кол-во ID-шек в заданном диапазоне дат и не идти по ПК (да и IOS может выйти)
- `CREATE INDEX CONCURRENTLY i_san_material_id_dtime ON san_material(id, detection_time)` -- так корректнее, поскольку диапазоны надо ставить в конце (дата обычно на больше-меньше проверяется, id -- на соответствие).

Первый индекс уже есть, а вот второй надо попробовать. Однако, щас другая проблема, один и тот же запрос отрабатывает с большой разницей по времени, когда читает с диска и из шаред буфера:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=22733.112..22733.112 rows=1 loops=1)"
"  Buffers: shared hit=70660 read=10521"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=7.941..22700.582 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48368"
"        Buffers: shared hit=70660 read=10521"
"Planning time: 0.164 ms"
"Execution time: 22733.139 ms"



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=16902.06..16902.07 rows=1 width=0) (actual time=95.168..95.168 rows=1 loops=1)"
"  Buffers: shared hit=81616"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..16424.47 rows=191035 width=0) (actual time=0.019..76.548 rows=191577 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-11-17 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-17 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 48805"
"        Buffers: shared hit=81616"
"Planning time: 0.107 ms"
"Execution time: 95.191 ms"



Неужели прочитать с диска 10521 блоков занимает 22 секунды?


Если у вас не ssd а механика то у вас в лучшем случае 15.000RPM диски а скорее всего 10.000RPM... максимум что можно с такого диска иметь это 200-250 случайных чтений в секунду, так что 22 секунды - еще приличный вполне результат (могло быть и в 2-3 раза медленнее легко).
...
Рейтинг: 0 / 0
02.12.2016, 16:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359511
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormВот здесь время равно ошибка планировщика в HashAggregate 677909 строк вместо ожидаемых 4575:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0) (actual time=5094071.793..5094071.793 rows=1 loops=1)"
"  Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0) (actual time=442399.330..5094045.011 rows=15416 loops=1)"
"        Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4) (actual time=441608.133..442743.481 rows=677909 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=7585 read=546810 dirtied=106"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4) (actual time=17118.598..439586.215 rows=1164661 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    Heap Blocks: exact=550599"
"                    Buffers: shared hit=7585 read=546810 dirtied=106"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0) (actual time=16889.923..16889.923 rows=1164661 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=6 read=3790"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4) (actual time=6.857..6.859 rows=0 loops=677909)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2017621 read=717834 dirtied=973"
"Planning time: 0.562 ms"
"Execution time: 5094108.176 ms"


А ПЖ не может (пока, надеюсь) увидеть корреляцию.
У `san_materialdata.material_id` 650k уникальных значений. А из таблицы, по предикату `tag_id=602` выбирается 0.7% записей.
Вот база и берёт эти самые 0.7% от 650k. И очень неловко, что по факту попадают... все, и даже чуть больше.

Кстати, вопрос -- почему HashAgg возвращает больше, чем n_distinct?..
...
Рейтинг: 0 / 0
02.12.2016, 16:52
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39359662
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
maxxstormВот здесь время равно ошибка планировщика в HashAggregate 677909 строк вместо ожидаемых 4575:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
"Aggregate  (cost=1895258.32..1895258.33 rows=1 width=0) (actual time=5094071.793..5094071.793 rows=1 loops=1)"
"  Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"  ->  Nested Loop  (cost=1856884.81..1895216.94 rows=16552 width=0) (actual time=442399.330..5094045.011 rows=15416 loops=1)"
"        Buffers: shared hit=2025206 read=1264644 dirtied=1079"
"        ->  HashAggregate  (cost=1856884.38..1856930.13 rows=4575 width=4) (actual time=441608.133..442743.481 rows=677909 loops=1)"
"              Group Key: u0.material_id"
"              Buffers: shared hit=7585 read=546810 dirtied=106"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=23855.81..1853927.92 rows=1182584 width=4) (actual time=17118.598..439586.215 rows=1164661 loops=1)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    Heap Blocks: exact=550599"
"                    Buffers: shared hit=7585 read=546810 dirtied=106"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..23560.16 rows=1182879 width=0) (actual time=16889.923..16889.923 rows=1164661 loops=1)"
"                          Index Cond: (tag_id = 602)"
"                          Buffers: shared hit=6 read=3790"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.36 rows=1 width=4) (actual time=6.857..6.859 rows=0 loops=677909)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-28 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-29 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"              Buffers: shared hit=2017621 read=717834 dirtied=973"
"Planning time: 0.562 ms"
"Execution time: 5094108.176 ms"




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

1) можно увеличить stats target для поля material_id еще больше, до 5000 или максимума 10000. минусы: analyze будет дольше (но это обычно не проблема), планирование запросов с этим полем может быть дольше - это может быть в некоторых случаях критично, если например есть много простых быстрых запросов с этой таблицей.

2) подсчитать действительное число различных material_id в этой таблице

Код: sql
1.
select count(*) from (select distinct material_id from san_materialdata) t


таким запросом скорей всего будет быстрее (если там будет hashaggregate. стоит предварительно убедиться в этом и если нет - то увеличить work_mem в сессии).

далее разделить его на 167943136 или сколько к тому моменту будет строк в таблице. и прописать полученное значение со знаком минус через
alter table san_materialdata alter column material_id set (n_distinct = x);

знак минус тут говорит планировщику что у нас не число различных значений, а отношение числа уникальных значений к общему числу строк. вполне вероятно что это соотношение в будущем не будет сильно меняться и достаточно его таким образом один раз "прибить гвоздями".
...
Рейтинг: 0 / 0
02.12.2016, 20:15
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360214
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim BogukЕсли у вас не ssd а механика то у вас в лучшем случае 15.000RPM диски а скорее всего 10.000RPM... максимум что можно с такого диска иметь это 200-250 случайных чтений в секунду, так что 22 секунды - еще приличный вполне результат (могло быть и в 2-3 раза медленнее легко).

Не поверю, что чтобы прочитать 90000 строк из индекса, понадобится 365 секунд:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Aggregate  (cost=26733.84..26733.85 rows=1 width=0) (actual time=365953.584..365953.584 rows=1 loops=1)"
"  Buffers: shared hit=130844 read=275479 dirtied=15881"
"  ->  Index Only Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..26687.58 rows=18503 width=0) (actual time=25.930..365876.681 rows=90156 loops=1)"
"        Index Cond: ((detection_datetime >= '2016-12-04 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-12-04 23:59:59.999999+03'::timestamp with time zone))"
"        Heap Fetches: 429927"
"        Buffers: shared hit=130844 read=275479 dirtied=15881"
"Planning time: 0.304 ms"
"Execution time: 365953.626 ms"



dirtied это что за блоки?
...
Рейтинг: 0 / 0
05.12.2016, 09:06
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360234
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstorm,

База читает не по-строчно, а по-блочно.
Было `read=275479` произвольных обращений к диску. Чтобы точно знать время IO операций, можно включить `track_io_timing=on`, тогда в плане будет видно реальное время работы с дисками.

`dirtied` значит, что ваш запрос должен был "подчищать" за другими запросами, проставля hint bits в записях.
...
Рейтинг: 0 / 0
05.12.2016, 10:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39360263
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm
Не поверю, что чтобы прочитать 90000 строк из индекса, понадобится 365 секунд:


если базе придется читать 90000 строк из маленькой таблички на 90 000--900 000 строк, то она просто забьёт на индексы и прочитает табличку последовательным доступом. т.е. относительно быстро. и отфильтрует.


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

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


к тому же "по индексу", но "с доступом к самим записям" -- это несколько дороже, чем "только по индексу" -- там как бе 2 отдельные физические реляции получаются. и к обеим -- физически доступаться.



зы. скоро обещают x--память, или как её -- скоро про механический доступ можно будет не вспоминать
...
Рейтинг: 0 / 0
05.12.2016, 10:36
| Ответить | Цитировать | Написать  
25 сообщений из 142, страница 4 из 6
  4  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39360234&tid=1996781]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 41ms
get topic data:
 8ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 358ms
total:  488ms
созд. / загр.: 488ms
x
x
Закрыть


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