Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос. / 11 сообщений из 11, страница 1 из 1
16.08.2018, 10:46
    #39688600
Rum11
Rum11
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Как ускорить запрос SELECT * FROM user_message_entity ume inner join message_entity me on me.id = ume.message_entity_id WHERE to_tsvector('russian', message) @@ to_tsquery('russian', 'привет') and user_entity_id = 2;

Повторный запрос работает быстрее , так как полностью чтение идет из буфера. Стоит поменять user_entity_id и запрос растягивается на десятки секунд из-за чтения с диска. Как то можно все в буфер загнать или какой-то другой способ решения?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Nested Loop  (cost=246.83..105916.36 rows=431 width=325) (actual time=50.694..28348.201 rows=1365 loops=1)
   Buffers: shared hit=514783 read=33840
   I/O Timings: read=25783.602
   ->  Bitmap Heap Scan on user_message_entity ume  (cost=246.27..29349.37 rows=20671 width=46) (actual time=43.804..11569.066 rows=95182 loops=1)
         Recheck Cond: (user_entity_id = 2)
         Rows Removed by Index Recheck: 3484133
         Heap Blocks: exact=43145 lossy=28665
         Buffers: shared hit=56368 read=15706
         I/O Timings: read=10709.177
         ->  Bitmap Index Scan on user_message_entity_user_peer_idx  (cost=0.00..241.10 rows=20671 width=0) (actual time=31.045..31.045 rows=95182 loops=1)
               Index Cond: (user_entity_id = 2)
               Buffers: shared hit=264
   ->  Index Scan using message_entity_pkey on message_entity me  (cost=0.56..3.69 rows=1 width=279) (actual time=0.176..0.176 rows=0 loops=95182)
         Index Cond: (id = ume.message_entity_id)
         Filter: (to_tsvector('russian'::regconfig, message) @@ '''привет'''::tsquery)
         Rows Removed by Filter: 1
         Buffers: shared hit=458415 read=18134
         I/O Timings: read=15074.425
 Planning time: 0.933 ms
 Execution time: 28348.765 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 10:55
    #39688606
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Rum11,

1)поставить нормальные диски 25s на чтение 35000 буферов это очень грустно
и
2)поставить достаточно памяти чтобы основная рабочая часть базы там помещалась

я бы с 1 начинал.
Вопрос не к базе а к используемому оборудованию.
Поставьте Intel optane диск и будуту цифры в 10000 раз меньше (по времени с диском) :)).

PS: я бы еще work_mem бы поднял чтобы не было Heap Blocks: exact=43145 lossy=28665
но фундаментально это вам вопрос не решит.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 11:20
    #39688626
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Rum11,

попробовать кластеризовать по user_entity_id. периодически.
?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 11:29
    #39688637
Rum11
Rum11
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
qwwq,

Данные растут очень быстро. Придется постоянно делать кластеризацию , если я правильно Вас понял.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 11:43
    #39688653
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Rum11,

а есть ли полнотекстовый индекс по message и индекс по user_message_entity.message_entity_id ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 11:47
    #39688665
Rum11
Rum11
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Alexius,

Есть.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 11:51
    #39688669
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Rum11,

а что показывает такой запрос ?
Код: sql
1.
 explain (analyze, buffers) SELECT * FROM user_message_entity ume inner join message_entity me on me.id = ume.message_entity_id WHERE to_tsvector('russian', message) @@ to_tsquery('russian', 'привет') and user_entity_id+0 = 2;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 12:02
    #39688687
Rum11
Rum11
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Alexius,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
Hash Join  (cost=966626.01..3020560.22 rows=9020 width=325) (actual time=290974.776..314592.306 rows=1365 loops=1)
   Hash Cond: (ume.message_entity_id = me.id)
   Buffers: shared hit=348957 read=829516, temp read=47173 written=46919
   I/O Timings: read=34802.834
   ->  Seq Scan on user_message_entity ume  (cost=0.00..2013197.44 rows=432952 width=46) (actual time=6.309..21550.896 rows=95182 loops=1)
         Filter: ((user_entity_id + 0) = 2)
         Rows Removed by Filter: 86495275
         Buffers: shared hit=199188 read=515152
         I/O Timings: read=9284.150
   ->  Hash  (cost=924634.63..924634.63 rows=846430 width=279) (actual time=290954.017..290954.017 rows=859809 loops=1)
         Buckets: 16384  Batches: 128  Memory Usage: 3136kB
         Buffers: shared hit=149769 read=314364, temp written=46088
         I/O Timings: read=25518.684
         ->  Bitmap Heap Scan on message_entity me  (cost=7035.33..924634.63 rows=846430 width=279) (actual time=233.156..289431.548 rows=859809 loops=1)
               Recheck Cond: (to_tsvector('russian'::regconfig, message) @@ '''привет'''::tsquery)
               Rows Removed by Index Recheck: 14220793
               Heap Blocks: exact=34213 lossy=423716
               Buffers: shared hit=149769 read=314364
               I/O Timings: read=25518.684
               ->  Bitmap Index Scan on msgs_text_idx  (cost=0.00..6823.73 rows=846430 width=0) (actual time=224.986..224.986 rows=859809 loops=1)
                     Index Cond: (to_tsvector('russian'::regconfig, message) @@ '''привет'''::tsquery)
                     Buffers: shared hit=179
 Planning time: 0.472 ms
 Execution time: 314592.660 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 12:19
    #39688705
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Rum11,

ок, значит исходный план оптимальный был для запроса с такими параметрами.
к уже предложенным советам добавлю, что можно еще попробовать проверить bloat в таблицах и индексах и устранить его, если есть. возможно нужны не все поля из обоих таблиц и можно index only scan где-то сделать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 14:19
    #39688867
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
Rum11qwwq,

Данные растут очень быстро. Придется постоянно делать кластеризацию , если я правильно Вас понял.
можно ещё партицировать по (диапазонам) user_entity_id.
попаданий на странице будет много больше.


такоже можно сначала отфильтровать не * а только ид (если я правильно понимаю, данные не апдейтятся почти) иос-ом (включить 2 поля в индекс) + фильтром по полнотексту, а только потом подтянуть поля (если полнотекст рубит сильно то выиграть может получиться) первой таблички по цтидам.

и вообще уйти на мускул с его иот--кластерами (чисто теоретицки -- ибо не был, не привлекался)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 14:22
    #39688870
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос.
qwwq,

забыл, что цтиды иосом не достать. не срастется. надо еще сами ид-ники 1--й в индекс заинклюдить. третьим полем.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос. / 11 сообщений из 11, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 149ms
Закрыть
start [/forum/topic.php?fid=53&tablet=1&tid=1995626]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 43ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 14ms
total:  149ms
x
x
Закрыть


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