0Pricing
PostgreSQL Performance & Query Optimization · レッスン

フィルターと検索述語の組み合わせ

テキスト検索と構造化されたWHERE条件を組み合わせたクエリを、効率的にインデックス化して実行計画を立てます。

「フィルターと検索述語の組み合わせ」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応の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時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「フィルターと検索述語の組み合わせ」で何を学びますか？

テキスト検索と構造化されたWHERE条件を組み合わせたクエリを、効率的にインデックス化して実行計画を立てます。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応の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に戻る