В Postgres индексы бывают разных типов. Самый базовый и нужный называется btree, который основан на одноименной структуре данных – дереве B-Tree. Оно состоит из распределительных узлов, и на последнем уровне находятся пары вида (значение, страница).

Btree поддерживает равенство и сравнение. Это значит, в нем можно искать по точному значению и диапазонам, а еще он подходит для сортировки, потому что упорядочен.

В случаях, когда нужно частичное сравнение, индекс btree не подходит. Вместо него используют Gin или Gist (отдавая предпочтение первому). Эти индексы сложнее и в целом медленнее btree. Поэтому пока возможно, используйте btree.

Есть, однако, лазейка, чтобы использовать btree для частичного сравнения. Если применить его к строке с особой опцией, можно искать по началу строки оператором (i)like и регулярным выражением.

Предположим, в таблице demo хранятся пути к файлам:

create table demo(
    id integer primary key,
    path text not null
);

Сгенерируем миллион файлов. Пути начинаются с года, месяца, дня и часа. Даты разнообразны в рамках десяти лет:

insert into demo
select
    x,
    format('%s/%s/picture.jpeg', to_char((now() - interval '10 years' *  random()), 'yyyy/MM/DD/HH'), gen_random_uuid())
from
    generate_series(1, 999999) as seq(x);

Навесим индекс на путь. Опция text_pattern_ops нужна, чтобы зафиксировать локаль. С ней сравнение строк не зависит от текущей локали:

create index path_btree on demo
using btree
(path text_pattern_ops);

Выберем файлы за октябрь 2023 года:

select * from demo
where path like '2023/10/12%'
limit 100;

Справа от like стоит шаблон, а не простой текст. Символ % означает любые символы. Вот что вернет запрос:

┌────────┬─────────────────────────────────────────┐
│   id   │                path                     │
├────────┼─────────────────────────────────────────┤
│ 213875 │ 2023/10/12/01/00ec...af263/picture.jpeg │
│ 670319 │ 2023/10/12/01/04e2...67afd/picture.jpeg │
│  34516 │ 2023/10/12/01/063a...ebddc/picture.jpeg │
│ 122843 │ 2023/10/12/01/3237...6ffd7/picture.jpeg │
│ 227530 │ 2023/10/12/01/5a48...c4ec0/picture.jpeg │
│ 141900 │ 2023/10/12/01/6a78...da021/picture.jpeg │
│ 663626 │ 2023/10/12/01/9d6d...84da7/picture.jpeg │
│ 154138 │ 2023/10/12/01/a02e...5570c/picture.jpeg │
│  37955 │ 2023/10/12/01/a465...47a7a/picture.jpeg │
│ 864446 │ 2023/10/12/01/ae47...943d9/picture.jpeg │
│ 196849 │ 2023/10/12/01/cd15...25874/picture.jpeg │

Но гораздо интересней план:

explain analyze
select * from demo
where path like '2023/10/12%'
limit 100;

│ Limit  (cost=0.55..8.57 rows=100 width=68)
│ (actual time=0.054..0.364 rows=100 loops=1)
│   ->  Index Scan using path_btree on demo
│       (cost=0.55..8.57 rows=100 width=68)
│         Index Cond: ((path ~>=~ '2023/10/12'::text)
│                  AND (path ~<~ '2023/10/13'::text))
│         Filter: (path ~~ '2023/10/12%'::text)
│ Planning Time: 0.375 ms
│ Execution Time: 0.400 ms
└────────────────────────────────────────────────────

Видим, что Postgres поступает хитро: заключает путь в диапазон 2023/10/12 и 2023/10/13. Цифра 3 на конце получилась как следующий символ за 2.

Попадаем в индекс, стоимость копеечная.

Если поставить в начало шаблона %, индекс не сработает. Другими словами, с таким подходом нельзя искать пути по вхождению.

Индекс и оператор like позволяют срезать пути по лидирующей строке. При этом не обязательно с точностью до папки. Например, шаблон может быть таким:

2025/10/23/%cat%.jpeg

Так мы получим все фотографии в папке 2025/10/23/… со словом cat в названии и расширением jpeg. Кроме шаблонов like, индекс поддерживает регулярки, и с ними критерий поиска можно описать точнее.

Чем больше лидирующих символов мы задали, тем быстрее будет поиск.

Еще один трюк: что если нужно искать с конца строки? Например, имеется таблица товаров с серийными номерами:

create table demo2(
    id integer primary key,
    serial_number text not null
);

Вставим миллион серийных номеров:

insert into demo2
select
    x,
    to_char(random() * 1000000000000, '0000-0000-0000')
from
    generate_series(1, 999999) as seq(x);

┌────────┬─────────────────┐
│   id   │  serial_number  │
├────────┼─────────────────┤
│      1 │  7084-1644-4953 │
│      2 │  2694-3476-5136 │
│      3 │  3274-5880-3765 │
│      4 │  9346-6945-3461 │
│      5 │  3352-3981-1479 │
│      6 │  7103-7860-4732 │
│      7 │  2634-9208-2518 │
│      8 │  1485-3601-0146 │
│      9 │  8068-5128-1222 │
│     10 │  5832-3252-5642 │

Сотрудники ищут продукцию по последним числам серийного номера. Чтобы сработал индекс btree, сделаем функциональный индекс на выражение reverse(serial_number). В результате в индексе будут храниться перевернутые номера:

create index path_btree_rev on demo2
using btree
(reverse(serial_number) text_pattern_ops);

Чтобы искать по ним, выражение поиска тоже нужно перевернуть. Давайте найдем все номера, которые заканчиваются на -0500:

select * from demo2
where reverse(serial_number) like reverse('%-0500')
limit 100;

Результат:

┌────────┬─────────────────┐
│   id   │  serial_number  │
├────────┼─────────────────┤
│ 237074 │  5088-3410-0500 │
│ 231067 │  0411-7020-0500 │
│ 728812 │  3489-6520-0500 │
│ 618494 │  3972-6720-0500 │
│ 486950 │  4871-5330-0500 │
│ 634762 │  8038-7440-0500 │
│ 733815 │  8856-2250-0500 │
│ 451464 │  3640-0450-0500 │
│ 868649 │  7289-2270-0500 │
│ 485383 │  7016-0370-0500 │
│ 782061 │  4076-8080-0500 │
│ 234715 │  4279-2180-0500 │
│  36948 │  9033-7280-0500 │
│ 460010 │  5881-8090-0500 │

Самое интересное – план:

│ Limit  (cost=0.42..8.45 rows=100 width=16) (actual
│   ->  Index Scan using path_btree_rev on demo2  (co
│         Index Cond: ((reverse(serial_number) ~>=~ '
│         Filter: (reverse(serial_number) ~~ '0050-%'
│ Planning Time: 0.286 ms
│ Execution Time: 1.088 ms
└────────────────────────────────────────────────────

Работает!

Повторю, что btree и text_pattern_ops работают только для начала строки. Если нужен поиск посередине, обратитесь к расширению pg_tgrm (триграммы). Этот индекс, однако, требует Gin.

У text_pattern_ops есть еще один полезный сценарий, но о нем – в следующий раз.

← Совет дня №31

Совет дня №33 →