tl;dr: Заявката с шаблон с префикс ще се изпълнява по-бързо от тази с суфикс, защото първата може да се възползва от индекс. В PostgreSQL можем да създадем функционален индекс, използвайки reverse(), за да направим заявка със суфиксен шаблон ефективна. Освен това може да се наложи да извикате специфични опции, за да накарате индекса да работи с шаблонни заявки.

По-малко от оптималното запитване

Имахме заявка, която търси потребители, чийто имейл принадлежи към определен домейн. (Без загуба на обобщеност, името на домейна е променено, за да защити невинните.)

select distinct users.id, users.name, users.email from users where users.email like '%example%';

Колоната email вече има индекс върху нея, така че не бихме очаквали производителността да е толкова бавна. Изпълнихме EXPLAIN ANALYZE, за да разберем как PostgreSQL изпълни заявката. Имаше тъжна история за разказване.

Postgres изобщо не използва индекса; че Seq Scan показва, че сканира всеки ред в таблицата, за да провери дали всеки ред съответства на шаблона. Със сигурност можем да се справим по-добре!

Според документацията на PostgreSQL:

Оптимизаторът може също да използва индекс на B-дърво за заявки, включващи операторите за съвпадение на шаблони LIKE и ~ if шаблонът е константа и е закотвен към началото на низа — например col LIKE 'foo %' или col ~ '^foo', но не и col LIKE '%bar'. Въпреки това, ако вашата база данни не използва C локал, ще трябва да създадете индекса със специален операторен клас, за да поддържате индексиране на заявки за съвпадение на шаблони; вижте Раздел 11.9 по-долу. Също така е възможно да се използват индекси на B-дърво за ILIKE и ~*, но само ако моделът започва с неазбучни знаци, т.е. знаци, които не се влияят от преобразуването на главни/малки букви.

Представете си, че имаме списък с президенти на Съединените щати по азбучен ред:

Abraham Lincoln
Andrew Jackson
Andrew Johnson
...
Zachary Taylor

Бихте могли бързо да потърсите всички президенти на име Уилям — просто преминете към W — но този списък не е оптимизиран за намиране на фамилни имена, да речем президенти, чието фамилно име е Рузвелт.

Поставете го, обърнете го и го обърнете

Нашата стратегия е да изградим отделен индекс, който ни позволява достъп до края на низа. В PostgreSQL можете да дефинирате функционален индекс, за да търсите стойност от виртуална колона. Това позволява няколко различни подхода:

Взехме третия вариант, тъй като изглеждаше най-лесен за изпълнение и най-малко склонен към грешки.

CREATE INDEX CONCURRENTLY idx_users_email_reverse on USERS (reverse(email))

Новото ни запитване ще бъде:

select distinct users.id, users.name, users.email from users where reverse(users.email) like reverse('%example.com');

Но уви, това не доведе до никаква промяна в плана EXPLAIN. Нашата заявка все още правеше сканиране на цялата таблица.

Нека прочетем отново този откъс от документацията по-горе. Втората половина на тази графика има цял отказ от отговорност относно локалите, така че нека попитаме: аизползваме ли C локал?

psql=> SHOW LC_COLLATE;
lc_collate
-------------
en_US.UTF-8
(1 row)

D’oh.

Здравейте, оператор

Както се посочва в документацията, ние „ще трябва да създадем индекса със специален операторен клас, за да поддържаме индексиране на заявки за съвпадение на шаблони“. С други думи, PostgreSQL трябва да създаде индекс със структура от данни, която поддържа операции като >= и <= с шаблони. Планът EXPLAIN по-горе предполага, че прехвърляме модела към текстов тип, така че избрахме квалификатора text_pattern_ops:

CREATE INDEX CONCURRENTLY idx_users_email_reverse on USERS (reverse(email) text_pattern_ops)

Нека стартираме EXPLAIN отново, за да видим дали това работи:

Зашеметяващо! Почти 20 000 пъти подобрение на производителността! Преминахме от над 5 секунди (в най-добрия случай; имахме данни в двуцифрени стойности) до надеждно под милисекунда.

Ще забележите, че също трябваше да превключим от нечувствителен към малки и малки буквиILIKE към чувствителен към малки и малки буквиLIKE, за да направим това работа. Това само по себе си осигурява значителни (3-4 пъти) подобрения на производителността. В този конкретен случай можем да предположим, че всички имейли са с малки букви, но ако наистина трябваше, вероятно бихме могли да индексираме на reverse(lower(email)).

"Набираме персонал"!