جست‌وجوی فارسی: full-text با simple، نرمال‌سازی و pg_trgm

جست‌وجویی که «كاشان» عربی و «کاشان» فارسی را یکی بداند

پستگرس برای انگلیسی و چند زبان اروپایی دیکشنری ریشه‌یاب (stemmer) دارد، اما برای فارسی نه. با این حال با پیکربندی simple (فقط شکستن به کلمه و کوچک‌کردن حروف لاتین) به‌علاوه‌ی یک تابع نرمال‌سازی، جست‌وجوی کلمه‌ای خوب و سریعی برای فارسی می‌سازید.

گام ۱: تابع نرمال‌سازی

CREATE OR REPLACE FUNCTION factory.fa_normalize(t text)
RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
AS $$
  SELECT regexp_replace(
           translate(t,
             'يكىۀة۰۱۲۳۴۵۶۷۸۹٠١٢٣٤٥٦٧٨٩' || chr(8204),
             'یکیهه01234567890123456789 '),
           '[ً-ْ]', '', 'g')          -- حذف اعراب
$$;

SELECT factory.fa_normalize('فرش‌هاي كاشان ۱۲۰۰ شانه');  -- فرش های کاشان 1200 شانه

«ي» و «ك» عربی، ارقام فارسی و عربی، نیم‌فاصله (U+200C که با chr(8204) ساخته‌ایم) و اعراب یکسان می‌شوند. نیم‌فاصله به فاصله تبدیل می‌شود تا «فرش‌ها» و «فرش ها» یکی شوند. مهم این است که همین تابع هم روی داده و هم روی عبارت جست‌وجو اعمال شود.

گام ۲: ستون tsvector و ایندکس GIN

تابع array_to_string در کاتالوگ STABLE علامت خورده (چون برای نوع‌های دیگر به تنظیمات نشست وابسته است) و ستون generated فقط تابع IMMUTABLE می‌پذیرد؛ برای آرایه‌ی text یک پوشش IMMUTABLE کوچک می‌سازیم:

CREATE FUNCTION factory.tags_text(text[]) RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT array_to_string($1, ' ') $$;

ALTER TABLE factory.designs
  ADD COLUMN search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', factory.fa_normalize(coalesce(title, ''))), 'A') ||
    setweight(to_tsvector('simple', factory.fa_normalize(factory.tags_text(tags))), 'B')
  ) STORED;

CREATE INDEX designs_search_gin ON factory.designs USING gin (search);

SELECT code, title, ts_rank(search, q) AS rank
FROM factory.designs,
     websearch_to_tsquery('simple', factory.fa_normalize('افشان -ابریشم')) AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

websearch_to_tsquery نحو آشنای موتورهای جست‌وجو را می‌فهمد: عبارت داخل گیومه، OR و منفی با «-»، و هیچ‌وقت به خاطر ورودی کاربر خطای نحوی نمی‌دهد. برای جست‌وجوی پیشوندی (تکمیل خودکار) از to_tsquery('simple', 'کاش:*') استفاده کنید.

گام ۳: pg_trgm برای LIKE '%...%' و غلط تایپی

full-text کلمه‌ی کامل می‌خواهد؛ اما کاربر «شانه۱۲» یا بخشی از کد نقشه را تایپ می‌کند. LIKE '%...%' هیچ B-treeی را استفاده نمی‌کند، مگر ایندکس trigram (سه‌حرفی):

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX customers_name_trgm ON factory.customers
  USING gin (factory.fa_normalize(full_name) gin_trgm_ops);

-- بخشی از نام، با ایندکس
SELECT id, full_name FROM factory.customers
WHERE factory.fa_normalize(full_name) LIKE '%' || factory.fa_normalize('رضای') || '%';

-- جست‌وجوی تقریبی: «محمدرضا کاشانی» با غلط تایپی
SELECT full_name, similarity(factory.fa_normalize(full_name), 'محمد رضا کاشانی') AS sim
FROM factory.customers
WHERE factory.fa_normalize(full_name) % 'محمد رضا کاشانی'
ORDER BY sim DESC LIMIT 5;

نکته‌هایی که کمتر کسی می‌داند

• قبل از هر چیز SELECT show_trgm('فرش'); را اجرا کنید. اگر آرایه‌ی خالی برگشت، LC_CTYPE دیتابیس C است و pg_trgm حروف فارسی را «حرف» به حساب نمی‌آورد؛ دیتابیس را با locale UTF-8 واقعی (مثل en_US.UTF-8 یا ICU) بسازید.
• آستانه‌ی عملگر % پیش‌فرض 0.3 است و با SET pg_trgm.similarity_threshold = 0.45 برای همان نشست تنظیم می‌شود؛ برای نام‌های کوتاه فارسی 0.3 نتایج پرتی زیادی می‌آورد.
• عبارت جست‌وجوی کمتر از سه حرف (مثلاً «رض») در ایندکس trigram عملاً همه‌ی ردیف‌ها را کاندید می‌کند؛ در رابط کاربری حداقل سه حرف بخواهید.
• تابع نرمال‌سازی را اگر بعداً تغییر دهید، ستون generated و ایندکس‌ها خودکار بازسازی نمی‌شوند؛ چون آن را IMMUTABLE اعلام کرده‌اید، پستگرس فرض می‌کند خروجی‌اش هرگز عوض نمی‌شود. بعد از تغییر، ستون را دوباره بسازید و REINDEX کنید.
• برای رتبه‌بندی، ts_rank_cd نزدیکی کلمه‌ها به هم را هم در نظر می‌گیرد؛ «فرش کاشان» کنار هم بالاتر از دو کلمه‌ی دور از هم می‌نشیند.