ReSQL.ru
     
powered by simpleCommunicator - 2.0.54     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
25 сообщений из 142, страница 3 из 6
  3  все
Тяжелый запрос к связанным таблицам
    #39330088
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstorm ...
А какие еще варианты для пагинации?
от достигнутого [WHERE {unique_index_list}>{last_index_row} ORDER BY {unique_index_list} LIMIT $Page ]
или однопроходный -- "сразу всё" с сохранением нарезки границ

у всех есть свои модельные ограничения, [как и у многократного оффсета в быстроменяющейся обстановке]. но затрат сильно меньше
...
Рейтинг: 0 / 0
19.10.2016, 17:32
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330155
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
maxxstormА какие еще варианты для пагинации?
http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way
...
Рейтинг: 0 / 0
19.10.2016, 19:49
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330349
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqmaxxstorm ...
А какие еще варианты для пагинации?
от достигнутого [WHERE {unique_index_list}>{last_index_row} ORDER BY {unique_index_list} LIMIT $Page ]
или однопроходный -- "сразу всё" с сохранением нарезки границ

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

Добавил индекс по tag_id, material_id. Теперь backword план работает при лимите 1000. Осталось переписать пагинацию
...
Рейтинг: 0 / 0
20.10.2016, 09:44
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330397
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Вот план:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Limit  (cost=1.00..27509.35 rows=50 width=12) (actual time=1517.514..1517.514 rows=0 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..2673262.14 rows=4859 width=12) (actual time=1517.514..1517.514 rows=0 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..463394.21 rows=540016 width=12) (actual time=0.025..655.094 rows=491587 loops=1)"
"              Index Cond: ((detection_datetime >= '2016-10-10 00:00:00+03'::timestamp with time zone) AND (detection_datetime < '2016-10-15 00:00:00+03'::timestamp with time zone))"
"        ->  Index Only Scan using san_materialdata_mat_tag on san_materialdata smd  (cost=0.57..63.53 rows=18 width=4) (actual time=0.002..0.002 rows=0 loops=491587)"
"              Index Cond: ((tag_id = 613) AND (material_id = sm.id))"
"              Heap Fetches: 0"
"Planning time: 0.481 ms"
"Execution time: 1517.565 ms"



Еще вопрос, если я добавлю в exist еще одно поле(stop=false), мне надо будет сделать тройной индекс (tag_id, material_id, stop) или просто добавить к существующему (tag_id, material_id) одинарный индекс на поле stop?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE 
           sm.detection_datetime >='2016-10-10 00:00:00+03:00'
           AND sm.detection_datetime<'2016-10-15 00:00:00+03:00'
          AND EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 613 
                                    AND stop=false -- <--- новый фильтр
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 50;
...
Рейтинг: 0 / 0
20.10.2016, 10:25
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330829
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
После создания индекса (material_id, tag_id) выяснилось, что есть теги, которые редко встречаются и для них план запрос "вдоль индекса не подходит", зато старый(джойн) работает быстро. Для сравнения 2 разных плана для одного и того же запроса:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
explain analyze SELECT  sm.id , sm.detection_datetime
FROM san_material sm
WHERE EXISTS( SELECT 1  
                               FROM san_materialdata smd
                               WHERE 
                                    sm.id = smd.material_id
                                    AND smd.tag_id = 597
                                    AND smd.stop=false
                                    ORDER BY material_id LIMIT 1
                                )
ORDER BY sm.detection_datetime DESC
LIMIT 5;



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
"Limit  (cost=1.00..3076.66 rows=5 width=12) (actual time=93.481..188.874 rows=5 loops=1)"
"  ->  Nested Loop Semi Join  (cost=1.00..39526592.69 rows=64257 width=12) (actual time=93.481..188.872 rows=5 loops=1)"
"        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..5828446.90 rows=7140822 width=12) (actual time=0.067..84.830 rows=57371 loops=1)"
"        ->  Index Scan using san_materialdata_mat_tag on san_materialdata smd  (cost=0.57..17.08 rows=4 width=4) (actual time=0.002..0.002 rows=0 loops=57371)"
"              Index Cond: ((tag_id = 597) AND (material_id = sm.id))"
"              Filter: (NOT stop)"
"Planning time: 0.421 ms"
"Execution time: 188.917 ms"



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
"Limit  (cost=685867.90..685867.92 rows=5 width=12) (actual time=17.422..17.424 rows=5 loops=1)"
"  ->  Sort  (cost=685867.90..686027.60 rows=63880 width=12) (actual time=17.419..17.419 rows=5 loops=1)"
"        Sort Key: sm.detection_datetime DESC"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  Nested Loop  (cost=683536.81..684806.88 rows=63880 width=12) (actual time=4.862..16.925 rows=1071 loops=1)"
"              ->  HashAggregate  (cost=683536.38..683537.88 rows=150 width=4) (actual time=4.836..5.216 rows=1071 loops=1)"
"                    Group Key: smd.material_id"
"                    ->  Bitmap Heap Scan on san_materialdata smd  (cost=5043.83..682861.85 rows=269814 width=4) (actual time=0.671..3.977 rows=1711 loops=1)"
"                          Recheck Cond: (tag_id = 597)"
"                          Filter: (NOT stop)"
"                          Heap Blocks: exact=1077"
"                          ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4976.38 rows=269841 width=0) (actual time=0.414..0.414 rows=1711 loops=1)"
"                                Index Cond: (tag_id = 597)"
"              ->  Index Scan using san_material_pkey on san_material sm  (cost=0.43..8.45 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1071)"
"                    Index Cond: (id = smd.material_id)"
"Planning time: 0.634 ms"
"Execution time: 17.506 ms"
...
Рейтинг: 0 / 0
20.10.2016, 16:11
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39330945
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormПосле создания индекса (material_id, tag_id) выяснилось, что есть теги, которые редко встречаются и для них план запрос "вдоль индекса не подходит"...
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
  ->  Nested Loop Semi Join  (cost=1.00..39526592.69 rows=64257 width=12) (actual time=93.481..188.872 rows=5 loops=1)
        ->  Index Scan Backward using san_material_detection_datetime_cb0a0120_uniq on san_material sm  (cost=0.43..5828446.90 rows=7140822 width=12) (actual time=0.067..84.830 rows=57371 loops=1)
        ->  Index Scan using san_materialdata_mat_tag on san_materialdata smd  (cost=0.57..17.08 rows=4 width=4) (actual time=0.002..0.002 rows=0 loops=57371)
              Index Cond: ((tag_id = 597) AND (material_id = sm.id))
              Filter: (NOT stop)"
Planning time: 0.421 ms
Execution time: 188.917 ms




получается по 1000 пустых поисков на запись, а не по 20, как ожидалось. т.е. в 50 раз дороже ожидаемого.

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

второй вариант -- вести массивы тагов (tags int[]) материала в той же таблице, где даты. в новом поле.
пользоваться btree_gist смешанным индексом (data,tags) . Никаких джойнов -- все в одном. но при частых апдейтах это будет скорее головной болью, чем решением. хотя рискнуть можно.
все удобства от Бортунова и К. они это всё в свое время понаписали.


по поводу "стопа" -- надо смотреть , зачем он вам. есть много вариаций. условный индекс в т.ч. (WHERE NOT stop), а в составном оно д.б. первым номером. в конце оно бессмысленно.
...
Рейтинг: 0 / 0
20.10.2016, 18:01
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334327
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwq
получается по 1000 пустых поисков на запись, а не по 20, как ожидалось. т.е. в 50 раз дороже ожидаемого.

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

второй вариант -- вести массивы тагов (tags int[]) материала в той же таблице, где даты. в новом поле.
пользоваться btree_gist смешанным индексом (data,tags) . Никаких джойнов -- все в одном. но при частых апдейтах это будет скорее головной болью, чем решением. хотя рискнуть можно.
все удобства от Бортунова и К. они это всё в свое время понаписали.


по поводу "стопа" -- надо смотреть , зачем он вам. есть много вариаций. условный индекс в т.ч. (WHERE NOT stop), а в составном оно д.б. первым номером. в конце оно бессмысленно.

Спасибо за советы, вы очень помогли. Еще вопрос из той же области. Есть 2 БД одинаковой структуры, тест и бой.
Один и тот же запрос идет по разным планам(таблицы те же):
Код: sql
1.
2.
3.
SELECT "san_materialdata"."id" , material_id
FROM "san_materialdata" 
WHERE (material_id=5858304 and "san_materialdata"."tag_id" = 675)



Тест:
Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..1418.62 rows=3 width=8) (actual time=0.049..0.053 rows=3 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 73"
"Planning time: 0.105 ms"
"Execution time: 0.074 ms"



Бой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Bitmap Heap Scan on san_materialdata  (cost=5262.46..5278.50 rows=4 width=8) (actual time=1321.956..1321.956 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 675))"
"  ->  BitmapAnd  (cost=5262.46..5262.46 rows=4 width=0) (actual time=1321.950..1321.950 rows=0 loops=1)"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.59 rows=1869 width=0) (actual time=0.035..0.035 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5227.62 rows=283607 width=0) (actual time=1318.741..1318.741 rows=890668 loops=1)"
"              Index Cond: (tag_id = 675)"
"Planning time: 0.183 ms"
"Execution time: 1322.008 ms"



В чем причина?
...
Рейтинг: 0 / 0
26.10.2016, 13:04
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334362
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
maxxstorm,

по всему, у вас данные разные. в тест "actual time=0.049..0.053 rows=3 loops=1", в боевом"actual time=1321.956..1321.956 rows=0 loops=1". с разными данными, их количеством, или распределением по таблице, можно получить разные планы.
...
Рейтинг: 0 / 0
26.10.2016, 13:35
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334367
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Lonepsychomaxxstorm,

по всему, у вас данные разные. в тест "actual time=0.049..0.053 rows=3 loops=1", в боевом"actual time=1321.956..1321.956 rows=0 loops=1". с разными данными, их количеством, или распределением по таблице, можно получить разные планы.

На боевом их больше в 1.5 раза. Я понимаю, что проблема решается составным индексом (tag_id, material_id), но все равно интересно знать почему так происходит.
...
Рейтинг: 0 / 0
26.10.2016, 13:39
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334464
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormqwwqполучается по 1000 пустых поисков на запись, а не по 20, как ожидалось. т.е. в 50 раз дороже ожидаемого.

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

второй вариант -- вести массивы тагов (tags int[]) материала в той же таблице, где даты. в новом поле.
пользоваться btree_gist смешанным индексом (data,tags) . Никаких джойнов -- все в одном. но при частых апдейтах это будет скорее головной болью, чем решением. хотя рискнуть можно.
все удобства от Бортунова и К. они это всё в свое время понаписали.


по поводу "стопа" -- надо смотреть , зачем он вам. есть много вариаций. условный индекс в т.ч. (WHERE NOT stop), а в составном оно д.б. первым номером. в конце оно бессмысленно.

Спасибо за советы, вы очень помогли. Еще вопрос из той же области. Есть 2 БД одинаковой структуры, тест и бой.
Один и тот же запрос идет по разным планам(таблицы те же):
Код: sql
1.
2.
3.
SELECT "san_materialdata"."id" , material_id
FROM "san_materialdata" 
WHERE (material_id=5858304 and "san_materialdata"."tag_id" = 675)



Тест:
Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..1418.62 rows=3 width=8) (actual time=0.049..0.053 rows=3 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 73"
"Planning time: 0.105 ms"
"Execution time: 0.074 ms"



Бой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
"Bitmap Heap Scan on san_materialdata  (cost=5262.46..5278.50 rows=4 width=8) (actual time=1321.956..1321.956 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 675))"
"  ->  BitmapAnd  (cost=5262.46..5262.46 rows=4 width=0) (actual time=1321.950..1321.950 rows=0 loops=1)"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.59 rows=1869 width=0) (actual time=0.035..0.035 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5227.62 rows=283607 width=0) (actual time=1318.741..1318.741 rows=890668 loops=1)"
"              Index Cond: (tag_id = 675)"
"Planning time: 0.183 ms"
"Execution time: 1322.008 ms"



В чем причина?
причина в cost--based оптимизации и статистиках, на которые она опирается.
например почему то для заданного материал_ид оно ожидает 1869 строк, а находит 6 -- разница более чем в 2 порядка. как вот оно так ? вы давно статистику собирали ? или индекс нажрался мертвых строк ?

то же по тагам -- ожидания в 3 раза меньше реальности. ну это нщё сносно.
сделайте енейбл битмап скан ту офф -- и посмотрите на оценку цены в плане для "хорошего" плана.

в пж слишком мало костовых констант -- видимо оптимайзер модельно очень дубовый. костами спихнуть с одного индексного поиска на другой тип индексного же поиска не удастся, думаю, хотя можно попробовать. (а енейблы сосут при 10^9 строк, т.к. "большая" константа пропадает на фоне).
...
Рейтинг: 0 / 0
26.10.2016, 15:00
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334481
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
qwwqпричина в cost--based оптимизации и статистиках, на которые она опирается.
например почему то для заданного материал_ид оно ожидает 1869 строк, а находит 6 -- разница более чем в 2 порядка. как вот оно так ? вы давно статистику собирали ? или индекс нажрался мертвых строк ?

то же по тагам -- ожидания в 3 раза меньше реальности. ну это нщё сносно.
сделайте енейбл битмап скан ту офф -- и посмотрите на оценку цены в плане для "хорошего" плана.

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

Статистику собирал непосредственно перед запросом. Насчет мертвых строк сомневаюсь. Когда они появляются? При делетах?

Если выключить битмапскан, то план хороший:

Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.060..0.060 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"Planning time: 0.177 ms"
"Execution time: 0.088 ms"
...
Рейтинг: 0 / 0
26.10.2016, 15:12
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334600
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
maxxstormЕсли выключить битмапскан, то план хороший:

Код: sql
1.
2.
3.
4.
5.
6.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.060..0.060 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"Planning time: 0.177 ms"
"Execution time: 0.088 ms"

вот у вас оценка пессимистическая "6676.53" при ожидании 4 строк. она больше пессимистической оценки для битмапа (cost=5262.46..5278.50). а вот откуда она такая большая ?
я бы с потолка насчитал 4 произвольных ценой (если не крутили) по 4 + найти нужную страничку индексов -- чтонть пессимистически странички 4 -- максимум 20--32 по произвольным набегает. откуда это вот угрёбище нагребает 6676.53 -- тайна великая есть. могабыть в статистике что ?
и зачем сравнивает только писси-мяссиские велчичины. "и вот всё у них так."(сс)
...
Рейтинг: 0 / 0
26.10.2016, 16:24
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334608
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwq,

всё время путаюсь. диапазон костов -- это оценка затрат на вывод первой и последней строки соответственно. из 4. а не пессимистич/оптимистич.

см https://habrahabr.ru/post/203320/

вот откуда оно 6000 набрало -- хз
...
Рейтинг: 0 / 0
26.10.2016, 16:34
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334630
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?
...
Рейтинг: 0 / 0
26.10.2016, 16:46
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334633
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
vyegorovqwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
"Bitmap Heap Scan on san_materialdata  (cost=5284.17..5300.21 rows=4 width=12) (actual time=1190.771..1190.771 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 600))"
"  Buffers: shared hit=3085"
"  ->  BitmapAnd  (cost=5284.17..5284.17 rows=4 width=0) (actual time=1190.765..1190.765 rows=0 loops=1)"
"        Buffers: shared hit=3085"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.65 rows=1878 width=0) (actual time=0.034..0.034 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"              Buffers: shared hit=4"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5249.27 rows=284893 width=0) (actual time=1188.350..1188.350 rows=1045140 loops=1)"
"              Index Cond: (tag_id = 600)"
"              Buffers: shared hit=3081"
"Planning time: 0.187 ms"
"Execution time: 1190.824 ms"
...
Рейтинг: 0 / 0
26.10.2016, 16:50
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334660
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Если без скана, то

Код: sql
1.
2.
3.
4.
5.
6.
7.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.041..0.041 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"  Buffers: shared hit=5"
"Planning time: 0.152 ms"
"Execution time: 0.069 ms"
...
Рейтинг: 0 / 0
26.10.2016, 17:13
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334727
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovqwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?
я вот думаю, что ожидается 4 записи после фильтра. м.б. до фильтра ожидается какое--то безумное число ? надо бы пошаманить -- посмотреть что в pg_statistic валяется и как это м.б. связано с той цифирью, которую мы видим как результат накрутки костов для 4--х доступов. думаю -- все таки бага где--то. или мы какой--то инфы о модели не знаем (стотысячмиллионов апдейтов, например)
...
Рейтинг: 0 / 0
26.10.2016, 18:33
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334734
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
о, слайды:

http://pgday.ru/files/papers/17/pgday2015.using-postgresql-statistics.pdf
...
Рейтинг: 0 / 0
26.10.2016, 18:42
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334897
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
maxxstormvyegorovqwwq,

Оценка строится на `%_cost` параметрах. Основная доля там — обращение к страницам, для индексов это `random_page_cost`.
Учитвыая, что ожидается всего 4 записи и также то, что индекс читается не весь, а "по дереву", то оценка в 6600 довольна высока.
Возможно (я не разбирался в оценке индексных доступов, только последовательных) сюда закладывается также и доступ к 4-м (в худшем случае) блокам из основной таблицы. Но всё равно — это очень много для 4-х записей.

А что выведет `EXPLAIN (analyze, buffers)`?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
"Bitmap Heap Scan on san_materialdata  (cost=5284.17..5300.21 rows=4 width=12) (actual time=1190.771..1190.771 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 600))"
"  Buffers: shared hit=3085"
"  ->  BitmapAnd  (cost=5284.17..5284.17 rows=4 width=0) (actual time=1190.765..1190.765 rows=0 loops=1)"
"        Buffers: shared hit=3085"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.65 rows=1878 width=0) (actual time=0.034..0.034 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"              Buffers: shared hit=4"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5249.27 rows=284893 width=0) (actual time=1188.350..1188.350 rows=1045140 loops=1)"
"              Index Cond: (tag_id = 600)"
"              Buffers: shared hit=3081"
"Planning time: 0.187 ms"
"Execution time: 1190.824 ms"



А у вас случайно
1)default_statistics_target на тесте и в бою не отличается?
2)случайно не выставлен руками n_distinct на san_materialdata_eb4b9aaa.material_id в тесте или другое значение statistic?

Что говорит
Код: plsql
1.
explain analyze select * from san_materialdata_eb4b9aaa where material_id = 5858304

в бою и в тесте?
И меняется ли что то после ручного выполнения analyze san_materialdata_eb4b9aaa; в бою и в тесте в результатах explain analyze?

Вероятнее всего не хватает статистки в бою адекватной и надо будет или statistics поднимать для поля (или по всей базе) или n_distinct ему руками прописывать.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
27.10.2016, 03:31
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39334948
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Maxim BogukА у вас случайно
1)default_statistics_target на тесте и в бою не отличается?
2)случайно не выставлен руками n_distinct на san_materialdata_eb4b9aaa.material_id в тесте или другое значение statistic?

Что говорит
Код: plsql
1.
explain analyze select * from san_materialdata_eb4b9aaa where material_id = 5858304

в бою и в тесте?
И меняется ли что то после ручного выполнения analyze san_materialdata_eb4b9aaa; в бою и в тесте в результатах explain analyze?

Вероятнее всего не хватает статистки в бою адекватной и надо будет или statistics поднимать для поля (или по всей базе) или n_distinct ему руками прописывать.

--
Maxim Boguk
www.postgresql-consulting.ru

1) default_statistics_target одинаковая, равна 100
2) n_distinct руками не ставился, но он различается, на бою 63880, на тесте 47247
3) Для explain analyze select * from san_materialdata_eb4b9aaa where material_id = 5858304 планы тоже различаются:

Бой:
Код: sql
1.
2.
3.
4.
5.
6.
7.
"Bitmap Heap Scan on san_materialdata  (cost=35.23..7420.48 rows=1892 width=62) (actual time=0.145..0.147 rows=6 loops=1)"
"  Recheck Cond: (material_id = 5858304)"
"  Heap Blocks: exact=1"
"  ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.76 rows=1892 width=0) (actual time=0.104..0.104 rows=6 loops=1)"
"        Index Cond: (material_id = 5858304)"
"Planning time: 0.545 ms"
"Execution time: 0.171 ms"



Тест:
Код: sql
1.
2.
3.
4.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..1185.58 rows=1541 width=50) (actual time=0.106..0.144 rows=76 loops=1)"
"  Index Cond: (material_id = 5858304)"
"Planning time: 0.500 ms"
"Execution time: 0.182 ms"



Однако, после сбора статистики, план на бою становится таким же как на тесте, что нельзя сказать о первоначальном запросе.
...
Рейтинг: 0 / 0
27.10.2016, 09:12
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39335147
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
"Bitmap Heap Scan on san_materialdata  (cost=5284.17..5300.21 rows=4 width=12) (actual time=1190.771..1190.771 rows=0 loops=1)"
"  Recheck Cond: ((material_id = 5858304) AND (tag_id = 600))"
"  Buffers: shared hit=3085"
"  ->  BitmapAnd  (cost=5284.17..5284.17 rows=4 width=0) (actual time=1190.765..1190.765 rows=0 loops=1)"
"        Buffers: shared hit=3085"
"        ->  Bitmap Index Scan on san_materialdata_eb4b9aaa  (cost=0.00..34.65 rows=1878 width=0) (actual time=0.034..0.034 rows=6 loops=1)"
"              Index Cond: (material_id = 5858304)"
"              Buffers: shared hit=4"
"        ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..5249.27 rows=284893 width=0) (actual time=1188.350..1188.350 rows=1045140 loops=1)"
"              Index Cond: (tag_id = 600)"
"              Buffers: shared hit=3081"
"Planning time: 0.187 ms"
"Execution time: 1190.824 ms"

"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..6676.53 rows=4 width=8) (actual time=0.041..0.041 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 675)"
"  Rows Removed by Filter: 6"
"  Buffers: shared hit=5"
"Planning time: 0.152 ms"
"Execution time: 0.069 ms"




планировщик считает кост не для 4 строк, а для 1878, поэтому такая оценка большая. что выдает запрос

Код: sql
1.
select null_frac, most_common_vals, most_common_freqs from pg_stats where tablename = 'san_materialdata' and attname = 'material_id';


? скорей всего надо будет добавить статистики по этому полю, чтобы не было ошибки на 2 порядка:
Код: sql
1.
2.
alter table san_materialdata alter column material_id set statistics 1000;
analyze san_materialdata;
...
Рейтинг: 0 / 0
27.10.2016, 11:32
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39335158
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexius
планировщик считает кост не для 4 строк, а для 1878, поэтому такая оценка большая. что выдает запрос

Код: sql
1.
select null_frac, most_common_vals, most_common_freqs from pg_stats where tablename = 'san_materialdata' and attname = 'material_id';


? скорей всего надо будет добавить статистики по этому полю, чтобы не было ошибки на 2 порядка:
Код: sql
1.
2.
alter table san_materialdata alter column material_id set statistics 1000;
analyze san_materialdata;



Вот что выдает:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
         0 | {1192860,1130697,705790,500500,599105,1153816,1767790,1907922,533945,1019058,1279798,2098764,2786912,
4065076,4442067,5014040,442362,831508,861475,929361,953667,1111350,1306808,1525637,1590370,1634612,1648336,1722187,
1954306,2043576,2117452,2318435,2464641,2501635,2531126,2807001,2860650,2872008,2967316,2997551,3017516,3215033,
3218870,3385712,3487426,3557502,3610889,3669507,3773322,3846947,4008954,4056740,4151981,4176495,4178926,4263691,
4264794,4366766,5388776,6677228,383076,395561,426592,589781,703157,715245,737659,762380,791073,798228,822590,
911859,917936,945688,984803,1119393,1209657,1308680,1325768,1329760,1358209,1389170,1396118,1417715,1479611,
1543218,1568069,1577566,1666076,1689851,1701960,1703076,1712447,1729870,1735242,1759967,1790674,1810619,1821964,1833650} | 
{0.000566667,0.000533333,0.0004,0.000233333,0.000233333,0.000233333,0.000233333,0.000233333,0.0002,0.0002,0.0002,
0.0002,0.0002,0.0002,0.0002,0.0002,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,
0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000166667,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,
0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333,0.000133333}
...
Рейтинг: 0 / 0
27.10.2016, 11:37
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39335166
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Alexiusalter table san_materialdata alter column material_id set statistics 1000;
analyze san_materialdata;

[/src]

Сделал, план запроса стал хорошим:

Код: sql
1.
2.
3.
4.
5.
6.
7.
"Index Scan using san_materialdata_eb4b9aaa on san_materialdata  (cost=0.57..794.32 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)"
"  Index Cond: (material_id = 5858304)"
"  Filter: (tag_id = 630)"
"  Rows Removed by Filter: 6"
"  Buffers: shared hit=5"
"Planning time: 0.085 ms"
"Execution time: 0.025 ms"
...
Рейтинг: 0 / 0
27.10.2016, 11:43
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39343503
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
Еще похожая проблема с запросом
Код: 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)))



Выдает план:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
"Aggregate  (cost=680181.83..680181.84 rows=1 width=0)"
"  ->  Nested Loop  (cost=671036.96..680176.53 rows=2120 width=0)"
"        ->  HashAggregate  (cost=671036.53..671047.42 rows=1089 width=4)"
"              Group Key: u0.material_id"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=4833.89..670420.71 rows=246329 width=4)"
"                    Recheck Cond: (tag_id = 602)"
"                    Filter: (NOT stop)"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4772.31 rows=246365 width=0)"
"                          Index Cond: (tag_id = 602)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.37 rows=1 width=4)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-08 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-08 23:59:59.999999+03'::timestamp with time zone))"



Однако, если взять другой тег
Код: 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 (660) AND U0."stop" = false)))



или меньший интервал
Код: sql
1.
2.
3.
explain SELECT COUNT(*) AS "__count" FROM "san_material" 
WHERE ("san_material"."detection_datetime" BETWEEN '2016-11-08T00:00:00+03:00'::timestamptz AND '2016-11-08T12: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)))



то план тот, что нужно:
Код: sql
1.
2.
3.
4.
5.
6.
7.
"Aggregate  (cost=683252.49..683252.50 rows=1 width=0)"
"  ->  Nested Loop Semi Join  (cost=1.00..683247.65 rows=1937 width=0)"
"        ->  Index Scan using san_material_detection_datetime_cb0a0120_uniq on san_material  (cost=0.43..43611.92 rows=79027 width=4)"
"              Index Cond: ((detection_datetime >= '2016-11-08 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-08 12:59:59.999999+03'::timestamp with time zone))"
"        ->  Index Scan using san_materialdata_material_id_32150950_idx on san_materialdata u0  (cost=0.57..8.09 rows=1 width=4)"
"              Index Cond: ((material_id = san_material.id) AND (tag_id = 602))"
"              Filter: (NOT stop)"



Насколкьо страшно будет, если отключить enable_bitmapscan?
Как решить без отключения?
...
Рейтинг: 0 / 0
08.11.2016, 14:23
| Ответить | Цитировать | Написать  
Тяжелый запрос к связанным таблицам
    #39344363
maxxstorm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
maxxstorm
Участник
А вот для другого тега план такой же, но ожидаемое количество записей на порядок меньше реального:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
"Aggregate  (cost=700708.29..700708.30 rows=1 width=4) (actual time=148.255..148.255 rows=1 loops=1)"
"  ->  Nested Loop  (cost=691124.49..700692.41 rows=6352 width=4) (actual time=47.021..148.238 rows=64 loops=1)"
"        ->  HashAggregate  (cost=691124.06..691135.46 rows=1140 width=4) (actual time=46.263..53.141 rows=25458 loops=1)"
"              Group Key: u0.material_id"
"              ->  Bitmap Heap Scan on san_materialdata u0  (cost=5041.54..690483.74 rows=256125 width=4) (actual time=7.506..34.393 rows=35858 loops=1)"
"                    Recheck Cond: (tag_id = 630)"
"                    Heap Blocks: exact=17356"
"                    ->  Bitmap Index Scan on san_materialdata_76f094bc  (cost=0.00..4977.51 rows=256125 width=0) (actual time=4.567..4.567 rows=35858 loops=1)"
"                          Index Cond: (tag_id = 630)"
"        ->  Index Scan using san_material_pkey on san_material  (cost=0.43..8.37 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=25458)"
"              Index Cond: (id = u0.material_id)"
"              Filter: ((detection_datetime >= '2016-11-07 00:00:00+03'::timestamp with time zone) AND (detection_datetime <= '2016-11-09 23:59:59.999999+03'::timestamp with time zone))"
"              Rows Removed by Filter: 1"
"Planning time: 0.396 ms"
"Execution time: 148.311 ms"
...
Рейтинг: 0 / 0
09.11.2016, 17:20
| Ответить | Цитировать | Написать  
25 сообщений из 142, страница 3 из 6
  3  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос к связанным таблицам
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39330088&tid=1996781]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 39ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 62ms
get tp. blocked users:
 1ms
others: 13ms
total:  152ms
созд. / загр.: 152ms
x
x
Закрыть


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