PostgreSQL에서 한글 전문 검색(BM25) 실현

services:
  paradedb:
    image: paradedb/paradedb:latest
    container_name: paradedb
    environment:
      POSTGRES_USER: paradedb
      POSTGRES_PASSWORD: paradedb
      POSTGRES_DB: paradedb
    ports:
      - "15432:5432"
    volumes:
      - paradedb_data:/var/lib/postgresql/

volumes:
  paradedb_data:

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    category TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

> docker compose up -d
> docker exec -i paradedb psql -U paradedb -d paradedb < setup.sql

> git clone https://bitbucket.org/eunjeon/mecab-ko.git
> cd mecab-ko
> ./configure
> make && make install

> apt install automake libtool -y
> vi /etc/ld.so.conf 
/usr/local/lib

> wget https://bitbucket.org/eunjeon/mecab-ko-dic/downloads/mecab-ko-dic-2.1.1-20180720.tar.gz
> cd mecab-ko-dic/
> ./configure
> make && make install

> echo '아버지가방에들어가신다'|mecab
아버지 NNG,*,F,아버지,*,*,*,*
가 JKS,*,F,가,*,*,*,*
방 NNG,장소,T,방,*,*,*,*
에 JKB,*,F,에,*,*,*,*
들어가 VV,*,F,들어가,*,*,*,*
신다  EP+EC,*,F,신다,Inflect,EP,EC,시/EP/*+ㄴ다/EC/*
EOS

> git clone https://github.com/i0seph/textsearch_ko.git
> cd textsearch_ko
> make USE_PGXS=1
postgres.h 못찾을 경우
> apt install postgresql-server-dev-18
> make USE_PGXS=1 install

> psql -U paradedb
paradedb=# \i ts_mecab_ko.sql

-- 1. ParadeDB BM25 lindera 한글 토크나이저 사용
DROP INDEX IF EXISTS search_idx;
CREATE INDEX search_idx ON articles
USING bm25 (
    id,
    (title::pdb.lindera(korean)),
    (body::pdb.lindera(korean)),
    (category::pdb.lindera(korean))
)
WITH (key_field='id');

-- 2. PostgreSQL GIN 색인(pg_trgm) LIKE 검색 가속화
CREATE EXTENSION IF NOT EXISTS pg_trgm;
DROP INDEX IF EXISTS trgm_title_idx;
DROP INDEX IF EXISTS trgm_body_idx;
CREATE INDEX trgm_title_idx ON articles USING gin (title gin_trgm_ops);
CREATE INDEX trgm_body_idx ON articles USING gin (body gin_trgm_ops);

-- 3. PostgreSQL GIN tsvector 색인(simple 사전 - 한글용 임베디드 사전이 없기 때문에)
DROP INDEX IF EXISTS ts_title_idx;
DROP INDEX IF EXISTS ts_body_idx;
CREATE INDEX ts_title_idx ON articles USING gin (to_tsvector('korean', title));
CREATE INDEX ts_body_idx ON articles USING gin (to_tsvector('korean', body));

-- ANALYZE
ANALYZE articles;

> SELECT '차세대반도체'::pdb.lindera(korean)::text[];
      text
-----------------
 {차세대,반도체}
(1 row)

Time: 0.514 ms

> SELECT '클라우드입문가이드'::pdb.lindera(korean)::text[];
          text
------------------------
 {클라우드,입문,가이드}
(1 row)

Time: 0.393 ms

> SELECT '기계학습을활용한이미지인식'::pdb.lindera(korean)::text[];
                text
------------------------------------
 {기계,학습,을,활용,한,이미지,인식}
(1 row)

Time: 0.374 ms

--- ParadeDB BM25: 디지털 트랜스포메이션 QUERY PLAN  ---
 Aggregate  (cost=46.25..46.26 rows=1 width=8) (actual time=1.834..1.835 rows=1.00 loops=1)
   Buffers: shared hit=48
   ->  Custom Scan (ParadeDB Scan) on articles  (cost=10.00..34.16 rows=4833 width=0) (actual time=1.150..1.604 rows=4799.00 loops=1)
         Table: articles
         Index: search_idx
         Segment Count: 2
         Heap Fetches: 66
         Exec Method: NormalScanExecState
         Scores: false
         Tantivy Query: {"with_index":{"query":{"match":{"field":"body","value":"디지털트랜스포메이션","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
         Buffers: shared hit=48
 Planning:
   Buffers: shared hit=43
 Planning Time: 0.594 ms
 Execution Time: 1.926 ms
(15 rows)

Time: 2.822 ms

--- PostgreSQL LIKE: 디지털 트랜스포메이션 QUERY PLAN ---
 Aggregate  (cost=727.52..727.53 rows=1 width=8) (actual time=11.848..11.849 rows=1.00 loops=1)
   Buffers: shared hit=623
   ->  Seq Scan on articles  (cost=0.00..724.38 rows=1257 width=0) (actual time=0.080..11.765 rows=1257.00 loops=1)
         Filter: (body ~~ '%디지털 트랜스포메이션%'::text)
         Rows Removed by Filter: 6855
         Buffers: shared hit=623
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.541 ms
 Execution Time: 11.875 ms
(10 rows)

Time: 12.859 ms

--- PostgreSQL tsvector: 디지털트랜스포메이션 QUERY PLAN ---
 Aggregate  (cost=382.44..382.45 rows=1 width=8) (actual time=395.779..395.780 rows=1.00 loops=1)
   Buffers: shared hit=549
   ->  Bitmap Heap Scan on articles  (cost=35.07..382.13 rows=127 width=0) (actual time=0.903..395.459 rows=1302.00 loops=1)
         Recheck Cond: (to_tsvector('korean'::regconfig, body) @@ '''디지털'' <-> ''트랜스'' <-> ''포메이션'''::tsquery)
         Heap Blocks: exact=540
         Buffers: shared hit=549
         ->  Bitmap Index Scan on ts_body_idx  (cost=0.00..35.04 rows=127 width=0) (actual time=0.373..0.373 rows=1302.00 loops=1)
               Index Cond: (to_tsvector('korean'::regconfig, body) @@ '''디지털'' <-> ''트랜스'' <-> ''포메이션'''::tsquery)
               Index Searches: 1
               Buffers: shared hit=9
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.258 ms
 Execution Time: 395.799 ms
(14 rows)

Time: 96.373 ms

SELECT id, title, pdb.score(id) AS score
FROM articles
WHERE title ||| '인공지능' OR body ||| '인공지능'
ORDER BY score DESC
LIMIT 10;

id | title                              | score
16 | 전이 학습 기반 소량 데이터 객체 인식 개선	  | 10.6050415
3	 | 강화학습 기반 게임 AI 의사결정 효율 개선	   | 10.563667
20 | LLM 할루시네이션 방지를 위한 RAG 적용	   | 10.5226145
9	 | 시계열 분석 기반 주가 예측 오차율 감소	     | 10.44146
12 | 능동 학습을 활용한 데이터 라벨링 비용 절감	  | 10.44146

SELECT title, pdb.snippet(body, start_tag => '【', end_tag => '】') AS highlighted
FROM articles
WHERE body ||| '기계학습'
ORDER BY pdb.score(id) DESC
LIMIT 3;

title                |     highlighted
한국의농림수산업에서기술혁신 | 대응과 글로벌 경쟁력 유지라는 두가지 큰 과제에 직면하고 있다. 인공 지능과 【기계】 【학습】 기술은 이러한 과제에 
지속가능농업생산의국제 비교	| 대응과 글로벌 경쟁력 유지라는 두가지 큰 과제에 직면하고 있다. 인공 지능과 【기계】 【학습】 기술은 이러한 과제에 
자동차업계의디지털 변혁	   | 대응과 글로벌 경쟁력 유지라는 두가지 큰 과제에 직면하고 있다. 인공 지능과 【기계】 【학습】 기술은 이러한 과제에 

-- OR
SELECT id, title FROM articles
WHERE body ||| '우주 탐사';

-- AND
SELECT id, title FROM articles
WHERE body &&& '신재생에너지 환경문제';

SELECT category, count(*) AS cnt, avg(pdb.score(id))::numeric(10,4) AS avg_score
FROM articles
WHERE body ||| '기술'
GROUP BY category
ORDER BY cnt DESC;