Сканирование индекса для сравнения нескольких столбцов — неоднородный порядок столбцов индекса
Этот вопрос тесно связан с Принудительное сканирование индекса для сравнения нескольких столбцов
Решение идеальное, но работает, только если все столбцы индекса имеют порядок такой же. Этот вопрос отличается, потому что столбец b здесь описание, и этот факт не позволяет использовать синтаксис строки для решения той же проблемы. Вот почему я ищу другое решение.
Предположим, индекс построен для 3 столбцов (a asc, b DESC, c asc), я хочу, чтобы Postgres:
- найти ключ [a=10, b=20, c=30] в этом B-дереве,
- отсканировать следующие 10 записей и вернуть их.
Если индекс имеет только один столбец, решение очевидно:
select * from table1 where a >= 10 order by a limit 10
Но если столбцов больше, решение становится намного сложнее. Для 3 столбцов:
select * from table1
where a > 10 or (a = 10 and (b < 20 or b = 20 and c <= 30))
order by a, b DESC, c
limit 10;
Как я могу сказать Postgres, что мне нужна эта операция?
И могу ли я быть уверен, что даже для этих сложных запросов для 2+ столбцов оптимизатор всегда будет понимать, что он должен выполнять сканирование диапазона? Почему?
Вы пробовали, когда вы выбираете a, а не *?
Ответы 2
PostgreSQL очень тщательно реализует кортежи (в отличие от половинчатых реализаций, встречающихся в Oracle, DB2, SQL Server и т. д.). Вы можете написать свое условие, используя «неравенство кортежей», например:
select *
from table1
where (a, -b, c) >= (10, -20, 30)
order by a, -b, c
limit 10
Обратите внимание, что поскольку второй столбец находится в порядке убывания, вы должны «инвертировать» его значение при сравнении. Вот почему это выражается как -b, а также -20. Это может быть сложно для нечисловых столбцов, таких как даты, varchars, большие объекты и т. д.
Наконец, использование индекса по-прежнему возможно со значением столбца -b, если вы создаете специальный индекс, например:
create index ix1 on table1 (a, (-b), c);
Однако вы никогда не сможете заставить PostgreSQL использовать индекс. SQL — это декларативный язык, а не императивный. Вы можете сделать это соблазнить, обновив статистику таблицы, а также выбрав небольшое количество строк. Если ваш LIMIT слишком велик, PostgreSQL может вместо этого использовать полное сканирование таблицы.
Это все интересно. Но мы все еще не можем использовать данный индекс (a ASC, b DESC, c ASC) для запроса полностью. Postgres считывает все кортежи индексов с a >= 10 и фильтры остальных по всему значению ROW. Индекс только по (a) обычно работает лучше — за заметным исключением сканирования только индекса, где это возможно. И только для типов данных немного определен отрицатель -. text, например, нет.
Я согласен, я не понимаю, как вы можете использовать индекс (a, b DESC, c). Расширение вашего второго запроса с помощью равенств и неравенств - единственное решение, которое я вижу для использования этого индекса. Но да, чем больше столбцов вы добавляете, тем громоздче становится.
Ваш обходной путь с индексом выражения на (a, (-b), c) может в конце концов работать полноценно — если вы адаптируете ORDER BY к соответствию также: order by a, -b, c (не order by a, b desc, c). Тогда мы получим сканирование индекса без дополнительного FILTER шага.
Насколько я понимаю, SQL Server вообще не реализует кортежи.
@GordonLinoff Должным образом отмечено.
Строго говоря, ваш индекс на (a ASC, b DESC, c ASC) все еще можно использовать, но только на основе ведущее выражение a. Видеть:
Его полезность ограничена, и Postgres будет использовать его только в том случае, если предикат только для a достаточно избирательен (примерно менее 5% всех строк имеют a >= 10). (Или, возможно, чтобы извлечь выгоду из сканирования только индекса, где это возможно.) Но все кортежи индекса, соответствующие только a, должны быть прочитаны, и вы увидите шаг FILTER в плане запроса для отбрасывания не соответствующих требованиям строк — оба добавляют дополнительные затраты. Индекс только на (a) обычно работает лучше, поскольку он меньше и дешевле в обслуживании.
В прошлом я пытался и не смог в полной мере использовать индекс с неоднородным порядком сортировки (ASC | DESC), как вы показываете для сравнения значений ROW. Я почти уверен, что это невозможно. Подумайте об этом: Postgres сравнивает значения целых строк, которые могут быть больше или меньше, но не оба значения одновременно.
Есть обходные пути для типов данных с определенным отрицателем (например, - для числовых типов данных). Смотрите решение от "The Impaler"! Хитрость заключается в том, чтобы инвертировать значения и обернуть их в индекс выражения, чтобы в конце концов получить единый порядок сортировки для всех выражений индекса — что в настоящее время является единственным способом задействовать весь потенциал сравнения строк. Убедитесь, что обеWHERE условия и ORDER BY соответствуют специальному индексу.
Да, я обсуждал, как чередовать <= и >= для каждого столбца для сравнения кортежей, но SQL недостаточно выразителен (пока?), чтобы сделать это, афаик. +1
Очень хороший вопрос +1.