Насколько хороши диапазонные типы и btree_gist индекс в PostgreSQL09.06.2024 02:00

283aa916e513220e373fc953d69d7bb2

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

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

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

Клиент

Дата с

Дата по

Фамилия

Телефон

Мария

2020–01–01

2020–07–12

Иванова

+74991001010

Мария

2021–05–20

2023–02–28

Петрова

+74991001010

Мария

2023–03–01

2023–09–11

Петрова

+74959990101

Мария

2023–12–22

Сидорова

+74959990101

Пустое значение «Дата по» обозначает, что аналитики действуют с «Даты с» до бесконечности. Диапазоны для каждого клиента не могут пересекаться, но между диапазонами допускаются разрывы на то время, пока с клиентом никаких отношений не было. На самом деле, разрывы между диапазонами мы допускаем потому, что 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);

На моем сервере PostgreSQL 15 это стабильно занимает более 45 секунд

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$
<>
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);

На моем сервере PostgreSQL 15 это стабильно занимает менее 12 секунд

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;

У меня в среднем получается 180 миллисекунд

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;

У меня в среднем получается 150 миллисекунд

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

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

© Habrahabr.ru