{"id":68143,"date":"2024-07-04T18:32:21","date_gmt":"2024-07-04T15:02:21","guid":{"rendered":"https:\/\/nabfollower.com\/blog\/optimizing-fuzzy-search-across-multiple-tables-pgtrgm-gin-and-triggers-4d1p\/"},"modified":"2024-07-04T18:32:21","modified_gmt":"2024-07-04T15:02:21","slug":"optimizing-fuzzy-search-across-multiple-tables-pgtrgm-gin-and-triggers-4d1p","status":"publish","type":"post","link":"https:\/\/nabfollower.com\/blog\/optimizing-fuzzy-search-across-multiple-tables-pgtrgm-gin-and-triggers-4d1p\/","title":{"rendered":"\u0628\u0647\u06cc\u0646\u0647 \u0633\u0627\u0632\u06cc \u062c\u0633\u062a\u062c\u0648\u06cc \u0641\u0627\u0632\u06cc \u062f\u0631 \u0686\u0646\u062f\u06cc\u0646 \u062c\u062f\u0648\u0644: pg_trgm\u060c GIN \u0648 Triggers"},"content":{"rendered":"

Summarize this content to 400 words in Persian Lang
\n PostgreSQL \u0628\u0647\u0628\u0648\u062f\u0647\u0627\u06cc \u0642\u0627\u0628\u0644 \u062a\u0648\u062c\u0647\u06cc \u0631\u0627 \u0641\u0631\u0627\u062a\u0631 \u0627\u0632 \u0646\u0645\u0627\u06cc\u0647 \u0633\u0627\u0632\u06cc \u062a\u06a9 \u0633\u062a\u0648\u0646\u06cc \u0627\u0631\u0627\u0626\u0647 \u0645\u06cc \u062f\u0647\u062f \u06a9\u0647 YugabyteDB \u0646\u06cc\u0632 \u0627\u0632 \u0622\u0646 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0645\u06cc \u06a9\u0646\u062f. \u0628\u0631\u0627\u06cc \u062c\u0633\u062a\u062c\u0648\u0647\u0627\u06cc\u06cc \u06a9\u0647 \u0645\u0642\u062f\u0627\u0631 \u06a9\u0627\u0645\u0644 \u0622\u0646 \u0645\u0634\u062e\u0635 \u0646\u06cc\u0633\u062a\u060c \u067e\u0633\u0648\u0646\u062f pg_trgm \u0645\u06cc\u200c\u062a\u0648\u0627\u0646\u062f \u0686\u0646\u062f\u06cc\u0646 \u062a\u0631\u06cc\u06af\u0631\u0627\u0645 \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u062f \u0648 GIN \u0627\u06cc\u0646 \u0645\u0642\u0627\u062f\u06cc\u0631 \u0686\u0646\u062f\u06af\u0627\u0646\u0647 \u0631\u0627 \u062f\u0631 \u0647\u0631 \u0631\u062f\u06cc\u0641 \u0641\u0647\u0631\u0633\u062a \u0645\u06cc\u200c\u06a9\u0646\u062f. \u0639\u0644\u0627\u0648\u0647 \u0628\u0631 \u0627\u06cc\u0646\u060c \u0627\u0633\u06a9\u0646 \u0628\u06cc\u062a \u0645\u067e \u0645\u06cc\u200c\u062a\u0648\u0627\u0646\u062f \u0646\u0645\u0627\u06cc\u0647\u200c\u0647\u0627 \u0631\u0627 \u062f\u0631 \u0633\u062a\u0648\u0646\u200c\u0647\u0627\u06cc \u0645\u062e\u062a\u0644\u0641 \u062f\u0631 \u06cc\u06a9 \u062c\u062f\u0648\u0644 \u062a\u0631\u06a9\u06cc\u0628 \u06a9\u0646\u062f. \u0628\u0627 \u0627\u06cc\u0646 \u062d\u0627\u0644\u060c \u0686\u0627\u0644\u0634\u200c\u0647\u0627 \u0632\u0645\u0627\u0646\u06cc \u0628\u0647 \u0648\u062c\u0648\u062f \u0645\u06cc\u200c\u0622\u06cc\u0646\u062f \u06a9\u0647 \u06af\u0632\u0627\u0631\u0647\u200c\u0647\u0627 \u062f\u0631 \u062c\u062f\u0648\u0644\u200c\u0647\u0627\u06cc \u0645\u062e\u062a\u0644\u0641 \u062f\u0631 \u06cc\u06a9 \u0627\u062a\u0635\u0627\u0644 \u0642\u0631\u0627\u0631 \u0645\u06cc\u200c\u06af\u06cc\u0631\u0646\u062f. \u062f\u0631 \u0627\u06cc\u0646\u062c\u0627\u060c \u062a\u0631\u06cc\u06af\u0631\u0647\u0627 \u0648\u0627\u0631\u062f \u0639\u0645\u0644 \u0645\u06cc\u200c\u0634\u0648\u0646\u062f \u0648 \u0628\u0627 \u062d\u0641\u0638 \u0633\u062a\u0648\u0646\u200c\u0647\u0627\u06cc \u062a\u0639\u0631\u06cc\u0641\u200c\u0634\u062f\u0647 \u062a\u0648\u0633\u0637 \u06a9\u0627\u0631\u0628\u0631 \u06a9\u0647 \u0628\u0647 \u062c\u0633\u062a\u062c\u0648\u0647\u0627\u06cc \u0641\u0627\u0632\u06cc \u0627\u062e\u062a\u0635\u0627\u0635 \u062f\u0627\u062f\u0647 \u0634\u062f\u0647\u200c\u0627\u0646\u062f\u060c \u0627\u0645\u06a9\u0627\u0646 \u0627\u06cc\u062c\u0627\u062f \u0646\u0645\u0627\u06cc\u0647\u200c\u0633\u0627\u0632\u06cc \u0633\u0641\u0627\u0631\u0634\u06cc \u0631\u0627 \u0641\u0631\u0627\u0647\u0645 \u0645\u06cc\u200c\u06a9\u0646\u0646\u062f.<\/p>\n

\u062f\u0631 \u0627\u06cc\u0646\u062c\u0627 \u0645\u062b\u0627\u0644\u06cc \u0628\u0627 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 \u062c\u062f\u0648\u0644\u06cc \u0627\u0632 \u0634\u0647\u0631\u0647\u0627 \u0648 \u06a9\u0634\u0648\u0631\u0647\u0627 \u0627\u0631\u0627\u0626\u0647 \u0634\u062f\u0647 \u0627\u0633\u062a \u06a9\u0647 \u0645\u0646 \u0627\u0632 gvenzl\/sample-data \u0645\u06cc\u200c\u0633\u0627\u0632\u0645:<\/p>\n

\\! curl -s https:\/\/raw.githubusercontent.com\/gvenzl\/sample-data\/main\/countries-cities-currencies\/uninstall.sql > countries.sql
\n\\! 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
\n\\i countries.sql<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0645\u06cc\u200c\u062e\u0648\u0627\u0647\u0645 \u0634\u0647\u0631\u0647\u0627\u06cc\u06cc \u0631\u0627 \u0641\u0647\u0631\u0633\u062a \u06a9\u0646\u0645 \u06a9\u0647 \u062f\u0631 \u0646\u0627\u0645 \u0634\u0647\u0631 \u06cc\u0627 \u0646\u0627\u0645 \u06a9\u0634\u0648\u0631\u0634\u0627\u0646 “\u0628\u0646\u062f\u0631” \u0648\u062c\u0648\u062f \u062f\u0627\u0631\u062f:<\/p>\n

yugabyte=# select city.name, country.name
\nfrom cities city
\njoin countries country using(country_id)
\nwhere city.name ilike ‘%port%’ or country.name ilike ‘%port%’;<\/p>\n

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

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u06a9\u062f\u0627\u0645 \u0634\u0627\u062e\u0635 \u0628\u0631\u0627\u06cc \u062a\u0633\u0631\u06cc\u0639 \u062f\u0631 \u0627\u06cc\u0646 \u062c\u0633\u062a\u062c\u0648 \u0627\u06cc\u062c\u0627\u062f \u0634\u0648\u062f\u061f<\/p>\n

\u062a\u0631\u06cc\u06af\u0631\u0627\u0645 \u0648 \u0634\u0627\u062e\u0635 GIN \u062f\u0631 \u0647\u0631 \u062f\u0648 \u062c\u062f\u0648\u0644<\/p>\n

\u0627\u0632 \u0622\u0646\u062c\u0627 \u06a9\u0647 \u0645\u0646 \u06cc\u06a9 \u062c\u0633\u062a\u062c\u0648\u06cc \u0641\u0627\u0632\u06cc \u0627\u0646\u062c\u0627\u0645 \u0645\u06cc \u062f\u0647\u0645\u060c \u0645\u06cc \u062a\u0648\u0627\u0646\u0645 \u0622\u0646 \u0631\u0627 \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u0645 pg_trgm \u067e\u0633\u0648\u0646\u062f \u0648 \u06cc\u06a9 \u0634\u0627\u062e\u0635 GIN \u062f\u0631 \u0647\u0631 \u062f\u0648 \u0633\u062a\u0648\u0646:<\/p>\n

create extension if not exists pg_trgm;
\ncreate index on cities using gin (name gin_trgm_ops);
\ncreate index on countries using gin (name gin_trgm_ops);<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0645\u062a\u0623\u0633\u0641\u0627\u0646\u0647\u060c \u0646\u0645\u06cc \u062a\u0648\u0627\u0646 \u0622\u0646 \u0631\u0627 \u0628\u0627 \u062f\u0631\u062e\u0648\u0627\u0633\u062a \u0645\u0646 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u06a9\u0631\u062f \u0632\u06cc\u0631\u0627 \u062f\u0648 \u0633\u062a\u0648\u0646 \u062f\u0631 \u062f\u0648 \u062c\u062f\u0648\u0644 \u0642\u0631\u0627\u0631 \u062f\u0627\u0631\u0646\u062f \u0648 \u0627\u0633\u06a9\u0646 \u0628\u06cc\u062a \u0645\u067e \u0641\u0642\u0637 \u0645\u06cc \u062a\u0648\u0627\u0646\u062f \u0628\u06cc\u062a \u0645\u067e \u0647\u0627 \u0631\u0627 \u0628\u0631\u0627\u06cc \u06cc\u06a9 \u062c\u062f\u0648\u0644 \u062a\u0631\u06a9\u06cc\u0628 \u06a9\u0646\u062f:<\/p>\n

yugabyte=# explain (costs off)
\nselect city.name, country.name
\nfrom cities city
\njoin countries country using(country_id)
\nwhere city.name ilike ‘%port%’ or country.name ilike ‘%port%’;
\n QUERY PLAN
\n———————————————————————————————–
\n Nested Loop
\n -> Seq Scan on cities city
\n -> Index Scan using countries_pk on countries country
\n Index Cond: ((country_id)::text = (city.country_id)::text)
\n Filter: (((city.name)::text ~~* ‘%port%’::text) OR ((name)::text ~~* ‘%port%’::text))
\n(5 rows)<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0645\u0646 \u0645\u06cc \u062a\u0648\u0627\u0646\u0645 \u067e\u0631\u0633 \u0648 \u062c\u0648 \u0631\u0627 \u0628\u0627 \u06cc\u06a9 UNION \u062f\u0648\u0628\u0627\u0631\u0647 \u0628\u0646\u0648\u06cc\u0633\u0645:<\/p>\n

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

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u06cc\u0646 \u06a9\u0627\u0631 \u0645\u06cc \u06a9\u0646\u062f\u060c \u0627\u0645\u0627 \u0645\u0645\u06a9\u0646 \u0627\u0633\u062a \u06a9\u0627\u0631\u0628\u0631 \u0645\u062d\u062f\u0648\u062f\u06cc\u062a \u0647\u0627\u06cc\u06cc \u0628\u0627 ORM \u062f\u0627\u0634\u062a\u0647 \u0628\u0627\u0634\u062f \u06a9\u0647 \u0646\u062a\u0648\u0627\u0646\u062f \u0686\u0646\u06cc\u0646 \u062f\u0631\u062e\u0648\u0627\u0633\u062a\u06cc \u0631\u0627 \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u062f.<\/p>\n

\u06cc\u06a9 \u0633\u062a\u0648\u0646 \u0627\u0636\u0627\u0641\u06cc \u0628\u0631\u0627\u06cc \u062c\u0633\u062a\u062c\u0648 \u062d\u0641\u0638 \u06a9\u0646\u06cc\u062f<\/p>\n

\u0645\u0646 \u06cc\u06a9 \u0633\u062a\u0648\u0646 \u0627\u0636\u0627\u0641\u06cc \u062f\u0631 “\u0634\u0647\u0631\u0647\u0627” \u0627\u06cc\u062c\u0627\u062f \u0645\u06cc \u06a9\u0646\u0645 \u06a9\u0647 \u0646\u0627\u0645 \u0634\u0647\u0631 \u0648 \u0646\u0627\u0645 \u06a9\u0634\u0648\u0631 \u0631\u0627 \u0628\u0627 \u06cc\u06a9 \u0634\u0627\u062e\u0635 GIN \u0631\u0648\u06cc \u0633\u0647 \u06af\u0631\u0627\u0645 \u0647\u0627 \u0628\u0647 \u0647\u0645 \u067e\u06cc\u0648\u0646\u062f \u0645\u06cc \u062f\u0647\u062f:<\/p>\n

alter table cities add fuzzy_search text;<\/p>\n

create index on cities using gin (fuzzy_search gin_trgm_ops);<\/p>\n

update cities
\nset fuzzy_search = format(‘%s %s’,cities.name, countries.name)
\nfrom countries where countries.country_id = cities.country_id
\n;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u06a9\u0646\u0648\u0646 \u0645\u06cc \u062a\u0648\u0627\u0646\u0645 \u062f\u0631 \u0627\u06cc\u0646 \u0633\u062a\u0648\u0646 “fuzzy_search” \u067e\u0631\u0633 \u0648 \u062c\u0648 \u06a9\u0646\u0645:<\/p>\n

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

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0628\u0647 \u0647\u0645\u06cc\u0646 \u062f\u0644\u06cc\u0644 \u0627\u0633\u062a \u06a9\u0647 \u067e\u0627\u06cc\u06af\u0627\u0647\u200c\u0647\u0627\u06cc \u062f\u0627\u062f\u0647 SQL \u062f\u0627\u0631\u0627\u06cc \u0645\u062d\u0631\u06a9\u200c\u0647\u0627\u06cc\u06cc \u0647\u0633\u062a\u0646\u062f: \u0628\u0631\u0627\u06cc \u0627\u0641\u0632\u0648\u062f\u0646 \u0645\u0642\u062f\u0627\u0631\u06cc \u0645\u0646\u0637\u0642 \u062f\u0627\u062f\u0647 \u0634\u0641\u0627\u0641 \u0628\u0647 \u0628\u0631\u0646\u0627\u0645\u0647.<\/p>\n

\u06a9\u062f \u062a\u0631\u06cc\u06af\u0631\u0647\u0627 \u0628\u0647 \u0628\u0631\u0646\u0627\u0645\u0647 \u0648 \u0627\u0644\u06af\u0648\u0647\u0627\u06cc \u062f\u0633\u062a\u0631\u0633\u06cc \u0634\u0645\u0627 \u0628\u0631\u0627\u06cc \u06a9\u0627\u0647\u0634 \u0647\u0632\u06cc\u0646\u0647 \u0647\u0627\u06cc \u0627\u0636\u0627\u0641\u06cc \u0628\u0633\u062a\u06af\u06cc \u062f\u0627\u0631\u062f. \u0645\u0648\u0627\u0631\u062f\u06cc \u06a9\u0647 \u0628\u0647 \u0622\u0646\u0647\u0627 \u062e\u0648\u0627\u0647\u0645 \u067e\u0631\u062f\u0627\u062e\u062a \u0639\u0628\u0627\u0631\u062a\u0646\u062f \u0627\u0632:<\/p>\n

\u062f\u0631\u062c \u06cc\u06a9 \u0634\u0647\u0631 \u062c\u062f\u06cc\u062f \u06a9\u0647 \u0628\u0647 \u06cc\u06a9 \u06a9\u0634\u0648\u0631 \u0627\u0634\u0627\u0631\u0647 \u062f\u0627\u0631\u062f \u0628\u0627\u06cc\u062f \u0646\u0627\u0645 \u06a9\u0634\u0648\u0631 \u0631\u0627 \u0628\u0647 \u0633\u062a\u0648\u0646 \u062c\u0633\u062a\u062c\u0648 \u0627\u0636\u0627\u0641\u0647 \u06a9\u0646\u062f
\n\u0628\u0647 \u0631\u0648\u0632 \u0631\u0633\u0627\u0646\u06cc \u06cc\u06a9 \u0634\u0647\u0631 \u0628\u0627\u06cc\u062f \u0622\u0646 \u0631\u0627 \u062f\u0631 \u0633\u062a\u0648\u0646 \u062c\u0633\u062a\u062c\u0648 \u0628\u0647 \u0631\u0648\u0632 \u06a9\u0646\u062f
\n\u062f\u0631\u062c\u060c \u0628\u0647\u200c\u0631\u0648\u0632\u0631\u0633\u0627\u0646\u06cc \u06cc\u0627 \u062d\u0630\u0641 \u06cc\u06a9 \u06a9\u0634\u0648\u0631 \u0628\u0627\u06cc\u062f \u0633\u062a\u0648\u0646 \u062c\u0633\u062a\u062c\u0648 \u0631\u0627 \u062f\u0631 \u0647\u0645\u0647 \u0634\u0647\u0631\u0647\u0627\u06cc\u06cc \u06a9\u0647 \u0628\u0647 \u0622\u0646 \u0627\u0631\u062c\u0627\u0639 \u0645\u06cc\u200c\u062f\u0647\u0646\u062f\u060c \u0628\u0647\u200c\u0631\u0648\u0632\u0631\u0633\u0627\u0646\u06cc \u06a9\u0646\u062f. \u0627\u06af\u0631 \u06a9\u0644\u06cc\u062f \u062e\u0627\u0631\u062c\u06cc \u0628\u0627 \u0645\u062d\u062f\u0648\u062f\u06cc\u062a \u0622\u0628\u0634\u0627\u0631\u06cc \u062f\u0627\u0631\u06cc\u062f\u060c \u0645\u0645\u06a9\u0646 \u0627\u0633\u062a \u0628\u0647 \u0647\u0645\u0647 \u0622\u0646 \u0645\u0648\u0627\u0631\u062f \u0646\u06cc\u0627\u0632 \u0646\u062f\u0627\u0634\u062a\u0647 \u0628\u0627\u0634\u06cc\u062f.<\/p>\n

\u062f\u0631 \u0627\u06cc\u0646\u062c\u0627 \u0645\u062d\u0631\u06a9 \u0647\u0627\u06cc \u0645\u0646 \u0647\u0633\u062a\u0646\u062f:<\/p>\n

— Trigger for insert, update, and delete on cities
\ncreate or replace function trg_cities_fuzzy_search() returns trigger as $$
\nbegin
\n if tg_op = ‘INSERT’ or tg_op = ‘UPDATE’ then
\n new.fuzzy_search := format(‘%s %s’, new.name, (select name from countries where country_id = new.country_id));
\n return new;
\n end if;
\nend;
\n$$ language plpgsql;
\ncreate trigger trg_cities_fuzzy_search
\nbefore insert or update or delete on cities
\nfor each row execute function trg_cities_fuzzy_search();<\/p>\n

— Trigger for update and delete on countries
\ncreate or replace function trg_countries_fuzzy_search() returns trigger as $$
\nbegin
\n if tg_op in ( ‘UPDATE’ , ‘INSERT’ ) then
\n update cities
\n set fuzzy_search = format(‘%s %s’, cities.name, new.name)
\n where country_id = new.country_id;
\n return new;
\n elsif tg_op = ‘DELETE’ then
\n update cities
\n set fuzzy_search = format(‘%s’, name)
\n where country_id = old.country_id;
\n return old;
\n end if;
\nend;
\n$$ language plpgsql;
\ncreate trigger trg_countries_fuzzy_search
\nafter update or delete on countries
\nfor each row
\nexecute function trg_countries_fuzzy_search();<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0645\u0647\u0645\u062a\u0631\u06cc\u0646 \u0686\u06cc\u0632 \u0627\u0636\u0627\u0641\u0647 \u06a9\u0631\u062f\u0646 \u062a\u0633\u062a \u0647\u0627\u06cc\u06cc \u0628\u0631\u0627\u06cc \u067e\u0648\u0634\u0634 \u062a\u0645\u0627\u0645 DML \u0627\u0633\u062a \u06a9\u0647 \u0645\u0645\u06a9\u0646 \u0627\u0633\u062a \u062f\u0631 \u0622\u0646 \u062c\u062f\u0627\u0648\u0644 \u0627\u062a\u0641\u0627\u0642 \u0628\u06cc\u0641\u062a\u062f \u0648 \u062a\u0623\u06cc\u06cc\u062f \u06a9\u0646\u06cc\u062f \u06a9\u0647 “\u0633\u062a\u0648\u0646 fuzzy_search” \u0647\u0645\u06cc\u0634\u0647 \u062f\u0631\u0633\u062a \u0627\u0633\u062a. \u0628\u0647 \u0639\u0628\u0627\u0631\u062a \u062f\u06cc\u06af\u0631\u060c \u0647\u06cc\u0686 \u0631\u062f\u06cc\u0641\u06cc \u062a\u0648\u0633\u0637:<\/p>\n

select format(‘%s %s’,city.name, country.name) , fuzzy_search as names
\nfrom cities city
\njoin countries country using(country_id)
\nwhere format(‘%s %s’,city.name, country.name) != fuzzy_search
\n;<\/p>\n

\u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/p>\n

\u0627\u06af\u0631 \u062a\u0633\u062a \u0627\u0633\u062a\u0631\u0633 \u0628\u0631\u0627\u06cc \u0645\u0648\u0627\u0631\u062f \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u062d\u06cc\u0627\u062a\u06cc \u062f\u0627\u0631\u06cc\u062f \u06a9\u0647 \u0622\u0646 \u062c\u062f\u0627\u0648\u0644 \u0631\u0627 \u0627\u0635\u0644\u0627\u062d \u0645\u06cc \u06a9\u0646\u062f\u060c \u0628\u0627\u06cc\u062f \u0628\u0631\u0631\u0633\u06cc \u06a9\u0646\u06cc\u062f \u06a9\u0647 \u0633\u0631\u0628\u0627\u0631 \u0642\u0627\u0628\u0644 \u0642\u0628\u0648\u0644 \u0627\u0633\u062a.<\/p>\n

\u0628\u0627 \u0627\u062c\u0631\u0627\u06cc \u0627\u06cc\u0646 \u0631\u0627\u0647 \u062d\u0644\u060c \u062a\u063a\u06cc\u06cc\u0631\u0627\u062a \u0645\u0648\u0631\u062f \u0646\u06cc\u0627\u0632 \u062f\u0631 \u0628\u0631\u0646\u0627\u0645\u0647 \u062d\u062f\u0627\u0642\u0644 \u0627\u0633\u062a: \u0628\u0647 \u0633\u0627\u062f\u06af\u06cc \u0645\u0639\u06cc\u0627\u0631\u0647\u0627 \u0631\u0627 \u0628\u0631 \u0627\u0633\u0627\u0633 \u0633\u062a\u0648\u0646\u06cc \u06a9\u0647 \u0628\u0647 \u0637\u0648\u0631 \u062e\u0627\u0635 \u0628\u0631\u0627\u06cc \u062c\u0633\u062a\u062c\u0648\u06cc \u0641\u0627\u0632\u06cc \u062a\u0639\u06cc\u06cc\u0646 \u0634\u062f\u0647 \u0627\u0633\u062a\u060c \u0628\u0633\u0627\u0632\u06cc\u062f. \u0627\u06cc\u0646 \u0631\u0648\u06cc\u06a9\u0631\u062f \u0645\u0632\u0627\u06cc\u0627\u06cc \u063a\u06cc\u0631\u0639\u0627\u062f\u06cc \u0633\u0627\u0632\u06cc (\u0641\u06cc\u0644\u062a\u0631 \u06a9\u0631\u062f\u0646 \u0642\u0628\u0644 \u0627\u0632 \u067e\u06cc\u0648\u0633\u062a\u0646) \u0631\u0627 \u0628\u062f\u0648\u0646 \u0645\u0639\u0627\u06cc\u0628 (\u0645\u0627\u0646\u0646\u062f \u0627\u0636\u0627\u0641\u0647 \u06a9\u0631\u062f\u0646 \u0645\u0646\u0637\u0642 \u062f\u0627\u062f\u0647 \u062f\u0631 \u06a9\u062f \u0628\u0631\u0646\u0627\u0645\u0647 \u0648 \u0622\u0632\u0645\u0627\u06cc\u0634 \u0628\u0631\u0627\u06cc \u062d\u0641\u0638 \u062b\u0628\u0627\u062a) \u0627\u0631\u0627\u0626\u0647 \u0645\u06cc \u062f\u0647\u062f.<\/p>\n

\u0627\u06cc\u0646 \u0645\u062b\u0627\u0644 \u0628\u0627 PostgreSQL\u060c YugabyteDB \u0648 \u0647\u0631 \u067e\u0627\u06cc\u06af\u0627\u0647 \u062f\u0627\u062f\u0647 \u0633\u0627\u0632\u06af\u0627\u0631 \u0628\u0627 PostgreSQL \u0633\u0627\u0632\u06af\u0627\u0631 \u0627\u0633\u062a\u060c \u06a9\u0647 \u0627\u0644\u0628\u062a\u0647 \u0628\u0647 \u0645\u0639\u0646\u0627\u06cc \u067e\u0634\u062a\u06cc\u0628\u0627\u0646\u06cc \u0627\u0632 \u0634\u0627\u062e\u0635\u200c\u0647\u0627 \u0648 \u0645\u062d\u0631\u06a9\u200c\u0647\u0627\u06cc GIN \u0627\u0633\u062a.<\/p>\n

\n

PostgreSQL \u0628\u0647\u0628\u0648\u062f\u0647\u0627\u06cc \u0642\u0627\u0628\u0644 \u062a\u0648\u062c\u0647\u06cc \u0631\u0627 \u0641\u0631\u0627\u062a\u0631 \u0627\u0632 \u0646\u0645\u0627\u06cc\u0647 \u0633\u0627\u0632\u06cc \u062a\u06a9 \u0633\u062a\u0648\u0646\u06cc \u0627\u0631\u0627\u0626\u0647 \u0645\u06cc \u062f\u0647\u062f \u06a9\u0647 YugabyteDB \u0646\u06cc\u0632 \u0627\u0632 \u0622\u0646 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0645\u06cc \u06a9\u0646\u062f. \u0628\u0631\u0627\u06cc \u062c\u0633\u062a\u062c\u0648\u0647\u0627\u06cc\u06cc \u06a9\u0647 \u0645\u0642\u062f\u0627\u0631 \u06a9\u0627\u0645\u0644 \u0622\u0646 \u0645\u0634\u062e\u0635 \u0646\u06cc\u0633\u062a\u060c \u067e\u0633\u0648\u0646\u062f pg_trgm \u0645\u06cc\u200c\u062a\u0648\u0627\u0646\u062f \u0686\u0646\u062f\u06cc\u0646 \u062a\u0631\u06cc\u06af\u0631\u0627\u0645 \u0627\u06cc\u062c\u0627\u062f \u06a9\u0646\u062f \u0648 GIN \u0627\u06cc\u0646 \u0645\u0642\u0627\u062f\u06cc\u0631 \u0686\u0646\u062f\u06af\u0627\u0646\u0647 \u0631\u0627 \u062f\u0631 \u0647\u0631 \u0631\u062f\u06cc\u0641 \u0641\u0647\u0631\u0633\u062a \u0645\u06cc\u200c\u06a9\u0646\u062f. \u0639\u0644\u0627\u0648\u0647 \u0628\u0631 \u0627\u06cc\u0646\u060c \u0627\u0633\u06a9\u0646 \u0628\u06cc\u062a \u0645\u067e \u0645\u06cc\u200c\u062a\u0648\u0627\u0646\u062f \u0646\u0645\u0627\u06cc\u0647\u200c\u0647\u0627 \u0631\u0627 \u062f\u0631 \u0633\u062a\u0648\u0646\u200c\u0647\u0627\u06cc \u0645\u062e\u062a\u0644\u0641 \u062f\u0631 \u06cc\u06a9 \u062c\u062f\u0648\u0644 \u062a\u0631\u06a9\u06cc\u0628 \u06a9\u0646\u062f. \u0628\u0627 \u0627\u06cc\u0646 \u062d\u0627\u0644\u060c \u0686\u0627\u0644\u0634\u200c\u0647\u0627 \u0632\u0645\u0627\u0646\u06cc \u0628\u0647 \u0648\u062c\u0648\u062f \u0645\u06cc\u200c\u0622\u06cc\u0646\u062f \u06a9\u0647 \u06af\u0632\u0627\u0631\u0647\u200c\u0647\u0627 \u062f\u0631 \u062c\u062f\u0648\u0644\u200c\u0647\u0627\u06cc \u0645\u062e\u062a\u0644\u0641 \u062f\u0631 \u06cc\u06a9 \u0627\u062a\u0635\u0627\u0644 \u0642\u0631\u0627\u0631 \u0645\u06cc\u200c\u06af\u06cc\u0631\u0646\u062f. \u062f\u0631 \u0627\u06cc\u0646\u062c\u0627\u060c \u062a\u0631\u06cc\u06af\u0631\u0647\u0627 \u0648\u0627\u0631\u062f \u0639\u0645\u0644 \u0645\u06cc\u200c\u0634\u0648\u0646\u062f \u0648 \u0628\u0627 \u062d\u0641\u0638 \u0633\u062a\u0648\u0646\u200c\u0647\u0627\u06cc \u062a\u0639\u0631\u06cc\u0641\u200c\u0634\u062f\u0647 \u062a\u0648\u0633\u0637 \u06a9\u0627\u0631\u0628\u0631 \u06a9\u0647 \u0628\u0647 \u062c\u0633\u062a\u062c\u0648\u0647\u0627\u06cc \u0641\u0627\u0632\u06cc \u0627\u062e\u062a\u0635\u0627\u0635 \u062f\u0627\u062f\u0647 \u0634\u062f\u0647\u200c\u0627\u0646\u062f\u060c \u0627\u0645\u06a9\u0627\u0646 \u0627\u06cc\u062c\u0627\u062f \u0646\u0645\u0627\u06cc\u0647\u200c\u0633\u0627\u0632\u06cc \u0633\u0641\u0627\u0631\u0634\u06cc \u0631\u0627 \u0641\u0631\u0627\u0647\u0645 \u0645\u06cc\u200c\u06a9\u0646\u0646\u062f.<\/p>\n

\u062f\u0631 \u0627\u06cc\u0646\u062c\u0627 \u0645\u062b\u0627\u0644\u06cc \u0628\u0627 \u0627\u0633\u062a\u0641\u0627\u062f\u0647 \u0627\u0632 \u062c\u062f\u0648\u0644\u06cc \u0627\u0632 \u0634\u0647\u0631\u0647\u0627 \u0648 \u06a9\u0634\u0648\u0631\u0647\u0627 \u0627\u0631\u0627\u0626\u0647 \u0634\u062f\u0647 \u0627\u0633\u062a \u06a9\u0647 \u0645\u0646 \u0627\u0632 gvenzl\/sample-data \u0645\u06cc\u200c\u0633\u0627\u0632\u0645:<\/p>\n

\n
\\<\/span>!<\/span> curl<\/span> -<\/span>s<\/span> https<\/span>:<\/span>\/\/<\/span>raw<\/span>.<\/span>githubusercontent<\/span>.<\/span>com<\/span>\/<\/span>gvenzl<\/span>\/<\/span>sample<\/span>-<\/span>data<\/span>\/<\/span>main<\/span>\/<\/span>countries<\/span>-<\/span>cities<\/span>-<\/span>currencies<\/span>\/<\/span>uninstall<\/span>.<\/span>sql<\/span>  ><\/span> countries<\/span>.<\/span>sql<\/span>\n\\<\/span>!<\/span> curl<\/span> -<\/span>s<\/span> https<\/span>:<\/span>\/\/<\/span>raw<\/span>.<\/span>githubusercontent<\/span>.<\/span>com<\/span>\/<\/span>gvenzl<\/span>\/<\/span>sample<\/span>-<\/span>data<\/span>\/<\/span>main<\/span>\/<\/span>countries<\/span>-<\/span>cities<\/span>-<\/span>currencies<\/span>\/<\/span>install<\/span>.<\/span>sql<\/span> |<\/span> awk<\/span> '\/D a t a    l o a d\/{print \"do $$ begin\"}\/ COMMIT \/{print \"end; $$ ;\"}{print}'<\/span> >><\/span> countries<\/span>.<\/span>sql<\/span>\n\\<\/span>i<\/span> countries<\/span>.<\/span>sql<\/span>\n\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\u0645\u06cc\u200c\u062e\u0648\u0627\u0647\u0645 \u0634\u0647\u0631\u0647\u0627\u06cc\u06cc \u0631\u0627 \u0641\u0647\u0631\u0633\u062a \u06a9\u0646\u0645 \u06a9\u0647 \u062f\u0631 \u0646\u0627\u0645 \u0634\u0647\u0631 \u06cc\u0627 \u0646\u0627\u0645 \u06a9\u0634\u0648\u0631\u0634\u0627\u0646 “\u0628\u0646\u062f\u0631” \u0648\u062c\u0648\u062f \u062f\u0627\u0631\u062f:<\/p>\n
\n
yugabyte<\/span>=#<\/span> select<\/span> city<\/span>.<\/span>name<\/span>,<\/span> country<\/span>.<\/span>name<\/span>\nfrom<\/span> cities<\/span> city<\/span>\njoin<\/span> countries<\/span> country<\/span> using<\/span>(<\/span>country_id<\/span>)<\/span>\nwhere<\/span> city<\/span>.<\/span>name<\/span> ilike<\/span> '%port%'<\/span> or<\/span> country<\/span>.<\/span>name<\/span> ilike<\/span> '%port%'<\/span>;<\/span>\n\n      name<\/span>      |<\/span>        name<\/span>\n----------------+---------------------<\/span>\n Port<\/span> Moresby<\/span>   |<\/span> Papua<\/span> New<\/span> Guinea<\/span>\n Port<\/span> of<\/span> Spain<\/span>  |<\/span> Trinidad<\/span> and<\/span> Tobago<\/span>\n Port<\/span> au<\/span> Prince<\/span> |<\/span> Haiti<\/span>\n Porto<\/span> Novo<\/span>     |<\/span> Benin<\/span>\n Port<\/span> Vila<\/span>      |<\/span> Vanuatu<\/span>\n Port<\/span> Louis<\/span>     |<\/span> Mauritius<\/span>\n Lisbon<\/span>         |<\/span> Portugal<\/span>\n(<\/span>7<\/span> rows<\/span>)<\/span>\n<\/code><\/pre>\n
\n
\n    \u0648\u0627\u0631\u062f \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n
    \u0627\u0632 \u062d\u0627\u0644\u062a \u062a\u0645\u0627\u0645 \u0635\u0641\u062d\u0647 \u062e\u0627\u0631\u062c \u0634\u0648\u06cc\u062f<\/title>\n    \n<\/svg><\/p>\n<\/div>\n<\/div>\n<\/div>\n
\u06a9\u062f\u0627\u0645 \u0634\u0627\u062e\u0635 \u0628\u0631\u0627\u06cc \u062a\u0633\u0631\u06cc\u0639 \u062f\u0631 \u0627\u06cc\u0646 \u062c\u0633\u062a\u062c\u0648 \u0627\u06cc\u062c\u0627\u062f \u0634\u0648\u062f\u061f<\/p>\n
\n
\n
\u0641\u0647\u0631\u0633\u062a \u0645\u0637\u0627\u0644\u0628<\/p>\nToggle<\/span><\/path><\/svg><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n