Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос с большим количеством записей в таблице? / 10 сообщений из 10, страница 1 из 1
14.06.2018, 18:37
    #39660927
Малахов Дмитрий
Малахов Дмитрий
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Существует таблица кодов, в ней в частности есть поля vendor_id, used и code_limit. Used обозначает, сколько раз выдавался код, code_limit - сколько максимум один код можно выдать, при "-1" бесконечно. vendor_id - это владелец кода (например, игра). В таблице имеется 13 млн записей, необходимо выдать первый неиспользованный код по определённому вендору. Собстсвенно, вот запрос:
Код: sql
1.
SELECT * FROM codes c WHERE c.vendor_id = 1 AND (c.used < c.code_limit OR limit < 0)

Индекс есть на vendor_id, однако вот, что показывает explain analyze:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
explain (analyze, buffers) select * from codes c where (c.used < c.code_limit OR c.code_limit < 0) and c.vendor_id = 1 limit 1;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=13) (actual time=525.050..525.051 rows=1 loops=1)
   Buffers: shared hit=245 read=35166
   ->  Seq Scan on codes c  (cost=0.00..359373.97 rows=983846 width=13) (actual time=525.048..525.048 rows=1 loops=1)
         Filter: ((vendor_id = 1) AND ((used < code_limit) OR (code_limit < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=245 read=35166
 Planning time: 0.163 ms
 Execution time: 525.077 ms

Во-первых, не используется индекс, во-вторых, это очень долго для казалось бы простого запроса.

Ок, делаю индексы (vendor_id, used, code_limit), (vendor_id, code_limit, used) - не прокатывает. Перепробовал все шесть вариантов индексов по этим трём полям - результат ноль.

Отписался здесь https://dba.stackexchange.com/questions/209521/how-to-involve-table-index-in-filtering-of-3-conditions?noredirect=1#comment410644_209521
Товарищ предложил сделать частичный индекс. Вот результат:

Код: sql
1.
CREATE INDEX idx_code_condition_1 ON codes (vendor_id) WHERE (used < code_limit OR code_limit < 0);


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
explain (analyze, buffers) select * from codes c where mt.vendor_id = 1 and (mt.used < mt.code_limit OR mt.code_limit < 0) limit 1;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.37 rows=1 width=37) (actual time=492.787..492.787 rows=1 loops=1)
   Buffers: shared hit=277 read=35134
   ->  Seq Scan on codes c  (cost=0.00..359373.97 rows=983846 width=37) (actual time=492.785..492.785 rows=1 loops=1)
         Filter: ((vendor_id = 1) AND ((used < code_limit) OR (code_limit < 0)))
         Rows Removed by Filter: 4006175
         Buffers: shared hit=277 read=35134
 Planning time: 0.110 ms
 Execution time: 492.806 ms


И близко не индекс.

Интуитивно предполагаю, что слабое место это used < limit. По нему либо индекс вобще работать не хочет, либо нужен какой-то очень хитрый индекс. Пробовал создать частичный индекс именно по этому условию:

Код: sql
1.
create index idx_test on codes (vendor_id) where used < code_limit;


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
explain analyze select * from codes WHERE used < code_limit and vendor_id = 1 limit 1;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.32 rows=1 width=37) (actual time=188.266..188.266 rows=1 loops=1)
   ->  Seq Scan on codes  (cost=0.00..324889.95 rows=1021148 width=37) (actual time=188.265..188.265 rows=1 loops=1)
         Filter: ((used < code_limit) AND (vendor_id = 10))
         Rows Removed by Filter: 1006286
 Planning time: 0.107 ms
 Execution time: 188.286 ms



Тоже не использует индекс. насколько я понимаю, индексы хорошо работают с конкретными значениями, но проиндекировать отношение полей что-то не очень получается.
Заметил щё вот такую вещ. Второй раз по тому же vendor_id запрос отрабатывает уже очень быстро:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
explain analyze select * from codes WHERE used < code_limit and vendor_id = 1 limit 1;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.48 rows=1 width=37) (actual time=1691.049..1691.050 rows=1 loops=1)
   ->  Seq Scan on codes  (cost=0.00..324889.95 rows=683607 width=37) (actual time=1691.047..1691.047 rows=1 loops=1)
         Filter: ((used < code_limit) AND (vendor_id = 36))
         Rows Removed by Filter: 9711408
 Planning time: 0.200 ms
 Execution time: 1691.084 ms


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
explain analyze select * from codes WHERE used < code_limit and vendor_id = 1 limit 1;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.48 rows=1 width=37) (actual time=0.324..0.325 rows=1 loops=1)
   ->  Seq Scan on codes  (cost=0.00..324889.95 rows=683607 width=37) (actual time=0.323..0.323 rows=1 loops=1)
         Filter: ((used < code_limit) AND (vendor_id = 36))
         Rows Removed by Filter: 658
 Planning time: 0.200 ms
 Execution time: 0.359 ms



Однако, если чередовать vendor_id (1 -> 2 -> 1), то все запросы будут медленными.

Собственно, вопрос один: как мне оптимизировать этот запрос?
Ну и сопуствующие: существует ли индекс, который задействуется, если в условиии есть отношение полей, а не сравнения с константами?
Почему при повторном запросе по тем же самым параметрам, запрос выполняется быстро? Если это кеширование, то по каким ключевым словам это гуглить? И как настроить такое кеширование на все значения vendor_id в моём случае, а не только на последнего, который был в запросе?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 18:43
    #39660928
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Малахов Дмитрий,

если размер таблицы сильно больше доступной под кэш памяти, то как ни крути - всю её в "голову" севера не вложишь. Будут выталкивания из кэша, будут физические чтения. Индексы полезны, если они селективны. Когда из таблицы выбирается, условно говоря, пол-таблицы (или пятая часть - как в вашем случае), то доступ по индексу будут заведомо хуже, чем полный скан таблицы (тут накладывается оверхед, связанный со спецификой доступа к данным по индексу).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 18:45
    #39660929
Ути Пути
Ути Пути
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Малахов Дмитрий,

а вы уверены, что вам нужен select * from, а не какое-то конкретное поле типа code?

Вот и сделайте индекс включающий нужное поле в селекте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 18:58
    #39660935
Уткъ
Уткъ
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Ути ПутиМалахов Дмитрий,

а вы уверены, что вам нужен select * from, а не какое-то конкретное поле типа code?

Вот и сделайте индекс включающий нужное поле в селекте.

include вроде в 11-й версии только появился..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 19:15
    #39660940
Малахов Дмитрий
Малахов Дмитрий
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Щукина Анна,

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

мы специально взяли достаточно ёмкий сервак со 120 Гб оперативы, так что память вобще не проблема. Другое дело, что я пробовал игратся с параметрами кеширования, а именно shared_buffers стаивл в 10Гб, temp_buffers в 512 Мб (хотя, они вроде не причём тут), work_mem в 2 Гб, effective_cache_size 10Гб ставил. На этих параметрах пробовал выбирать vendor_id, у которого кодов не больше 1000. И всё равно, кешируется только последнее обращение. Вот почему я и спрашиваю про кеширование, я походу его просто не понимаю и надеюсь, что если пойму, оно мне поможет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 19:25
    #39660943
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Малахов Дмитрий,

то есть, из 120 Гиг памяти на борту под буферный кэш PG выделили только 10 Гиг? Зачем тогда остальное? Если мне память не изменяет, то при более-менее больших значениях ОЗУ под буферный кэш рекомендуется выделять от 25% до 40%.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 19:43
    #39660948
Малахов Дмитрий
Малахов Дмитрий
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Щукина Анна,
тут три причины:
- память нужна не только постгресу, но щё и редису, монге и нескольким другим сервисам. Взяли с запасом, чтобы не жалко было выделять;
- это было просто тестовое значение, минимальное, которое в голову пришло;
- я тестил на более слабом серваке. Если бы 10 Гб себя оправдали, на бою уже было бы все 60. 2000 записей для двух вендоров в 10 Гб явно должны были со свистом влезть, но не прокатило.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 20:52
    #39660965
ursido
ursido
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
Малахов Дмитрий- память нужна не только постгресу, но щё и редису, монге и нескольким другим сервисам. ...

Вот и попробуйте решить задачу теми сервисами. Чтобы задарма ресурсы не жрали.

По поводу запроса:

Создайте индекс (vedor_id, <primary_key>). В самом запросе обязательно укажите ORDER BY <primary_key> (и далее ограничение limit)

Результат отпишите.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.06.2018, 21:17
    #39660968
Ути Пути
Ути Пути
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
УткъУти ПутиМалахов Дмитрий,

а вы уверены, что вам нужен select * from, а не какое-то конкретное поле типа code?

Вот и сделайте индекс включающий нужное поле в селекте.

include вроде в 11-й версии только появился..
Без инклуде, просто последним полем в индексе. В оракле тоже инклуде нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.06.2018, 01:13
    #39661007
Малахов Дмитрий
Малахов Дмитрий
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос с большим количеством записей в таблице?
ursido,
авторВот и попробуйте решить задачу теми сервисами. Чтобы задарма ресурсы не жрали.
Такие идеи тоже рассматриваются, однако для этого не две строчки кода надо будет написать. Да и хотелось бы запрос делать там, де данные лежат, а не писать костыли просто потому что много ресурсов подъехало. Не очень верится, что субд не умеет в производительность.

авторСоздайте индекс (vedor_id, <primary_key>). В самом запросе обязательно укажите ORDER BY <primary_key> (и далее ограничение limit)
Рабочий вариант! Спасибо.

https://dba.stackexchange.com/questions/209521/how-to-involve-table-index-in-filtering-of-3-conditions - здесь мне тоже отписали рабочий вариант:
Код: sql
1.
CREATE INDEX idx__get_fast_code ON codes (ABS(vendor_id)) WHERE (used < code_limit OR code_limit < 0);


И затем при выборке тоже использовать abs:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
explain analyze select * from codes WHERE ((used < code_limit) OR (code_limit < 0)) AND ABS(vendor_id) = 36 limit 1;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..4.11 rows=1 width=37) (actual time=0.054..0.054 rows=1 loops=1)
   ->  Index Scan using idx__get_fast_code on codes  (cost=0.43..84217.08 rows=22894 width=37) (actual time=0.052..0.052 rows=1 loops=1)
         Index Cond: (abs(vendor_id) = 36)
 Planning time: 0.183 ms
 Execution time: 0.086 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос с большим количеством записей в таблице? / 10 сообщений из 10, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 415ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1995726]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 286ms
total:  415ms
x
x
Закрыть


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