Что нового в PostgreSQL 11: INCLUDE-индексы

    image

    Релиз PostgreSQL 11 состоится еще не скоро, только в октябре. Но фичфриз уже наступил, а значит мы знаем, какие фичи попали в этот релиз, и можем их потестировать, собрав PostgreSQL из ветки master. Особого внимания заслуживает фича под названием INCLUDE-индексы. Патч изначально написан Анастасией Лубенниковой, а потом допилен Александром Коротковым и Федором Сигаевым. Протолкнуть его в PostgreSQL заняло «всего лишь» что-то около трех лет.

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

    create table test (k serial primary key, v text, ts timestamp);
    insert into test (v, ts) select 'key_' || s , now() from generate_series(1, 10000) as s;
    

    … и построим по ней обычный btree-индекс:

    create index on test (v);
    

    Взглянем на план выполнения следующего запроса:

    =# explain select v, ts from test where v > 'key_1337' and v < 'key_2337';
                                     QUERY PLAN                                  
    -----------------------------------------------------------------------------
     Bitmap Heap Scan on test  (cost=31.57..112.09 rows=1101 width=16)
       Recheck Cond: ((v > 'key_1337'::text) AND (v < 'key_2337'::text))
       ->  Bitmap Index Scan on test_v_idx  (cost=0.00..31.29 rows=1101 width=0)
             Index Cond: ((v > 'key_1337'::text) AND (v < 'key_2337'::text))
    (4 rows)
    

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

    Идея INCLUDE-индексов заключается в том, чтобы включить все необходимые для выполнения запроса данные прямо в индекс (но не индексировать их). Таким образом, запрос становится возможно выполнить за один index scan.

    Давайте проверим:

    drop index test_v_idx;
    create index on test (v) include (ts);
    explain select v, ts from test where v > 'key_1337' and v < 'key_2337';
    

    Результат:

     Index Only Scan using test_v_ts_idx on test  (cost=0.29..46.30 rows=1101 width=16)
       Index Cond: ((v > 'key_1337'::text) AND (v < 'key_2337'::text))
    (2 rows)
    

    За счет того, что теперь мы не ходим в таблицу, запрос должен работать быстрее. Стоит однако отметить, что на практике все зависит от ваших данных. Каждый случай уникален, поэтому я сознательно не привожу здесь каких-то синтетических бенчмарков. Может оказаться, что на ваших объемах данных index only scan с include-индексами работает так же быстро, как и в случае с обычными индексами. А то и вовсе накопленная статистика говорит PostgreSQL, что запрос быстрее сделать heap scan'ом. Такое может произойти, например, если селективность вашего запроса низка.

    Так или иначе, знать про эту возможность полезно, и я искренне рад, что она появится в PostgreSQL 11.

    Postgres Professional

    167,00

    Российский вендор PostgreSQL

    Поделиться публикацией

    Похожие публикации

    Комментарии 30
      0
      Огонь! :-)
        0
        Назовите по человечески «Покрывающие индексы».
          +1
          Мы говорим так. Но это спорное название, т.к. понятие «покрывающий индекс» определено по отношению к конкретному запросу. По сути, это индекс, которого достаточно для index-only scan в этом запросе. Не обязательно с INCLUDE. См use-the-index-luke.com/sql/clustering/index-only-scan-covering-index
            0
            Включающие индексы? //Если никакого названия пока нет, то не грех и придумать.
          +5
          Осталось немного:
          — table in memory
          — columnstore index
          :)
            +11

            Нужно наверное добавить, что де факто — эта штука "слизана" с INCLUDE-INDEX for SQL Server от мелко-мягких… (которая по моему появилась в MSSQL аж в 2005-й версии).


            Реально полезная штука…
            И тут по моему не совсем понятно описаны преимущества сего действа — т.е. грубо говоря для чего оно вообще имеет место.
            В общем, вот это вот:


            CREATE UNIQUE INDEX newidx ON sometab (c1, c2) INCLUDE (c3, c4);

            заменяет следующие два индекса (в старом виде):


            CREATE UNIQUE INDEX oldunqidx ON sometab (c1, c2);
            CREATE        INDEX oldcvridx ON sometab (c1, c2, c3, c4);

            без хранения редундантной "дубликации" для одинаковых значений.


            Т.е. прирост скорости тут скорее вторичен (например через меньшее вымывание кэша на соответственно меньшем индексе и за счет меньших потерь времени на перестроение его на CUD-операциях).


            Например, для этого примера (если все c1-c4 int):


              Name    | RowCnt  | Reserved | Data     | IndexSize | Unused
              ------- | ------- | -------- | -------- | --------- | ------
            - testold | 638976  | 49112 KB | 15832 KB | 33192 KB  | 88 KB
            + testnew | 638976  | 35472 KB | 15832 KB | 19560 KB  | 80 KB

            Про скорость оно всё сложно на самом деле, но если тупо в лоб при прочих равных (под паразитной нагрузкой) — как-то так:


            # 1M rows
            - testold:  365.057 µs/# 2740 # 2739.3 #/sec
            + testnew:  357.219 µs/# 2800 # 2799.4 #/sec
            # 10M rows
            - testold:  413.326 µs/# 2420 # 2419.4 #/sec
            + testnew:  367.916 µs/# 2719 # 2718.0 #/sec
            # 100M rows
            - testold:  696.943 µs/# 1435 # 1434.8 #/sec
            + testnew:  455.329 µs/# 2197 # 2196.2 #/sec

            Ну и при таком "сахаре" бонусом сверху возможность использовать короткий UNIQUE индекс по значениям c1, c2 при scan по короткому кортежу (как в этом примере и др. тому подобные вещи).

              0
              заменяет не 2 индекса, а 3, порядок полей в Include неважен, в этом вся и суть
              (c1, c2); (c1, c2, c3, c4); (c1, c2, c4, c3);
                0
                Что-то я не пойму никак зачем тут три индекса, если бы хватило одного, который накрывает все 4 поля?
                => create index on test(a,b,c,d);
                CREATE INDEX
                => explain select c,d from test where a=100 and b=1;
                                                    QUERY PLAN
                -----------------------------------------------------------------------
                 Index Only Scan using test_a_b_c_d_idx on test  (cost=0.42..8.45 rows=1 width=8)
                   Index Cond: ((a = 100) AND (b = 1))
                (2 rows)
                  0
                  Не надо забывать, что индекс по нескольким полям не во всех случаях поможет, а СУБД нагружает всегда.
                  В вашем примере всё работает хорошо, т.к. отбор идёт по первым двум полям. Если же потребуется отбор по полям a и c, индекс уже не поможет.
                    0
                    разве по «c» в INCLUDE можно будет искать? там видимо filter будет?
                    0

                    Заменяет два, потому что oldunqidx — UNIQUE и покрывает ключевые поля (и соответственно быстрее), кроме того играет дополнительно роль CONSTRAINT по кортежу (c1,c2);


                    Собрав же только один индекс oldcvridx (даже если и UNIQUE, но уже по 4-м полям), вы "теряете" обе эти возможности.

                      0
                      UNIQUE просмотрел, точно.
                        0
                        Так нет, непонятно всё же. По include индексированный поиск возможен разве? сортировка?
                          0
                          1. Да
                          2. Да, но смысл сомнителен при выборке по юник-ключам (c1, c2)
                      0
                      порядок полей в Include неважен

                      Ну уточните тогда, неважен для какого действия?
                      Где например ниже будет важен порядок, т.е. по вашему выходит следующие два запроса разные?


                      select c3, c4 from sometab where c1 = ?v1 and c2 = ?v2
                      select c4, c3 from sometab where c1 = ?v1 and c2 = ?v2
                        0
                        Порядок полей неважен, когда по include-полям требуется только получение данных, а отбор не производится.
                      0
                      Не совсем заменяет.
                      Подумайте как запрос вида
                       select .. from where c1 = var1 and c2 = var2 and c3 = var3 and c4 = var4

                      Будет вести себя с
                      CREATE        INDEX oldcvridx ON sometab (c1, c2, c3, c4);

                      и с
                      CREATE UNIQUE INDEX newidx ON sometab (c1, c2) INCLUDE (c3, c4);

                        0

                        Иии… что там должно чем отличатся?
                        Вы правда про запрос по одной row спрашиваете (я напомню кортеж (с1, с2) уникален).


                        Весь смысл в том что весь кортеж (с1, с2, c3, c4) находится в индексе, т.е. ему не нужно лезть в таблицу для проверки данных.


                        А вообще-то, даже следующее будет с INCLUDE-индексом если не быстрее, то за то-же самое время:


                        select ... where c1 = ?v1 and c2 >= ?v2fr and c2 <= ?v2to and c3 = ?v3 and c4 = ?v4

                        Собственно по той же причине.
                        По крайней мере для MSSQL проверялось мною не раз — чистый index-seek в обоих случаях.

                          0
                          Иии… что там должно чем отличатся?
                          Вы правда про запрос по одной row спрашиваете (я напомню кортеж (с1, с2) уникален).

                          Мм, в случае уникальности (с1, с2) пожалуй, скорей всего, вы правы. Тогда это не совсем понял ваш пример. Почему индекс по (с1, с2, c3, c4) в вашем примере не уникальный, если кортеж c1, c2 уникален?
                            0

                            Потому что только ключ (или ключи) включаются в индекс напрямую, а покрытие (nokeys) соответственно в INCLUDE.
                            Грубо причина описана выше.

                        0
                        А теперь сделайте то же сравнение, но с c3 и c4 — строковыми типами максимального размера.
                        MS SQL, например, даже не даст создать такой индекс, т.к. размер индексных колонок суммарно не должен превышать 900 байт, ЕМНИП. А вот в INCLUDE их вполне можно поместить, чтобы не делать KEY LOOKUP в основную таблицу на каждую найденную строку.
                        INCLUDE полезен именно для того, чтобы не растягивать без необходимости уровень дерева в индексе, оставив большие поля, которые используются в запросе, только на листовом уровне.
                        ЗЫ: с Postgre не работал, работаю с MS SQL, пишу по опыту использования таких индексов там.
                        +2
                        То есть если включить в индекс все поля исходной таблицы, то получится Index Organized Table, за тем исключением, что данные будут дублированы и в индексе, и в таблице?
                          +1
                          Индекс не очень-то приспособлен для таких вещей. Вот например, размер записи в btree ограничен тем, что на странице индекса их должно минимум три умещаться. Большие поля не пройдут, а toast в индексе нет.
                            +1
                            Ну то есть в очень ограниченном виде, но иногда можно и так использовать.
                          +1
                          Правильно ли я понимаю, что такой подход сводит на нет оптимизацию HOT-update в случае, если изменяется «включенное» поле? Так же не очень понятно на счёт работы MVCC. PostgreSQL в худшем случае все равно придётся сходить в таблицу, чтобы узнать xmin/xmax?
                            +2
                            Да, это так.
                            Как и для любого случая использования покрывающих индексов и стратегии index only scan.

                            Главная полезная особенность индексов с INCLUDE в том, что можно объединить уникальные индексы с покрывающими там, где они уже используются.
                            И ещё одна особенность заключается в том, что в INCLUDE можно добавить типы данных, для которых нет opclass, поскольку они не учитываются в сортировке.
                              0
                              Вот это действительно звучит круто! Особенно в случаях, когда индексы создаются только для поддержки ограничений целостности. Теперь они смогут выполнять ещё и «полезную» работу. Жаль, что это не отмечено в основном тексте статьи
                            0

                            Случайно наткнулся на данную статью, и стало интересно.


                            В чем отличие multicolumn index от include?

                              0
                              В том что, во многоколоночном индексе индексируются все поля, а в данном случае включённые поля не индексируются. Просто хранятся в индексе
                              +1
                              Ждём INCLUDE-джоинов в скором будущем и поддержку блокчейна.

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

                              Самое читаемое