전문 검색(역색인)

-- 텍스트를 tsvector로 바꾼다 (PostgreSQL 문서의 예)
SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
-- 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4

-- tsquery로 검색: @@ 연산자 (결과 t)
SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats')
       @@ to_tsquery('english', 'fat & rat');

-- tsvector 생성 열과 GIN 인덱스
CREATE TABLE docs (
    id   int PRIMARY KEY,
    body text,
    tsv  tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);
CREATE INDEX docs_tsv_idx ON docs USING GIN (tsv);

SELECT id FROM docs WHERE tsv @@ to_tsquery('english', 'cat');

-- 관련도 순으로 정렬
SELECT id, ts_rank(tsv, query) AS rank
FROM docs, to_tsquery('english', 'fat & cat') AS query
WHERE tsv @@ query
ORDER BY rank DESC;

-- LIKE '%word%' 같은 부분 문자열 검색은 pg_trgm 확장의 인덱스
CREATE EXTENSION pg_trgm;
CREATE INDEX docs_body_trgm ON docs USING GIN (body gin_trgm_ops);

docs = {1: 'fat cat sat', 2: 'fat rat ran', 3: 'cat ate rat', 4: 'big fat cat',
        5: 'dog ran fast', 6: 'big dog sat', 7: 'cat ran fast', 8: 'fast dog'}

index = {}
for doc_id, text in docs.items():
    for word in text.split():
        index.setdefault(word, []).append(doc_id)       # 단어 -> 문서 목록(posting list)

for word in sorted(index):
    print(word, index[word])

def search_and(*words):
    result = set(index.get(words[0], []))
    for w in words[1:]:
        result &= set(index.get(w, []))                  # 문서 목록의 교집합
    return sorted(result)

print('cat:', search_and('cat'))
print('fat & cat:', search_and('fat', 'cat'))

scanned = 0
like = []
for doc_id, text in docs.items():                        # LIKE '%cat%' : 문서를 하나씩 검사
    scanned += 1
    if 'cat' in text:
        like.append(doc_id)
print('LIKE:', like, '검사한 문서', scanned)
# ate [3]  big [4, 6]  cat [1, 3, 4, 7]  dog [5, 6, 8]  fast [5, 7, 8]
# fat [1, 2, 4]  ran [2, 5, 7]  rat [2, 3]  sat [1, 6]  (한 줄에 하나씩 출력)
# cat: [1, 3, 4, 7]
# fat & cat: [1, 4]
# LIKE: [1, 3, 4, 7] 검사한 문서 8

Map<Integer, String> docs = new TreeMap<>();
docs.put(1, "fat cat sat"); docs.put(2, "fat rat ran");
docs.put(3, "cat ate rat"); docs.put(4, "big fat cat");
docs.put(5, "dog ran fast"); docs.put(6, "big dog sat");
docs.put(7, "cat ran fast"); docs.put(8, "fast dog");

Map<String, TreeSet<Integer>> index = new TreeMap<>();         // 단어는 정렬된 순서로 보관
for (Map.Entry<Integer, String> e : docs.entrySet())
    for (String w : e.getValue().split(" "))
        index.computeIfAbsent(w, k -> new TreeSet<>()).add(e.getKey());
System.out.println(index);

TreeSet<Integer> both = new TreeSet<>(index.get("fat"));
both.retainAll(index.get("cat"));                              // 문서 목록의 교집합
System.out.println("fat & cat: " + both);

int scanned = 0;
List<Integer> like = new ArrayList<>();
for (Map.Entry<Integer, String> e : docs.entrySet()) {                // LIKE '%cat%' : 문서를 하나씩 검사
    scanned++;
    if (e.getValue().contains("cat")) like.add(e.getKey());
}
System.out.println("LIKE: " + like + " 검사한 문서 " + scanned);
// {ate=[3], big=[4, 6], cat=[1, 3, 4, 7], dog=[5, 6, 8], fast=[5, 7, 8], fat=[1, 2, 4], ran=[2, 5, 7], rat=[2, 3], sat=[1, 6]}
// fat & cat: [1, 4]
// LIKE: [1, 3, 4, 7] 검사한 문서 8