Оптимизация на единична заявка с GROUP BY в PostgreSQL

Веднага трябва да кажа, че в тази статия няма универсален съвет за всички случаи, но се разглежда случаят на оптимизиране само на малък клас заявки. Такива заявки обаче могат да възникнат в много проекти.

Да формулираме проблема

Нека разгледаме такава схема. Имаме две дъски

съдържание – документи.
content_keyword_ref - ключови думи, които присъстват в документа.

Имам около 2 милиона документа в моята локална база данни и около 15 милиона връзки с ключови думи.

Изберете документи, съдържащи една от изброените ключови думи.

Класическо решение

За да направим това, ще трябва да напишем нещо подобно на тази заявка (веднага ще добавя EXPLAIN ANALYZE и ще покажа плана):

GROUP BY трябва да използваме само за да гарантираме, че изходът не дублира документи за всяка намерена ключова дума. Какво виждаме в плана за изпълнение на заявката:

Ще получим същия резултат, ако използваме DISTINCT вместо GROUP BY:

Както можете да видите, групирането води до сортиране и други допълнителни разходи. При някои данни времето за изпълнение достига няколко секунди!

Оптимизация

Идеите ми как да ускоря заявката със съществуващата схема приключиха. Нека се опитаме да възстановим схемата. Оставете етикета за съдържание. Но ние ще съхраняваме връзки с ключови думи в масив. За бързо избиране на данни по условия в масива, ние също създаваме GiST индекс. За информация кои оператори на масиви се поддържат от индекси, вижте документацията на PostgreSQL.

Сега нека се опитаме да създадем заявка, която ще върне същите данни като в опциите по-горе:

Полза има и то забележима. На избраните данни, оптимизирана версия на заявкатаработи около 14 пъти по-бързо. Текстът на искането остана приблизително същият разбираем. Да видим какви други ползи имаме.

Да приемем, че трябва да покажете намерени документи на страница с пагинация. Как в този случай да преброим броя на записите в извадката в "класическата" версия? Ето някои опции:

Преброяваме броя на записите в подзаявка с GROUP BY:

Преброяване на броя записи в подзаявка с DISTINCT:

Преброяване на броя на записите без подзаявка, но с използване на БРОЙ (ОТДЕЛНИ колони):

Във всички тези опции минусът не е само в производителността. Ще направи ли автоматично една от тези опции модулът за страниране във вашата рамка? Laravel, например, не го прави. Вместо това ще избере всички записи и ще ги преброи с помощта на count() вече в PHP. Следователно най-вероятно ще трябва да предефинирате метода за изчисляване на броя на записите, така че цялата селекция да не се изважда от базата данни всеки път.

Как да преброим броя на записите в оптимизирана версия на заявка:

Много по-сбито и без проблеми с пагинатора.

Още един бонус

Избрахме документи, съдържащи поне една от посочените думи. Какво ще стане, ако искате да изберете документи, които съдържат всички ключови думи, представляващи интерес? В класическата версия заявката може да бъде изградена по следния начин:

Тоест, колко ключови думи търсим, толкова JOINs правим. Ако филтрираме записи по масив, можем да използваме @> . Тогава заявката изглежда по-чиста:

И той има по-добър план за изпълнение.

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

Експериментирах с различни данни. обикновено,оптимизираната версия дава увеличение на скоростта от 2 до 10 пъти. Но успях да взема примери, когато заявките за изчисляване на броя на записите в изхода работят 1,5-2 пъти по-бавно в случая на „оптимизираната“ версия.

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

Hardcore conf в C++. Каним само професионалисти.