В 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. Создадим таблицы:

REATE TABLE tmp_test_range_header (
  Id          integer       NOT NULL,
  Description varchar       NULL,
  CONSTRAINT tmp_test_range_header_PK_idx PRIMARY KEY (Id)
);

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),
  CONSTRAINT tmp_test_not_range_FK_Id_idx
    FOREIGN KEY (Id) REFERENCES tmp_test_range_header (Id)
);

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

Заполним таблицу заголовка:

INSERT INTO tmp_test_range_header (Id, Description)
SELECT G.n AS Id, 'Description is '||G.n::text AS Code
FROM generate_series(0,99999) G(n);

Cоздадим функцию для триггера:

CREATE OR REPLACE FUNCTION
  tmp_test_not_range_after_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;
  -- Блокируем Id в таблице заголовка, чтобы между проверкой на
  -- пересечения диапазонов и фиксацией транзакции другой процесс
  -- не смог бы зафиксировать свою транзакцию
  PERFORM H.Id
  FROM tmp_test_range_header H
  WHERE H.Id=NEW.Id
  FOR NO KEY UPDATE OF H;
  -- Проверяем наличие пересечений диапазонов
  SELECT F.ValidFrom, F.ValidUntil
  FROM (
    SELECT T.ValidFrom, T.ValidUntil 
    FROM tmp_test_not_range T
    WHERE T.Id=NEW.Id AND T.ValidFrom<>NEW.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=2353.214..2353.215 rows=0 loops=1)
  ->  Function Scan on generate_series g  (cost=0.00..65000.00 rows=1000000 width=34) (actual time=68.960..660.541 rows=1000000 loops=1)
Planning Time: 0.069 ms
Execution Time: 2375.919 ms

Timing is on.
INSERT 0 1000000
Time: 19005.504 ms (00:19.006)

Получается, что вставка записей в таблицу индексированную btree_gist проигрывает более чем в 2 раза вставке записей в таблицу индексированную 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 сокращают время разработки, но цена этого - деградация производительности при вставке записей более чем в 2 раза, а на выборке - 20%. Поэтому использовать btree_gist в тех случаях, когда производительность важна, не рекомендуется. Возможно, в будущем эта проблема будет исправлена, но пока что приходится с этим жить.

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

Update: Благодаря конструктивной критике @erogovв статью были внесены изменения для обеспечения контроля за пересечением диапазонов при конкурентной записи в таблицу из нескольких соединений. За что ему выражаю огромную благодарность!