بهینه سازی جستجوی فازی در چندین جدول: pg_trgm، GIN و Triggers

Summarize this content to 400 words in Persian Lang
PostgreSQL بهبودهای قابل توجهی را فراتر از نمایه سازی تک ستونی ارائه می دهد که YugabyteDB نیز از آن استفاده می کند. برای جستجوهایی که مقدار کامل آن مشخص نیست، پسوند pg_trgm می‌تواند چندین تریگرام ایجاد کند و GIN این مقادیر چندگانه را در هر ردیف فهرست می‌کند. علاوه بر این، اسکن بیت مپ می‌تواند نمایه‌ها را در ستون‌های مختلف در یک جدول ترکیب کند. با این حال، چالش‌ها زمانی به وجود می‌آیند که گزاره‌ها در جدول‌های مختلف در یک اتصال قرار می‌گیرند. در اینجا، تریگرها وارد عمل می‌شوند و با حفظ ستون‌های تعریف‌شده توسط کاربر که به جستجوهای فازی اختصاص داده شده‌اند، امکان ایجاد نمایه‌سازی سفارشی را فراهم می‌کنند.

در اینجا مثالی با استفاده از جدولی از شهرها و کشورها ارائه شده است که من از gvenzl/sample-data می‌سازم:

\! curl -s https://raw.githubusercontent.com/gvenzl/sample-data/main/countries-cities-currencies/uninstall.sql > countries.sql
\! curl -s https://raw.githubusercontent.com/gvenzl/sample-data/main/countries-cities-currencies/install.sql | awk ‘/D a t a l o a d/{print “do $$ begin”}/ COMMIT /{print “end; $$ ;”}{print}’ >> countries.sql
\i countries.sql

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

می‌خواهم شهرهایی را فهرست کنم که در نام شهر یا نام کشورشان “بندر” وجود دارد:

yugabyte=# select city.name, country.name
from cities city
join countries country using(country_id)
where city.name ilike ‘%port%’ or country.name ilike ‘%port%’;

name | name
—————-+———————
Port Moresby | Papua New Guinea
Port of Spain | Trinidad and Tobago
Port au Prince | Haiti
Porto Novo | Benin
Port Vila | Vanuatu
Port Louis | Mauritius
Lisbon | Portugal
(7 rows)

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

کدام شاخص برای تسریع در این جستجو ایجاد شود؟

تریگرام و شاخص GIN در هر دو جدول

از آنجا که من یک جستجوی فازی انجام می دهم، می توانم آن را ایجاد کنم pg_trgm پسوند و یک شاخص GIN در هر دو ستون:

create extension if not exists pg_trgm;
create index on cities using gin (name gin_trgm_ops);
create index on countries using gin (name gin_trgm_ops);

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

متأسفانه، نمی توان آن را با درخواست من استفاده کرد زیرا دو ستون در دو جدول قرار دارند و اسکن بیت مپ فقط می تواند بیت مپ ها را برای یک جدول ترکیب کند:

yugabyte=# explain (costs off)
select city.name, country.name
from cities city
join countries country using(country_id)
where city.name ilike ‘%port%’ or country.name ilike ‘%port%’;
QUERY PLAN
———————————————————————————————–
Nested Loop
-> Seq Scan on cities city
-> Index Scan using countries_pk on countries country
Index Cond: ((country_id)::text = (city.country_id)::text)
Filter: (((city.name)::text ~~* ‘%port%’::text) OR ((name)::text ~~* ‘%port%’::text))
(5 rows)

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

من می توانم پرس و جو را با یک UNION دوباره بنویسم:

yugabyte=# explain (costs off)
select city.name, country.name
from cities city
join countries country using(country_id)
where city.name ilike ‘%port%’
union
select city.name, country.name
from cities city
join countries country using(country_id)
where country.name ilike ‘%port%’;
QUERY PLAN
—————————————————————————————————————————————————-
HashAggregate
Group Key: city.name, country.name
-> Append
-> YB Batched Nested Loop Join
Join Filter: ((city.country_id)::text = (country.country_id)::text)
-> Index Scan using cities_name_idx1 on cities city
Index Cond: ((name)::text ~~* ‘%port%’::text)
-> Index Scan using countries_pk on countries country
Index Cond: ((country_id)::text = ANY (ARRAY[(city.country_id)::text, ($1)::text, ($2)::text, …, ($1023)::text]))
-> YB Batched Nested Loop Join
Join Filter: ((city_1.country_id)::text = (country_1.country_id)::text)
-> Index Scan using countries_name_idx1 on countries country_1
Index Cond: ((name)::text ~~* ‘%port%’::text)
-> Index Scan using cities_countries_fk001 on cities city_1
Index Cond: ((country_id)::text = ANY (ARRAY[(country_1.country_id)::text, ($1025)::text, ($1026)::text, …, ($2047)::text]))
(15 rows)

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

این کار می کند، اما ممکن است کاربر محدودیت هایی با ORM داشته باشد که نتواند چنین درخواستی را ایجاد کند.

یک ستون اضافی برای جستجو حفظ کنید

من یک ستون اضافی در “شهرها” ایجاد می کنم که نام شهر و نام کشور را با یک شاخص GIN روی سه گرام ها به هم پیوند می دهد:

alter table cities add fuzzy_search text;

create index on cities using gin (fuzzy_search gin_trgm_ops);

update cities
set fuzzy_search = format(‘%s %s’,cities.name, countries.name)
from countries where countries.country_id = cities.country_id
;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

اکنون می توانم در این ستون “fuzzy_search” پرس و جو کنم:

yugabyte=# explain (costs off)
select city.name, country.name
from cities city
join countries country using(country_id)
where fuzzy_search ilike ‘%port%’;
QUERY PLAN
—————————————————————————————————————————–
YB Batched Nested Loop Join
Join Filter: ((city.country_id)::text = (country.country_id)::text)
-> Index Scan using cities_fuzzy_search_idx on cities city
Index Cond: (fuzzy_search ~~* ‘%port%’::text)
-> Index Scan using countries_pk on countries country
Index Cond: ((country_id)::text = ANY (ARRAY[(city.country_id)::text, ($1)::text, ($2)::text, …, ($1023)::text]))
(6 rows)

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

به همین دلیل است که پایگاه‌های داده SQL دارای محرک‌هایی هستند: برای افزودن مقداری منطق داده شفاف به برنامه.

کد تریگرها به برنامه و الگوهای دسترسی شما برای کاهش هزینه های اضافی بستگی دارد. مواردی که به آنها خواهم پرداخت عبارتند از:

درج یک شهر جدید که به یک کشور اشاره دارد باید نام کشور را به ستون جستجو اضافه کند
به روز رسانی یک شهر باید آن را در ستون جستجو به روز کند
درج، به‌روزرسانی یا حذف یک کشور باید ستون جستجو را در همه شهرهایی که به آن ارجاع می‌دهند، به‌روزرسانی کند. اگر کلید خارجی با محدودیت آبشاری دارید، ممکن است به همه آن موارد نیاز نداشته باشید.

در اینجا محرک های من هستند:

— Trigger for insert, update, and delete on cities
create or replace function trg_cities_fuzzy_search() returns trigger as $$
begin
if tg_op = ‘INSERT’ or tg_op = ‘UPDATE’ then
new.fuzzy_search := format(‘%s %s’, new.name, (select name from countries where country_id = new.country_id));
return new;
end if;
end;
$$ language plpgsql;
create trigger trg_cities_fuzzy_search
before insert or update or delete on cities
for each row execute function trg_cities_fuzzy_search();

— Trigger for update and delete on countries
create or replace function trg_countries_fuzzy_search() returns trigger as $$
begin
if tg_op in ( ‘UPDATE’ , ‘INSERT’ ) then
update cities
set fuzzy_search = format(‘%s %s’, cities.name, new.name)
where country_id = new.country_id;
return new;
elsif tg_op = ‘DELETE’ then
update cities
set fuzzy_search = format(‘%s’, name)
where country_id = old.country_id;
return old;
end if;
end;
$$ language plpgsql;
create trigger trg_countries_fuzzy_search
after update or delete on countries
for each row
execute function trg_countries_fuzzy_search();

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

مهمترین چیز اضافه کردن تست هایی برای پوشش تمام DML است که ممکن است در آن جداول اتفاق بیفتد و تأیید کنید که “ستون fuzzy_search” همیشه درست است. به عبارت دیگر، هیچ ردیفی توسط:

select format(‘%s %s’,city.name, country.name) , fuzzy_search as names
from cities city
join countries country using(country_id)
where format(‘%s %s’,city.name, country.name) != fuzzy_search
;

وارد حالت تمام صفحه شوید

از حالت تمام صفحه خارج شوید

اگر تست استرس برای موارد استفاده حیاتی دارید که آن جداول را اصلاح می کند، باید بررسی کنید که سربار قابل قبول است.

با اجرای این راه حل، تغییرات مورد نیاز در برنامه حداقل است: به سادگی معیارها را بر اساس ستونی که به طور خاص برای جستجوی فازی تعیین شده است، بسازید. این رویکرد مزایای غیرعادی سازی (فیلتر کردن قبل از پیوستن) را بدون معایب (مانند اضافه کردن منطق داده در کد برنامه و آزمایش برای حفظ ثبات) ارائه می دهد.

این مثال با PostgreSQL، YugabyteDB و هر پایگاه داده سازگار با PostgreSQL سازگار است، که البته به معنای پشتیبانی از شاخص‌ها و محرک‌های GIN است.

\! curl -s https://raw.githubusercontent.com/gvenzl/sample-data/main/countries-cities-currencies/uninstall.sql  > countries.sql
\! curl -s https://raw.githubusercontent.com/gvenzl/sample-data/main/countries-cities-currencies/install.sql | awk '/D a t a    l o a d/{print "do $$ begin"}/ COMMIT /{print "end; $$ ;"}{print}' >> countries.sql
\i countries.sql

yugabyte=# select city.name, country.name
from cities city
join countries country using(country_id)
where city.name ilike '%port%' or country.name ilike '%port%';

      name      |        name
----------------+---------------------
 Port Moresby   | Papua New Guinea
 Port of Spain  | Trinidad and Tobago
 Port au Prince | Haiti
 Porto Novo     | Benin
 Port Vila      | Vanuatu
 Port Louis     | Mauritius
 Lisbon         | Portugal
(7 rows)

create extension if not exists pg_trgm;
create index on cities using gin (name gin_trgm_ops);
create index on countries using gin (name gin_trgm_ops);

yugabyte=# explain (costs off)
select city.name, country.name
from cities city
join countries country using(country_id)
where city.name ilike '%port%' or country.name ilike '%port%';
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Nested Loop
   ->  Seq Scan on cities city
   ->  Index Scan using countries_pk on countries country
         Index Cond: ((country_id)::text = (city.country_id)::text)
         Filter: (((city.name)::text ~~* '%port%'::text) OR ((name)::text ~~* '%port%'::text))
(5 rows)

yugabyte=# explain (costs off)
select city.name, country.name
from cities city
join countries country using(country_id)
where city.name ilike '%port%'
union
select city.name, country.name
from cities city
join countries country using(country_id)
where country.name ilike '%port%';
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate
   Group Key: city.name, country.name
   ->  Append
         ->  YB Batched Nested Loop Join
               Join Filter: ((city.country_id)::text = (country.country_id)::text)
               ->  Index Scan using cities_name_idx1 on cities city
                     Index Cond: ((name)::text ~~* '%port%'::text)
               ->  Index Scan using countries_pk on countries country
                     Index Cond: ((country_id)::text = ANY (ARRAY[(city.country_id)::text, ($1)::text, ($2)::text, ..., ($1023)::text]))
         ->  YB Batched Nested Loop Join
               Join Filter: ((city_1.country_id)::text = (country_1.country_id)::text)
               ->  Index Scan using countries_name_idx1 on countries country_1
                     Index Cond: ((name)::text ~~* '%port%'::text)
               ->  Index Scan using cities_countries_fk001 on cities city_1
                     Index Cond: ((country_id)::text = ANY (ARRAY[(country_1.country_id)::text, ($1025)::text, ($1026)::text, ..., ($2047)::text]))
(15 rows)

alter table cities add fuzzy_search text;

create index on cities using gin (fuzzy_search gin_trgm_ops);

update cities
set fuzzy_search = format('%s %s',cities.name, countries.name)
from countries where countries.country_id = cities.country_id
;

yugabyte=# explain (costs off)
select city.name, country.name
from cities city
join countries country using(country_id)
where fuzzy_search ilike '%port%';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join
   Join Filter: ((city.country_id)::text = (country.country_id)::text)
   ->  Index Scan using cities_fuzzy_search_idx on cities city
         Index Cond: (fuzzy_search ~~* '%port%'::text)
   ->  Index Scan using countries_pk on countries country
         Index Cond: ((country_id)::text = ANY (ARRAY[(city.country_id)::text, ($1)::text, ($2)::text, ..., ($1023)::text]))
(6 rows)

-- Trigger for insert, update, and delete on cities
create or replace function trg_cities_fuzzy_search() returns trigger as $$
begin
    if tg_op = 'INSERT' or tg_op = 'UPDATE' then
        new.fuzzy_search := format('%s %s', new.name, (select name from countries where country_id = new.country_id));
        return new;
    end if;
end;
$$ language plpgsql;
create trigger trg_cities_fuzzy_search
before insert or update or delete on cities
for each row execute function trg_cities_fuzzy_search();

-- Trigger for update and delete on countries
create or replace function trg_countries_fuzzy_search() returns trigger as $$
begin
    if tg_op in ( 'UPDATE' , 'INSERT' ) then
        update cities
        set fuzzy_search = format('%s %s', cities.name, new.name)
        where country_id = new.country_id;
        return new;
    elsif tg_op = 'DELETE' then
        update cities
        set fuzzy_search = format('%s', name)
        where country_id = old.country_id;
        return old;
    end if;
end;
$$ language plpgsql;
create trigger trg_countries_fuzzy_search
after update or delete on countries
for each row
execute function trg_countries_fuzzy_search();

select format('%s %s',city.name, country.name) , fuzzy_search as names
from cities city
join countries country using(country_id)
where format('%s %s',city.name, country.name) != fuzzy_search
;