Как ускорить SELECT с высокой вариативностью высокоселективных WHERE и сортировкой?
Есть табличка T с полями A, B, C и D.
Тип полей не так важен, главное, что к ним применимы опреатры сравнения больше/меньше (int,float,timestamp,...).
Запросы к этой таблицы имеют следующие вариации:
1) Всегда применяется сортировка (ASC или DESC) по одному любому полю из перечисленных.
2) Возможны ограничения min/max (как только min, только max, так и оба) для любых из перечисленных полей в любых комбинациях.
При определенном кол-ве строк всё работает медленно. В этом и проблема.
Что уже сделано:
1) Построены b-tree индексы по всем полям, что дало прекрасный результат в ускорении запросов где:
а) происходит только сортировка
б) вместе с сортировкой применяются низкоселективные ограничения
в) когда высокоселективное ограничение применяется к тому же полю, по которому идет сортировка
В принципе результат предсказуемый.
Наверное уже ясно когда возникают проблемы. А именно при высокоселективных ограничениях по полю, отличному от поля сортировки.
Селективность таких ограничений может быть и нулевой. Тогда вообще труба.
Проблема не кажется мне уникальной. Как с ней бороться?
Pavel Denisov, да. Как и ожидалось, Pg бежит по индексу поля сортировки (надеясь на низкую селективность ограничений?).
Limit (cost=0.43..43.52 rows=2 width=20)
-> Index Scan Backward using communities_post_published_at_index on communities_post (cost=0.43..779074.67 rows=36164 width=20)
Filter: (views > 10000000)
views > 10000000 - высокоселективный фильтр и высекает всего-то 64 записи из 4млн.
Хотя индекс по views тоже есть и конечно выгоднее было бы пробежать по нему. Но как видно Pg поступил иначе.
Думаю сделать частичные индексы:
create index "..._index" on T (col) where {expr};
где {expr} - высокоселективное условие по полю, отличному от col. Но я так задолбаюсь с:
1) Выяснением подходящего порога {expr}
2) В попытках не забыть все комбинации (col, {expr}) и на каждый построить индекс.
Здравый смысл, подсказывает, что Pg сам должен анализировать распределение по столбцам (хранить некоторые перцентили например) и из этой статы понимать когда бежать по индексу сортировки и применять фильтр условия, а когда по индексу условия и потом сортировать.
Оу, меня упоминают, неловко даже.
Вообще из пары тостер и sqlru я как раз отсюда, ещё на Q&A хабра джуниором мелькал, а на sql.ru заполз всего-то года два назад.
У меня, к сожалению, нет сегодня настроения играть в "psql over форум". К тому же на sqlru уже Максим показался.
Всем спасибо. Мне уже помогли.
Проблема в том, что дефолтное default_statistics_target=100 маловат для гистограмм по моей таблице с 4 млн строк. Поставил на 1000, пересчитал стату, и теперь pg строит планы разумнее.
Средний
