0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Combining Filters with Search Predicates

Index and plan queries that mix text search with structured WHERE conditions efficiently.

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

All lessons in this course

  1. Designing tsvector Columns and GIN Indexes
  2. Ranking and Relevance Tuning with ts_rank
  3. Fuzzy Matching with pg_trgm Similarity
  4. Combining Filters with Search Predicates
← Back to PostgreSQL Performance & Query Optimization