Индексы в PostgreSQL — 2


    Интерфейс


    В первой части мы говорили о том, что метод доступа должен предоставлять информацию о себе. Посмотрим, как устроен этот интерфейс.

    Свойства


    Все свойства методов доступа представлены в таблице pg_am (am — access method). Из этой таблицы можно получить и сам список доступных методов:

    postgres=# select amname from pg_am;
     amname
    --------
     btree
     hash
     gist
     gin
     spgist
     brin
    (6 rows)

    Хотя к методам доступа можно с полным правом отнести и последовательное сканирование, исторически сложилось так, что оно отсутствует в этом списке.

    В версиях PostgreSQL 9.5 и более старых каждое свойство было представлено отдельным полем таблицы pg_am. Начиная с версии 9.6 свойства опрашиваются специальными функциями и разделены на несколько уровней:

    • свойства метода доступа — pg_indexam_has_property,
    • свойства конкретного индекса — pg_index_has_property,
    • свойства отдельных столбцов индекса — pg_index_column_has_property.

    Разделение на уровни метода доступа и индекса сделано с прицелом на будущее: в настоящее время все индексы, созданные на основе одного метода доступа, всегда будут иметь одинаковые свойства.


    К свойствам метода доступа относятся следующие четыре (на примере btree):

    postgres=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
    from pg_am a,
    unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
    where a.amname = 'btree' order by a.amname;
     amname |     name      | pg_indexam_has_property
    --------+---------------+-------------------------
     btree  | can_order     | t
     btree  | can_unique    | t
     btree  | can_multi_col | t
     btree  | can_exclude   | t
    (4 rows)


    • can_order
      Метод доступа позволяет указать порядок сортировки значений при создании индекса (в настоящее время применимо только для btree);
    • can_unique
      Поддержка ограничения уникальности и первичного ключа (применимо только для btree);
    • can_multi_col
      Индекс может быть построен по нескольким столбцам;
    • can_exclude
      Поддержка ограничения исключения EXCLUDE.

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

    postgres=# select p.name, pg_index_has_property('t_a_idx'::regclass,p.name)
    from unnest(array['clusterable','index_scan','bitmap_scan','backward_scan']) p(name);
         name      | pg_index_has_property
    ---------------+-----------------------
     clusterable   | t
     index_scan    | t
     bitmap_scan   | t
     backward_scan | t
    (4 rows)


    • clusterable
      Возможность переупорядочивания строк таблицы в соответствии с данным индексом (кластеризация одноименной командой CLUSTER);
    • index_scan
      Поддержка индексного сканирования. Это свойство может показаться странным, однако не все индексы могут выдавать TID по одному — некоторые выдают все результаты сразу и поддерживают только сканирование битовой карты;
    • bitmap_scan
      Поддержка сканирования битовой карты;
    • backward_scan
      Выдача результата в порядке, обратном указанному при создании индекса.

    Наконец, свойства столбцов:

    postgres=# select p.name, pg_index_column_has_property('t_a_idx'::regclass,1,p.name)
    from unnest(array['asc','desc','nulls_first','nulls_last','orderable','distance_orderable','returnable','search_array','search_nulls']) p(name);
            name        | pg_index_column_has_property
    --------------------+------------------------------
     asc                | t
     desc               | f
     nulls_first        | f
     nulls_last         | t
     orderable          | t
     distance_orderable | f
     returnable         | t
     search_array       | t
     search_nulls       | t
    (9 rows)


    • asc, desc, nulls_first, nulls_last, orderable
      Эти свойства связаны с упорядочиванием значений (мы поговорим о них, когда дойдем до btree-индексов);
    • distance_orderable
      Выдача результата в порядке сортировки по операции (в настоящее время применимо только для индексов gist и rum);
    • returnable
      Возможность использования индекса без обращения к таблице, то есть поддержка исключительно индексного доступа;
    • search_array
      Поддержка поиска нескольких значений для конструкции «индексированное-поле IN (список_констант)» или, что то же самое, «индексированное-поле = ANY(массив_констант)»;
    • search_nulls
      Возможность поиска по условиям is null и is not null.

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

    Классы и семейства операторов


    Помимо набора «умений» надо также знать, с какими типами данных и с какими операторами работает метод доступа. Для этого в PostgreSQL есть понятия класса операторов и семейства операторов.

    Класс операторов содержит минимальный набор операторов (и, возможно, вспомогательных функций) для работы индекса с некоторым типом данных.

    Класс всегда входит в какое-либо семейство операторов. При этом в одно общее семейство могут входить несколько классов, если они имеют одинаковую семантику. Например, семейство integer_ops включает классы int8_ops, int4_ops и int2_ops для разных по размеру, но одинаковых по смыслу типов bigint, integer и smallint:

    postgres=# select opfname, opcname, opcintype::regtype
    from pg_opclass opc, pg_opfamily opf
    where opf.opfname = 'integer_ops'
    and opc.opcfamily = opf.oid
    and opf.opfmethod = (select oid from pg_am where amname = 'btree');
       opfname   | opcname  | opcintype
    -------------+----------+-----------
     integer_ops | int2_ops | smallint
     integer_ops | int4_ops | integer
     integer_ops | int8_ops | bigint
    (3 rows)

    Другой пример: в семейство datetime_ops входят классы операторов для работы с датами (как без времени, так и со временем):

    postgres=# select opfname, opcname, opcintype::regtype
    from pg_opclass opc, pg_opfamily opf
    where opf.opfname = 'datetime_ops'
    and opc.opcfamily = opf.oid
    and opf.opfmethod = (select oid from pg_am where amname = 'btree');
       opfname    |     opcname     |          opcintype          
    --------------+-----------------+-----------------------------
     datetime_ops | date_ops        | date
     datetime_ops | timestamptz_ops | timestamp with time zone
     datetime_ops | timestamp_ops   | timestamp without time zone
    (3 rows)

    Семейство может также включать дополнительные операторы для сравнения значений разных типов. За счет группировки в семейство планировщик может использовать индекс для предикатов со значениями разных типов. Также семейство может содержать и другие вспомогательные функции.

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

    Однако можно указать класс операторов явно. Простой пример, когда это необходимо: в базе данных с правилом сортировки, отличным от C, обычный индекс по текстовому полю не поддерживает операцию LIKE:

    postgres=# show lc_collate;
     lc_collate
    -------------
     en_US.UTF-8
    (1 row)
    postgres=# explain (costs off) select * from t where b like 'A%';
             QUERY PLAN          
    -----------------------------
     Seq Scan on t
       Filter: (b ~~ 'A%'::text)
    (2 rows)

    Это ограничение можно преодолеть, создав индекс с классом операторов text_pattern_ops (обратите внимание, как изменилось условие в плане):

    postgres=# create index on t(b text_pattern_ops);
    CREATE INDEX
    postgres=# explain (costs off) select * from t where b like 'A%';
                               QUERY PLAN                          
    ----------------------------------------------------------------
     Bitmap Heap Scan on t
       Filter: (b ~~ 'A%'::text)
       ->  Bitmap Index Scan on t_b_idx1
             Index Cond: ((b ~>=~ 'A'::text) AND (b ~<~ 'B'::text))
    (4 rows)

    Системный каталог


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



    Все эти таблицы, разумеется, подробно описаны.

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

    postgres=# select opcname, opcintype::regtype
    from pg_opclass
    where opcmethod = (select oid from pg_am where amname = 'btree')
    order by opcintype::regtype::text;
           opcname       |          opcintype          
    ---------------------+-----------------------------
     abstime_ops         | abstime
     array_ops           | anyarray
     enum_ops            | anyenum
    ...

    Какие операторы входят в класс (и, следовательно, индекс может использоваться для доступа по условию, включающему такой оператор)?

    postgres=# select amop.amopopr::regoperator
    from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop
    where opc.opcname = 'array_ops'
    and opf.oid = opc.opcfamily
    and am.oid = opf.opfmethod
    and amop.amopfamily = opc.opcfamily
    and am.amname = 'btree'
    and amop.amoplefttype = opc.opcintype;
            amopopr        
    -----------------------
     <(anyarray,anyarray)
     <=(anyarray,anyarray)
     =(anyarray,anyarray)
     >=(anyarray,anyarray)
     >(anyarray,anyarray)
    (5 rows)

    Продолжение.
    Метки:
    Postgres Professional 151,64
    Российский вендор PostgreSQL
    Поделиться публикацией
    Комментарии 0

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

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