Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL

В PostgreSQL есть довольно интересный функционал — диапазонные типы данных (range). Они весьма удобны в использовании. Для индексирования этих типов данных существует GIST индекс. Однако на практике часто требуется сочетание BTREE индекса с GIST, что реализуется расширением btree_gist. Насколько эффективно удобство, предоставляемое диапазонными типами данных в сочетании с btree_gist мы и разберем в этой статье.

Для ЛЛ — с производительностью при использовании btree_gist будет плохо.

Предположим нам нужен версионированная по датам таблица. У нас есть некоторый идентификатор, некоторые аналитики и диапазон дат, в течении которых эти аналитики были действительны. Например:

Пустое значение «Дата по» обозначает, что аналитики действуют с «Даты с» до бесконечности. Диапазоны для каждого клиента не могут пересекаться, но между диапазонами допускаются разрывы на то время, пока с клиентом никаких отношений не было. На самом деле, разрывы между диапазонами мы допускаем потому, что GIST индекс позволяет не допускать пересечения диапазонов, но не позволяет контролировать отсутствие между ними разрывов. А так как контроль за отсутствием разрывов без триггера всё равно не реализовать, то для целей сравнения производительности BTREE и btree_gist это значение не имеет.

Максимально упростим пример, сделав идентификатор (Id) просто integer, а аналитики пусть будут строкой и числом. Это можно представить в виде следующей таблицы.

CREATE TABLE tmp_test_range (   Id    integer       NOT NULL,   Valid daterange     NOT NULL, -- диапазон дат в виде встроенного типа   Code  integer       NOT NULL,   Amt   decimal(16,2) NOT NULL,   CONSTRAINT tmp_test_range_PK_idx     EXCLUDE USING GIST (Id WITH =, Valid WITH &&) -- запрет на пересечение  );

Теперь заполним нашу таблицу тестовыми данными.

INSERT INTO tmp_test_range (Id, Valid, Code, Amt) SELECT G.n / 10 AS Id,   daterange(     ( '2023-01-01'::date       + '1 day'::interval       * (G.n % 10) * 30 )::date,     CASE WHEN G.n % 10 = 9 THEN NULL       ELSE         ( '2023-01-01'::date           + '1 day'::interval           * ( (G.n % 10) * 30                + (G.n % 10 + 1) * 3 ) )::date END,     '[)' ),   G.n AS Code,   G.n*0.5 AS Amt FROM generate_series(0,999999) G(n);

Insert on tmp_test_range  (cost=0.00..67500.00 rows=0 width=0) (actual time=45364.169..45364.170 rows=0 loops=1)   ->  Function Scan on generate_series g  (cost=0.00..67500.00 rows=1000000 width=58) (actual time=63.500..1115.834 rows=1000000 loops=1) Planning Time: 0.068 ms Execution Time: 45373.995 ms

Попробуем теперь сделать тоже самое без использования btree_gist и GIST. Создадим таблицу:

CREATE TABLE tmp_test_not_range (   Id         integer       NOT NULL,   ValidFrom  date          NOT NULL,   ValidUntil date          NULL,   Code       integer       NOT NULL,   Amt        decimal(16,2) NOT NULL,   CONSTRAINT tmp_test_not_range_PK_idx     PRIMARY KEY (Id, ValidFrom) INCLUDE (ValidUntil) );

Так как контролировать пересечения диапазонов дат BTREE нам не позволяет, то потребуется выполнять это самим через триггер. Сначала создадим функцию для триггера:

CREATE OR REPLACE FUNCTION   tmp_test_not_range_before_insert_update_tfn() RETURNS TRIGGER AS $func$ <<func>> DECLARE   ValidFrom  date;   ValidUntil date; BEGIN   -- Дата конца диапазона должна быть больше или равна дате его начала   IF NEW.ValidFrom>COALESCE(NEW.ValidUntil,NEW.ValidFrom) THEN     RAISE EXCEPTION 'ValidUntil must be higher or equal ValidFrom';   END IF;    -- Ищем, нет ли пересечений диапазонов   -- При UPDATE исключаем из поиска обновляемую запись   SELECT F.ValidFrom, F.ValidUntil   FROM (     SELECT T.ValidFrom, T.ValidUntil      FROM tmp_test_not_range T     WHERE T.Id=NEW.Id       AND ( OLD.Id IS NULL         OR NOT (T.Id=OLD.Id AND T.ValidFrom=OLD.ValidFrom) )     AND T.ValidFrom<=COALESCE(NEW.ValidUntil,'infinity'::date)     ORDER BY T.ValidFrom DESC     LIMIT 1 ) F   WHERE COALESCE(F.ValidUntil,'infinity'::date)>=NEW.ValidFrom   INTO func.ValidFrom, func.ValidUntil;    -- Если пересечение обнаружено, выводим подробности   IF func.ValidFrom IS NOT NULL THEN     RAISE EXCEPTION       'Id % ValidFrom % intersect with ValidFrom % and ValidUntil %',       NEW.Id, New.ValidFrom, func.ValidFrom, func.ValidUntil;   END IF;    RETURN NEW; END; $func$ LANGUAGE plpgsql;

А теперь создаем и триггер:

CREATE OR REPLACE TRIGGER tmp_test_not_range_before_insert_update_trg   BEFORE INSERT OR UPDATE OF Id, ValidFrom, ValidUntil   ON tmp_test_not_range FOR EACH ROW   EXECUTE FUNCTION tmp_test_not_range_before_insert_update_tfn();

Заполним и эту таблицу теми же самыми тестовыми данными:

INSERT INTO tmp_test_not_range (Id,   ValidFrom, ValidUntil, Code, Amt) SELECT G.n / 10 AS Id,   ( '2023-01-01'::date     + '1 day'::interval     * (G.n % 10) * 30 )::date,   CASE WHEN G.n % 10 = 9 THEN NULL     ELSE       ( '2023-01-01'::date         + '1 day'::interval         * ( (G.n % 10) * 30              + (G.n % 10 + 1) * 3 ) )::date END,   G.n AS Code,   G.n*0.5 AS Amt FROM generate_series(0,999999) G(n);

Insert on tmp_test_not_range  (cost=0.00..65000.00 rows=0 width=0) (actual time=11581.151..11581.151 rows=0 loops=1)   ->  Function Scan on generate_series g  (cost=0.00..65000.00 rows=1000000 width=34) (actual time=83.449..890.624 rows=1000000 loops=1) Planning Time: 0.086 ms Trigger tmp_test_not_range_after_insert_update_trg: time=8429.938 calls=1000000 Execution Time: 11621.851 ms

Получается, что вставка записей в таблицу индексированную btree_gist проигрывает почти в 4 раза вставке записей в таблицу индексированную BTREE плюс издержки на триггере.

Может быть btree_gist даст выигрыш хотя бы на выборке данных? Проверим. Выберем из нашей таблицы с миллионом записей и 100 тыс. различных Id всего 10 тыс записей для разных Id на некоторую дату:

SELECT R.Id, R.Valid, R.Code, R.Amt FROM generate_series(0,999999,10) G(n) JOIN tmp_test_range R ON R.Id=G.n AND R.Valid@>'2023-06-12'::date;

Hash Join  (cost=11992.91..20109.38 rows=99147 width=28) (actual time=155.387..179.831 rows=10000 loops=1)   Hash Cond: (g.n = r.id)   ->  Function Scan on generate_series g  (cost=0.00..1000.00 rows=100000 width=4) (actual time=7.546..11.391 rows=100000 loops=1)   ->  Hash  (cost=10753.36..10753.36 rows=99164 width=28) (actual time=147.726..147.729 rows=100000 loops=1)         Buckets: 131072  Batches: 1  Memory Usage: 7368kB         ->  Bitmap Heap Scan on tmp_test_range r  (cost=1769.81..10753.36 rows=99164 width=28) (actual time=106.657..132.703 rows=100000 loops=1)               Recheck Cond: (valid @> '2023-06-12'::date)               Heap Blocks: exact=7744               ->  Bitmap Index Scan on tmp_test_range_pk_idx  (cost=0.00..1745.02 rows=99164 width=0) (actual time=105.592..105.592 rows=100000 loops=1)                     Index Cond: (valid @> '2023-06-12'::date) Planning Time: 0.179 ms Execution Time: 180.755 ms

Аналогичный запрос по второй таблице:

SELECT R.Id, R.ValidFrom, R.ValidUntil, R.Code, R.Amt FROM generate_series(0,999999,10) G(n) CROSS JOIN LATERAL (   SELECT T.Id, T.ValidFrom, T.ValidUntil, T.Code, T.Amt   FROM tmp_test_not_range T   WHERE T.Id=G.n AND T.ValidFrom<='2023-06-12'::date   ORDER BY T.ValidFrom DESC   LIMIT 1) R WHERE COALESCE(R.ValidUntil,'infinity'::date)>='2023-06-12'::date;

Nested Loop  (cost=0.43..124520.84 rows=100000 width=23) (actual time=3.551..148.543 rows=10000 loops=1)   ->  Function Scan on generate_series g  (cost=0.00..1000.00 rows=100000 width=4) (actual time=3.512..8.371 rows=100000 loops=1)   ->  Subquery Scan on r  (cost=0.42..1.23 rows=1 width=23) (actual time=0.001..0.001 rows=0 loops=100000)         Filter: (COALESCE(r.validuntil, 'infinity'::date) >= '2023-06-12'::date)         ->  Limit  (cost=0.42..1.21 rows=1 width=23) (actual time=0.001..0.001 rows=0 loops=100000)               ->  Index Scan Backward using tmp_test_not_range_pk_idx on tmp_test_not_range t  (cost=0.42..5.15 rows=6 width=23) (actual time=0.001..0.001 rows=0 loops=100000)                     Index Cond: ((id = g.n) AND (validfrom <= '2023-06-12'::date)) Planning Time: 0.145 ms Execution Time: 149.595 ms

Увы. Как видим на выборке данных btree_gist тоже проигрывает, хоть и не столь значительно — на 20%. Что, впрочем, тоже немало.

Таким образом, нам удалось выяснить, что btree_gist следует использовать с осторожностью. Да, диапазонные типы данных и btree_gist сокращают время разработки, но цена этого — деградация производительности при вставке записей почти в 4 раза, а на выборке — 20%. Поэтому использовать btree_gist в тех случаях, когда производительность важна, не рекомендуется. Возможно, в будущем эта проблема будет исправлена, но пока что приходится с этим жить.

Спасибо, если дочитали!