Существует таблица кодов, в ней в частности есть поля vendor_id, used и code_limit. Used обозначает, сколько раз выдавался код, code_limit - сколько максимум один код можно выдать, при "-1" бесконечно. vendor_id - это владелец кода (например, игра). В таблице имеется 13 млн записей, необходимо выдать первый неиспользованный код по определённому вендору. Собстсвенно, вот запрос:
1.
SELECT * FROM codes c WHERE c.vendor_id = 1 AND (c.used < c.code_limit OR limit < 0)
Индекс есть на vendor_id, однако вот, что показывает explain analyze:
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
Товарищ предложил сделать частичный индекс. Вот результат:
1.
CREATE INDEX idx_code_condition_1 ON codes (vendor_id) WHERE (used < code_limit OR code_limit < 0);
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. По нему либо индекс вобще работать не хочет, либо нужен какой-то очень хитрый индекс. Пробовал создать частичный индекс именно по этому условию:
1.
create index idx_test on codes (vendor_id) where used < code_limit;
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 запрос отрабатывает уже очень быстро:
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
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 в моём случае, а не только на последнего, который был в запросе?
