Почему нельзя использовать Index Only Scan для индекса, созданного с помощью COALESCE?

Question

Почему нельзя использовать Index Only Scan для индекса, созданного с помощью COALESCE?

PostgreSQL 9.4
Таблица создается следующим образом:

CREATE TABLE foo (

    id integer,

    date date,

    value numeric(14,3)

);

Я оптимизирую запрос, используя функцию окна ROW_NUMBER() и COALESCE. Для наиболее эффективного я склонен использовать Index Only Scan в следующем запросе:

SELECT id, c_val

FROM (

    SELECT id, COALESCE(value, 0) c_val, ROW_NUMBER() OVER(PARTITION BY id ORDER BY date DESC NULLS LAST) rn

    FROM foo) sbt

WHERE sbt.rn = 1;

Итак, если я создам индекс следующим образом:

CREATE INDEX ON foo (id, date DESC NULLS LAST, value);

Планировщик выбирает использовать Index Only Scan, но если я делаю это таким образом:

CREATE INDEX ON foo (id, date DESC NULLS LAST, COALESCE(value, 0));

Планировщик будет делать только Index Scan.

Почему? Я пытаюсь избежать затрат на оценку функции COALESCE во время выполнения запрос. Почему это не работает с Index Only Scan?

686 1

postgresql sql indexing internals

1 ответ:

Comments

Ничего не найдено.

Gabriel's Messanger · Accepted Answer · 2017-05-23 15:29:53

Я думаю, что вы ошибочно предположили, что COALESCE(value, 0) в вашем SELECT имеет значение с точки зрения использования индекса. По правде говоря, это только преобразование вида, выполненное после того, как возвращаются значения строк.

Что имеет значение для использования индекса, так это ваш WINDOW FUNCTION. Во-первых, вы разделяете по id, а во-вторых, вы упорядочиваете значения в каждом разделе по date DESC NULLS LAST. Эти две вещи определяют, что индекс, подобный CREATE INDEX ON foo (id, date DESC NULLS LAST, ...), полезен независимо от того, что вы ставите в следующие позиции. обратите внимание, что если вы измените порядок из id и date при создании индекса PostgreSQL вообще не будет использовать индекс.
Теперь вы должны знать, что INDEX ONLY SCAN можно использовать только в том случае, если сам индекс хранит все нетронутые значения строк, запрошенные запросом. После PostgreSQL manual :

Если индекс хранит исходные значения индексированных данных (а не их представление с потерями), полезно поддерживать сканирование только по индексу, при котором индекс возвращает фактические данные...

В вашем в случае, если ваш индекс seccond хранит некоторое представление с потерями строки, потому что значение последнего столбца преобразуется функцией и запрашивается запрос id, value и date. PostgreSQL не настолько умен, чтобы видеть, что это всего лишь замена NULLs на 0. Для него это не первоначальная ценность. Поэтому нам нужно получить доступ к таблице, чтобы получить исходные значения строк (в конце концов, используя plain INDEX SCAN). После этого значения форматируются для вывода и COALESCE(values, 0) происходит.

Редактировать:
Я думаю, что этого объяснения достаточно для вас в том, что касается вашего вопроса о внутренних органах. Говоря о стоимости оценки COALECE(), я согласен с a_horse_with_no_name, что вам, вероятно, не стоит беспокоиться об этом.