PostgreSQL Performance & Query Optimization · 课时

组合筛选条件与搜索谓词

高效地为混合文本搜索和结构化 WHERE 条件的查询建立索引并制定执行计划。

第 4 / 4 课13 个步骤

组合筛选条件与搜索谓词 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 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.

免费开始

用 AI 导师学习 SQL — 免费

在浏览器中编写并运行真实代码，获得全天候 AI 导师的即时帮助，并在网页或应用中继续学习。

课程: 22
课程: 88

免费开始学习查看完整课程

常见问题解答

「组合筛选条件与搜索谓词」课时是免费的吗？

是的 — 「组合筛选条件与搜索谓词」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「组合筛选条件与搜索谓词」这节课中我会学到什么？

高效地为混合文本搜索和结构化 WHERE 条件的查询建立索引并制定执行计划。你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 4 节课，共 4 节。

「组合筛选条件与搜索谓词」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 PostgreSQL Performance & Query Optimization