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))
.
"Набираме персонал"!