Combinazione di filtri e predicati di ricerca

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.