Перейти к основному содержимому

btree_gin. Показательные классы операторов GIN

В исходном дистрибутиве установлено по умолчанию: нет.

Связанные компоненты: отсутствуют.

Схема размещения: ext.

Модуль предоставляет показательные классы операторов GIN, которые реализуют поведение, подобное поведению обычных классов b-tree, для следующих типов данных:

  • int2;
  • int4;
  • int8;
  • float4;
  • float8;
  • timestamp with time zone;
  • timestamp without time zone;
  • time with time zone;
  • time without time zone;
  • date;
  • interval;
  • oid;
  • money;
  • char;
  • varchar;
  • text;
  • bytea;
  • bit;
  • varbit;
  • macaddr;
  • macaddr8;
  • inet;
  • cidr;
  • uuid;
  • name;
  • bool;
  • bpchar;
  • все типы перечислений (enum).

Классы расширения btree_gin можно применять для тестирования операторов GIN или взять их за основу для разработки других операторов. Для запросов, где проверяются столбцы с индексом GIN и индексом-b-tree, может быть эффективней реализовать составной индекс GIN, а не использовать два отдельных индекса.

Описание​

Работа с индексами GIN​

GIN (Generalized Inverted Index, обратный индекс) – используется преимущественно для полнотекстового поиска.

Для примера, создайте таблицу:

CREATE TABLE users ( first_name text, last_name text );

Заполните ее случайными строками:

INSERT INTO users SELECT md5(random()::text), md5(random()::text) FROM (SELECT * FROM generate_series(1,1000000) AS id) AS x;

Поиск по строке может занимать длительное время:

SELECT count(*) FROM users WHERE first_name ilike '%aeb%';

Для ускорения поиска используйте GIN индекс:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX users_search_idx ON users USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops);

где gin_trgm_ops из расширения pg_trgm – указание Pangolin использовать триграммы для построения индекса.

Применение GIN на практике не ограничивается использованием расширения pg_trgm. В данном случае оно выбрано в целях упрощения примера.

Проверьте, что время выполнения запроса уменьшается:

SELECT count(*) FROM users where first_name ilike '%aeb%';

Описание btree_gin​

btree_gin добавляет возможность использовать индекс GIN с операциями, характерными для b-tree.

Допустим, в таблицу добавилось дополнительное поле с возрастом:

ALTER TABLE users ADD age INTEGER DEFAULT 0;

Если необходимо фильтровать пользователей по возрасту, то использование существующего индекса GIN может оказаться эффективней введения дополнительного BTREE-индекса:

CREATE EXTENSION btree_gin;
DROP INDEX user_search_index;
CREATE INDEX users_search_idx ON users USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops, age);

Теперь можно эффективно фильтровать по возрасту:

EXPLAIN SELECT count(*) FROM users where first_name ilike '%aeb%' and age >= 27;

Так же как и btree_gist, btree_gin работает менее эффективно, чем простой b-tree. Его разумно использовать, если индекс уже включает в себя объекты операции GIN (как в примере выше).

Доработка​

Не проводилась.

Ограничения​

Существуют следующие ограничения:

  • классы операторов GIN не будут работать быстрее аналогичных стандартных методов индекса-B-tree;
  • нет возможности ограничивать уникальность.

Установка​

Расширение входит в стандартную поставку Pangolin. Для использования расширения необходимо выполнить команду:

CREATE EXTENSION btree_gin SCHEMA ext;

Настройка​

Не требуется.

Использование модуля​

Использование btree_gin вместо btree:

CREATE TABLE test (a int4);
-- создать индекс
CREATE INDEX testidx ON test USING GIN (a);
-- запрос
SELECT * FROM test WHERE a < 10;

Ссылки на документацию разработчика​

Дополнительно поставляемый модуль btree_gin: https://www.postgresql.org/docs/15/btree-gin.html.