PostgreSQL / Бүтэн текст хайлт

Бүтэн текст хайлт

WHERE title LIKE '%postgresql%' гэж хайхад олон асуудал гардаг — том/жижиг үсгийн ялгаа, үгийн өөр хэлбэр (run, running, ran), хамаарлын оноо байхгүй. PostgreSQL-н full-text search нь эдгээр бүгдийг шийдсэн, хурдан, хүчирхэг хайлтын тогтолцоо юм. Тусгай индекстэй хослуулбал хэдэн сая мөрөөс хэдхэн миллисекундэд хайх боломжтой.

tsvector ба tsquery — үндэс ойлголт

Full-text search хоёр тусгай өгөгдлийн төрөл дээр суурилдаг:

  • tsvector — текстийг задлан, үгийн үндэс хэлбэрт (lexeme) хөрвүүлж хадгалдаг
  • tsquery — хайлтын нөхцөлийг илэрхийлдэг
sql
-- tsvector: текстийг хайлтанд бэлтгэх
SELECT TO_TSVECTOR('english', 'PostgreSQL is a powerful open source database system');
-- Үр дүн: 'databas':7 'open':5 'postg':1 'power':3 'sourc':6 'system':8

-- tsquery: хайлтын нөхцөл
SELECT TO_TSQUERY('english', 'powerful & database');
-- Үр дүн: 'power' & 'databas'

-- Тохирч байна уу?
SELECT TO_TSVECTOR('english', 'PostgreSQL is a powerful database') @@
       TO_TSQUERY('english', 'powerful & database');
-- Үр дүн: true

TO_TSVECTOR() нь stopword (is, a, the гэх мэт) хасаж, үгийг үндэс хэлбэрт (running → run) хөрвүүлдэг. @@ оператор нь tsvector ба tsquery тохирч байна уу гэдгийг шалгана.

Хүснэгтэд full-text search тавих

sql
CREATE TABLE articles (
    id        SERIAL PRIMARY KEY,
    title     TEXT NOT NULL,
    body      TEXT NOT NULL,
    author    TEXT NOT NULL,
    published BOOLEAN DEFAULT false
);

INSERT INTO articles (title, body, author, published) VALUES
    ('PostgreSQL индекс гарын авлага',
     'Индекс бол өгөгдлийн санг хурдасгах хамгийн чухал хэрэгсэл юм. B-tree, GIN, GiST төрлүүд байдаг.',
     'Болд', true),
    ('Transaction ба ACID баталгаа',
     'Банкны систем бол transaction-г зөв хэрэгжүүлэх хамгийн сайн жишээ юм.',
     'Сарнай', true),
    ('PostgreSQL-н window функцүүд',
     'Window функц бол GROUP BY-аас огт өөр аргаар нэгтгэх боломж олгодог.',
     'Болд', true),
    ('SQL JOIN-уудыг ойлгох',
     'INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN хоорондын ялгааг жишээгээр авч үзнэ.',
     'Ганбаяр', false);

-- tsvector баганыг урьдчилан тооцоолж хадгалах
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

UPDATE articles
SET search_vector = TO_TSVECTOR('russian',
    COALESCE(title, '') || ' ' || COALESCE(body, '') || ' ' || COALESCE(author, ''));

Монгол текстэд тохирсон dictionary байхгүй тул 'russian' ашигладаг — кирилл дэмждэг хамгийн ойрын тохиргоо юм. 'simple' ч ашиглаж болно.

GIN индекс ба хайлт

sql
-- GIN индекс үүсгэх
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Хайлт хийх
SELECT title, author
FROM articles
WHERE search_vector @@ TO_TSQUERY('russian', 'индекс')
AND published = true;

-- Олон үгээр хайх (& = ба, | = эсвэл, ! = биш)
SELECT title FROM articles
WHERE search_vector @@ TO_TSQUERY('russian', 'postgresql & индекс');

SELECT title FROM articles
WHERE search_vector @@ TO_TSQUERY('russian', 'transaction | window');

TS_RANK — хамаарлын оноо

TS_RANK() нь хайлтын үр дүнг хамаарлаар эрэмбэлж болгодог — хайлтын системийн чухал онцлог:

sql
SELECT
    title,
    author,
    TS_RANK(search_vector, TO_TSQUERY('russian', 'postgresql')) AS оноо
FROM articles
WHERE search_vector @@ TO_TSQUERY('russian', 'postgresql')
ORDER BY оноо DESC;

Trigger-ээр автомат шинэчлэлт

Шинэ нийтлэл нэмэгдэх бүрт search_vector-г автоматаар шинэчлэх:

sql
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    NEW.search_vector := TO_TSVECTOR('russian',
        COALESCE(NEW.title, '') || ' ' ||
        COALESCE(NEW.body,  '') || ' ' ||
        COALESCE(NEW.author,''));
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_articles_search
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- Одоо автоматаар шинэчлэгдэнэ
INSERT INTO articles (title, body, author, published)
VALUES ('Шинэ нийтлэл', 'PostgreSQL trigger маш хэрэгтэй.', 'Номин', true);

Дараагийн хичээлд:

Хэдэн жилийн өгөгдөл хуримтлагдсан том хүснэгтийг огноо эсвэл утгаар нь хэд хэдэн хэсэгт хуваах partitioning аргыг сурна.

← Өмнөх хичээлМассив өгөгдлийн төрөлДараах хичээл →Хүснэгт хуваалт (Partitioning)