PostgreSQL Performance & Query Optimization · 강의

ts_rank를 사용한 순위 및 관련성 조정

문서 섹션에 가중치를 부여하고 순위 함수를 조정하여 가장 관련성 높은 결과를 먼저 표시하는 방법을 배웁니다.

ts_rank를 사용한 순위 및 관련성 조정은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 2번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

Why Ranking Matters

A full-text query with @@ only tells you whether a document matches a query, not how well. To surface the most relevant rows first, you need a ranking function.

PostgreSQL ships two: ts_rank (frequency-based) and ts_rank_cd (cover-density, considers term proximity). Both return a real score you sort by.

Matching is binary, fast, and index-backed.
Ranking is a separate, more expensive computation done on the matched rows.

SELECT title,
       ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'index & performance') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC
LIMIT 10;

How ts_rank Scores

ts_rank bases its score on term frequency: how often the query lexemes appear in the document, and their assigned weights. More occurrences of a query term generally means a higher score.

Critically, the rank is computed against the tsvector, which stores lexeme positions. A document where the term appears 5 times outranks one where it appears once, all else equal.

ts_rank ignores how close terms are to each other.
ts_rank_cd rewards documents where query terms cluster together.

Weight Labels A, B, C, D

Each lexeme position in a tsvector can carry a weight label: A, B, C, or D. Use setweight() to tag different document sections so a match in the title counts more than a match in the body.

D is the default (lowest). The convention is: A = title, B = abstract/summary, C = body, D = comments or metadata.

You build a weighted vector by concatenating setweight() calls with ||.

SELECT setweight(to_tsvector('english', 'PostgreSQL Indexing'), 'A') ||
       setweight(to_tsvector('english', 'A guide to fast queries'), 'B') ||
       setweight(to_tsvector('english', 'Detailed body text about GIN indexes'), 'C');

Storing a Weighted tsvector

For performance, precompute the weighted tsvector into a generated column and index it with GIN. This means ranking and matching both run against the same weighted vector, and you never re-tokenize at query time.

The generated column recomputes automatically when title or body changes, so it stays consistent.

ALTER TABLE articles
  ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body, '')),  'C')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_vec);

Tuning Weights with the Array

ts_rank accepts an optional first argument: a 4-element float4[] of multipliers for labels in the order {D, C, B, A}. Note the order — it runs D first, A last.

The default array is {0.1, 0.2, 0.4, 1.0}. Raise the A multiplier to make title matches dominate even more, or flatten the array to reduce the impact of section weighting.

SELECT title,
       ts_rank('{0.1, 0.2, 0.4, 1.0}', search_vec, query) AS rank
FROM articles, to_tsquery('english', 'gin & index') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 10;

Length Normalization

By default ts_rank does not normalize for document length, so long documents can accumulate higher scores simply by being long. The optional final integer argument controls normalization via bit flags you sum together.

0 — ignore length (default)
1 — divide rank by 1 + log(length)
2 — divide rank by length
4 — divide by mean harmonic distance (cd only)
8 — divide by number of unique words
16 — divide by 1 + log(unique words)
32 — divide by itself + 1 (maps rank into [0,1))

Applying Normalization

Flag 1 is the most common choice: it gently penalizes long documents using a logarithm so a 2000-word article doesn't crush a focused 200-word one. Combine flags by summing them, e.g. 1|32 = 33 to also map into [0,1).

A normalized-to-[0,1) score is convenient when you want to blend full-text rank with other signals like recency or popularity.

SELECT title,
       ts_rank(search_vec, query, 1) AS rank_lognorm,
       ts_rank(search_vec, query, 33) AS rank_0_to_1
FROM articles, to_tsquery('english', 'query & optimization') query
WHERE search_vec @@ query
ORDER BY rank_lognorm DESC
LIMIT 10;

ts_rank_cd for Phrase Proximity

ts_rank_cd implements cover density ranking: it rewards documents where the query lexemes appear close together. This needs positional information, so it only works on a tsvector that still has positions (not stripped).

For queries like "query planner" where adjacency signals relevance, ts_rank_cd usually beats plain ts_rank. It accepts the same weight array and normalization arguments.

SELECT title,
       ts_rank_cd(search_vec, query, 1) AS cd_rank
FROM articles,
     phraseto_tsquery('english', 'query planner') query
WHERE search_vec @@ query
ORDER BY cd_rank DESC
LIMIT 10;

The Two-Phase Performance Pattern

Ranking is CPU-bound and runs per matched row, so never let it run over millions of rows. The winning pattern is two-phase: filter cheaply with the GIN index, then rank only the survivors.

Push the @@ match (index-backed) into a subquery or CTE, optionally with a coarse LIMIT, then compute ts_rank on that small candidate set.

The index narrows millions to thousands.
ts_rank then sorts only thousands.

WITH candidates AS (
  SELECT id, title, search_vec
  FROM articles
  WHERE search_vec @@ to_tsquery('english', 'index & tuning')
  LIMIT 500
)
SELECT id, title,
       ts_rank(search_vec, to_tsquery('english', 'index & tuning')) AS rank
FROM candidates
ORDER BY rank DESC
LIMIT 10;

Ranking Is Not Indexable

A common misconception: that a GIN index can satisfy ORDER BY ts_rank(...). It cannot. GIN indexes accelerate the @@ membership test, but ts_rank is a black-box function whose value isn't stored in the index, so PostgreSQL must compute it and then sort.

If ranking sort is a bottleneck, options include: precomputing a static quality score column, using RUM indexes (an extension that can return rows in rank order), or capping the candidate set first.

Blending Rank with Business Signals

Pure text rank rarely matches product intuition. Blend the normalized text score with signals like recency and popularity to compute a final ordering. Because flag 32 maps text rank into [0,1), it composes cleanly with other normalized factors.

Keep the @@ filter index-backed; the blend math only runs on matched candidate rows.

SELECT id, title,
       ts_rank(search_vec, query, 32) AS text_score,
       ts_rank(search_vec, query, 32) * 0.7
         + (1.0 / (1 + extract(epoch FROM now() - created_at) / 86400)) * 0.3
         AS final_score
FROM articles, to_tsquery('english', 'postgres & performance') query
WHERE search_vec @@ query
ORDER BY final_score DESC
LIMIT 10;

Quick Check

You rank search results over a 5-million-row table and the query is slow. EXPLAIN shows a Bitmap Index Scan on the GIN index followed by a Sort on ts_rank(...). What is the most effective fix?

Recap

You learned to tune full-text relevance in PostgreSQL:

ts_rank scores by term frequency; ts_rank_cd rewards proximity (needs positions).
Tag sections with setweight() using labels A/B/C/D, and store the weighted vector in a GIN-indexed generated column.
The weight array {D, C, B, A} (default {0.1,0.2,0.4,1.0}) tunes section influence.
The normalization flag controls length penalties; 1 applies a log penalty, 32 maps into [0,1) for blending.
Ranking is not indexable: always filter with @@ first, then rank the small candidate set.

자주 묻는 질문

“ts_rank를 사용한 순위 및 관련성 조정” 강의는 무료인가요?

네 — “ts_rank를 사용한 순위 및 관련성 조정” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“ts_rank를 사용한 순위 및 관련성 조정”에서 뭘 배우나요?

문서 섹션에 가중치를 부여하고 순위 함수를 조정하여 가장 관련성 높은 결과를 먼저 표시하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 2번째 강의입니다.

“ts_rank를 사용한 순위 및 관련성 조정” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← PostgreSQL Performance & Query Optimization(으)로 돌아가기