На обучающих занятиях по PostgreSQL, и на продвинутом, и на базовом курсах, я часто сталкиваюсь с тем фактом, что обучающиеся практически ничего не знают о том, насколько мощными могут быть индексы по выражениям (если они вообще знают об их существовании). Так что позвольте мне сделать для Вас небольшой обзор.

Предположим, мы имеем таблицу, с диапазоном timestamp'ов (да, у нас имеется функция generate_series, которая может генерировать даты):

CREATE TABLE t AS
SELECT d, repeat(md5(d::text), 10) AS padding
  FROM generate_series(timestamp '1900-01-01',
                       timestamp '2100-01-01',
                       interval '1 day') s(d);
VACUUM ANALYZE t;

Также таблица содержит в себе столбец padding, чтобы она была немного побольше. Теперь, давайте выполним простой запрос по диапазону, возвращая только один месяц из приблизительно 200 лет, имеющихся в таблице. Если выполнить этот запрос с explain'ом, то получится приблизительно следующее:

EXPLAIN SELECT * FROM t WHERE d BETWEEN '2001-01-01' AND '2001-02-01';

                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..4416.75 rows=32 width=332)
   Filter: ((d >= '2001-01-01 00:00:00'::timestamp without time zone)
        AND (d <= '2001-02-01 00:00:00'::timestamp without time zone))
(2 rows)

и на моем компьютере, запрос выполняется приблизительно 20 миллисекунд. Неплохо, учитывая тот факт, что ему необходимо пройти по всей таблице, состоящей из 75 тысяч строк.

Но давайте создадим индекс на колонке с timestamp'ом (все индексы в данном тексте базовые, т.е. btree, если не заданы явно):

CREATE INDEX idx_t_d ON t (d);

И теперь давайте попробуем выполнить запрос снова:

                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using idx_t_d on t  (cost=0.29..9.97 rows=34 width=332)
   Index Cond: ((d >= '2001-01-01 00:00:00'::timestamp without time zone)
            AND (d <= '2001-02-01 00:00:00'::timestamp without time zone))
(2 rows)

Теперь он выполняется за 0.5 миллисекунды, что, грубо говоря, в 40 раз быстрее. Но это был, конечно же, простой индекс, созданный непосредственно на колонке, не индекс по выражению. Так что давайте предположим, что вместо прошлого запроса, мы будем запрашивать данные для каждого первого дня каждого месяца, выполняя следующий запрос:

SELECT * FROM t WHERE EXTRACT(day FROM d) = 1;

который, однако, не может использовать индекс, так как ему нужно выполнять выражение на колонке, в то время как индекс построен на самой колонке, что и показано с помощью EXPLAIN ANALYZE:

                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..4416.75 rows=365 width=332)
                (actual time=0.045..40.601 rows=2401 loops=1)
   Filter: (date_part('day'::text, d) = '1'::double precision)
   Rows Removed by Filter: 70649
 Planning time: 0.209 ms
 Execution time: 43.018 ms
(5 rows)

Так что он не только должен выполнить sequential scan, но и подсчитать количество, увеличивая время запроса до 43 миллисекунд.

База данных не может использовать индексы по нескольким причинам. Индексы (как минимум btree) основываются на запросах к отсортированным данным, предоставленным древовидной структурой, и, если первый запрос по диапазону от этого выигрывает, то второй (с вызовом extract) — нет.

На заметку: Другая проблема заключается в том, что набор операторов, поддерживаемых индексами (т.е. которые могут быть выполнены непосредственно на самом индексе) очень ограничен. И функция extract не поддерживается, поэтому запрос не может обойти проблему сортировки используя Bitmap Index Scan.

Теоретически, база данных может попробовать преобразовать условие в набор условий, но это крайне сложно и специфично для каждого выражения. В данном случае, нам придется генерировать бесконечное количество таких диапазонов «за день», потому что планировщик на деле не знает минимальный/максимальный timestamp'ы в таблице. Так что база даже и не будет пробовать.

Но пока база данных не знает как преобразовать условие, разработчики, как правило, знают. К примеру условие вида:

(column + 1) >= 1000

вовсе нетрудно переписать следующим образом:

column >= (1000 - 1)

и оно уже будет нормально работать с индексами.

Но что, если такое преобразование невозможно, как в случае с нашим запросом:

SELECT * FROM t WHERE EXTRACT(day FROM d) = 1;

В данном случае разработчику придется столкнуться все с той же проблемой неизвестных минимального/максимального значений для колонки d, и даже тогда ему придется генерировать множество диапазонов.

Что ж, эта статья об индексах по выражениям, а мы использовали только обычные индексы до этого момента, построенные непосредственно на колонке. Давайте создадим первый индекс по выражению:

CREATE INDEX idx_t_expr ON t ((extract(day FROM d)));
ANALYZE t;

в результате выполнения которого мы получим следующий план:

                               QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=47.35..3305.25 rows=2459 width=332)
                        (actual time=2.400..12.539 rows=2401 loops=1)
   Recheck Cond: (date_part('day'::text, d) = '1'::double precision)
   Heap Blocks: exact=2401
   ->  Bitmap Index Scan on idx_t_expr  (cost=0.00..46.73 rows=2459 width=0)
                                (actual time=1.243..1.243 rows=2401 loops=1)
         Index Cond: (date_part('day'::text, d) = '1'::double precision)
 Planning time: 0.374 ms
 Execution time: 17.136 ms
(7 rows)

Пока что он не дает того же прироста в скорости в 40 раз, как индекс из первого примера, это и ожидаемо, т.к. этот запрос возвращает намного больше кортежей (2401 против 32). Более того, они рассредоточены по всей таблице и не настолько локализованы, как в первом примере. Так что это неплохое ускорение в 2 раза, и во многих ситуациях из реальной жизни, вы увидите намного больший прирост.

Но возможность использовать индексы для условий со сложными выражениями в основе — это не самая интересная информация в данной статье, это причина, по которой люди создают индексы по выражениям. Но это не единственное преимущество.

Если посмотреть на два плана выполнения запросов ниже (без и с индексом по выражению), можно заметить следующее:

                               QUERY PLAN
------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..4416.75 rows=365 width=332)
                (actual time=0.045..40.601 rows=2401 loops=1)
 ...

                               QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=47.35..3305.25 rows=2459 width=332)
                        (actual time=2.400..12.539 rows=2401 loops=1)
 ...

Верно — создание индекса по выражению значительно улучшило оценки. Без индекса мы имеем только статистику (MCV + гистограмма) для грубых столбцов таблицы, и база не знает как оценить выражение

EXTRACT(day FROM d) = 1

Соответственно она применяет сравнение на равенство по-умолчанию, которое возвращает приблизительно 0.5% всех строк — так как таблица имеет 73050 строк, мы получаем оценку только 365 строк. Часто можно увидеть гораздо большие ошибки оценки в реальных приложениях.

С индексом, тем не менее, база данных также собрала статистику по колонкам индекса и, в данном случае, колонка содержит в себе результат выражения. В процессе планирования, оптимизатор обращает на это внимание и выдает гораздо лучшую оценку.

Это большое преимущество, которое может помочь разобраться с проблемами плохих планов выполнения запросов, вызванных неточными оценками. Тем не менее, большинство людей не знает о таком удобном инструменте.

И полезность этого инструмента только возросла с представлением JSONB типа данных в версии 9.4, потому что это практически единственный способ собрать статистику о содержимом JSONB документов.

При использовании JSONB документов, существует две базовых стратегии индексирования. Вы можете создать GIN/GIST индекс по всему документу, например следующим образом:

CREATE INDEX ON t USING GIN (jsonb_column);

который позволяет выполнять запросы к произвольным частям JSONB поля, использовать оператор содержания для сравнения поддокументов, и т.д. Это прекрасно, но у Вас все-равно имеется только базовая статистика по колонке, которая не очень удобна, так как документы обслуживаются как скалярные величины (и ни одна не совпадает с целым документом, или не использует диапазон документов).

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

CREATE INDEX ON t ((jsonb_column->'id'));

будут полезны только для конкретного выражения, для данного конкретного примера:

SELECT * FROM t WHERE jsonb_column ->> 'id' = 123;

но не для запросов, направленных к другим JSON ключам, например, value:

SELECT * FROM t WHERE jsonb_column ->> 'value' = 'xxxx';

Это не означает, что GIN/GIST индексы бесполезны на целом документе, но Вам придется выбирать. Либо Вы создадите направленный на конкретное выражение индекс, полезный, когда запрос идет к конкретному ключу и с дополнительным преимуществом статистических данных о выражении. Или Вы создаете GIN/GIST индекс на всем документе, способный справляться с запросами к произвольным ключам, но без статистики.

Тем не менее, Вы можете убить обоих зайцев, в данном случае, потому что Вы можете создать оба индекса в одно и то же время, а сама база данных будет выбирать какой из них использовать для каких запросов. И у Вас будет точная статистика, благодаря индексом по выражениям.

К сожалению, индексы по выражению и GIN/GIST индексы используют разные условия:

-- expression (btree)
SELECT * FROM t WHERE jsonb_column ->> 'id' = 123;

-- GIN/GiST
SELECT * FROM t WHERE jsonb_column @> '{"id" : 123}';

так что планировщик не может их использовать в одно и то же время — индексы по выражению для оценки и GIN/GIST индексы для выполнения.