0Pricing
PostgreSQL Performance & Query Optimization · 강의

필터와 검색 조건 결합

텍스트 검색과 구조화된 WHERE 조건을 함께 사용하는 쿼리를 효율적으로 인덱싱하고 계획하는 방법을 배웁니다.

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

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

The Mixed-Predicate Problem

Real search queries rarely use only text matching. A user searches for "wireless headphones" but also filters by category = 'electronics', price < 200, and in_stock = true.

This mixes a full-text/trigram predicate with one or more structured WHERE conditions. The challenge: how does PostgreSQL combine these, and how do you index so that both parts stay fast?

  • Text search wants a GIN index (FTS or trigram).
  • Structured filters want a B-tree index.
  • Combining them naively can lose the benefit of either.

How PostgreSQL Combines Two Indexes

When a query has predicates served by two separate indexes, the planner can use a BitmapAnd. Each index produces a bitmap of matching rows, and the bitmaps are intersected before fetching from the heap.

This is powerful but not free: building two bitmaps and ANDing them costs CPU, and you still re-check conditions on the heap. For very selective combinations it shines; for cheap filters it can be overkill.

The plan below shows the shape you want to recognize.

EXPLAIN ANALYZE
SELECT id, title
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphones')
  AND category = 'electronics';

-- Look for:
--   BitmapAnd
--     -> Bitmap Index Scan on products_search_gin
--     -> Bitmap Index Scan on products_category_idx

Selectivity Drives the Plan

The planner's choice hinges on selectivity — what fraction of rows each predicate keeps.

  • If the text predicate is highly selective (matches 50 of 5M rows), let the GIN index lead and filter the rest on the heap.
  • If the structured filter is highly selective (one tiny tenant_id), it may be cheaper to scan that B-tree first and re-check text as a heap filter.
  • When both are moderately selective, a BitmapAnd of both indexes usually wins.

Accurate statistics (via ANALYZE) are what let the planner estimate this correctly.

Composite GIN with btree_gin

Instead of relying on BitmapAnd across two indexes, you can put both a scalar column and a tsvector into a single GIN index using the btree_gin extension.

This lets one index scan satisfy both the text predicate and an equality filter, avoiding the cost of intersecting two bitmaps.

It is ideal when a specific low-cardinality column (like category or status) almost always accompanies the search.

CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX products_cat_search_gin
ON products
USING gin (category, search_vector);

-- Now this can be served by ONE index scan:
SELECT id, title
FROM products
WHERE category = 'electronics'
  AND search_vector @@ to_tsquery('english', 'wireless & headphones');

When btree_gin Helps and When It Hurts

A composite GIN index is not always the right call.

  • Helps when the scalar column is low cardinality and frequently combined with text search — the planner skips the BitmapAnd overhead.
  • Hurts when the scalar column is high cardinality (like a unique id): GIN entries balloon, the index grows large, and inserts slow down.
  • GIN indexes are generally slower to update than B-tree, so adding more columns increases write amplification.

Rule of thumb: composite-GIN the column you always filter on; leave rarely-used filters to a separate B-tree + BitmapAnd.

Partial Indexes for Hot Filters

If most searches target a specific subset — say only status = 'active' rows — a partial index bakes that filter into the index itself.

The index is smaller (only active rows), faster to scan, and the filter condition disappears from runtime work because the planner knows the index already satisfies it.

CREATE INDEX products_active_search_gin
ON products
USING gin (search_vector)
WHERE status = 'active';

-- The planner uses this index ONLY when the query
-- includes a matching WHERE status = 'active':
SELECT id, title
FROM products
WHERE status = 'active'
  AND search_vector @@ to_tsquery('english', 'wireless');

Range Filters Need Care

Equality filters (category = 'x') combine cleanly with GIN via btree_gin. Range filters (price BETWEEN ..., created_at > ...) are trickier.

  • btree_gin supports range operators on the leading scalar column, but GIN does not order results, so it cannot exploit ranges as efficiently as a B-tree.
  • Often the best plan is a BitmapAnd of a GIN (text) index and a separate B-tree (range) index.
  • If the range is the more selective predicate, a B-tree-led plan with a re-check on the text vector can beat GIN entirely.

Reading a BitmapAnd Plan

To verify your indexing strategy, read the actual plan. A good combined plan shows two Bitmap Index Scans feeding a BitmapAnd, then a single Bitmap Heap Scan.

Watch the Rows Removed by Filter and the estimated vs actual rows: a large gap means stale statistics or a poor cardinality estimate that misled the planner.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, price
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphones')
  AND price < 200
  AND category = 'electronics';

-- Healthy shape:
--   Bitmap Heap Scan on products
--     Recheck Cond: ...
--     -> BitmapAnd
--          -> Bitmap Index Scan on products_search_gin
--          -> Bitmap Index Scan on products_price_idx

Trigram Search with Filters

For fuzzy / substring matching you use pg_trgm with a GIN (or GiST) index on the text column. The same combination rules apply.

A trigram ILIKE '%term%' or % similarity predicate produces a bitmap that can be ANDed with a B-tree bitmap from your structured filter.

As with FTS, if a scalar filter is nearly always present, fold it into a composite or partial GIN index.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX products_name_trgm
ON products
USING gin (name gin_trgm_ops);

SELECT id, name
FROM products
WHERE name ILIKE '%headphn%'   -- fuzzy / typo-tolerant
  AND category = 'electronics';

Ordering by Relevance After Filtering

Combining filters with ORDER BY ts_rank(...) adds another cost: ranking requires fetching matching rows and computing a score, then sorting.

  • Filter first so ranking runs over the smallest possible candidate set.
  • ts_rank cannot be served from a GIN index — it always re-reads the tsvector from the heap.
  • For top-N relevance queries on large result sets, consider a GiST/RUM index, or limit candidates with selective filters before ranking.
SELECT id, title,
       ts_rank(search_vector, query) AS rank
FROM products,
     to_tsquery('english', 'wireless & headphones') AS query
WHERE search_vector @@ query
  AND category = 'electronics'
  AND price < 200
ORDER BY rank DESC
LIMIT 10;

A Practical Decision Checklist

When you mix text search with structured filters, work through this:

  • Which predicate is most selective? Let the most selective one drive the index strategy.
  • Is one scalar filter always present? Use a composite btree_gin or a partial GIN index.
  • Are the filters independent and both moderately selective? Keep separate indexes and trust BitmapAnd.
  • Stale estimates? Run ANALYZE; consider raising statistics_target on key columns.
  • Ranking? Filter before ranking; never rank the whole table.

Quick Check

Test your understanding of combining a frequently-used equality filter with a full-text search predicate.

Recap

You learned how to make queries that mix text search with structured filters fast:

  • BitmapAnd combines two separate indexes by intersecting their bitmaps — great when both predicates are moderately selective.
  • btree_gin folds a scalar column into the GIN index so one scan serves text + equality, removing BitmapAnd overhead.
  • Partial indexes bake a hot filter into a smaller, faster index.
  • Range filters usually pair best with a separate B-tree via BitmapAnd; let the most selective predicate lead.
  • Always filter before ranking, and keep statistics fresh with ANALYZE so the planner estimates selectivity correctly.

자주 묻는 질문

“필터와 검색 조건 결합” 강의는 무료인가요?

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

“필터와 검색 조건 결합”에서 뭘 배우나요?

텍스트 검색과 구조화된 WHERE 조건을 함께 사용하는 쿼리를 효율적으로 인덱싱하고 계획하는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

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

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

“필터와 검색 조건 결합” 강의는 얼마나 걸리나요?

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

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

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

이 강의의 모든 강의

  1. tsvector 열과 GIN 인덱스 설계
  2. ts_rank를 사용한 순위 및 관련성 조정
  3. pg_trgm 유사도를 활용한 퍼지 매칭
  4. 필터와 검색 조건 결합
← PostgreSQL Performance & Query Optimization(으)로 돌아가기