Every production database eventually meets the query its indexes were not designed for: a WHERE clause combining two or three columns, each of which has its own single-column index, none of which covers the combination. Both MySQL and PostgreSQL have machinery for combining multiple indexes in one scan, MySQL through the index_merge access method and PostgreSQL through bitmap index scans. On paper they solve the same problem. In practice they behave differently enough that the index-design advice for each engine diverges, and a migration is exactly when you should reexamine it.
The problem both features solve
Consider a filter like status = 'pending' AND region = 'eu', or its OR cousin, with separate indexes on status and region. A single-index plan must pick one index, scan every row matching that half of the predicate, and filter the rest row by row. If each condition alone matches millions of rows but the combination matches hundreds, both engines are staring at the same waste, and both reach for the same idea: evaluate each index separately, combine the row pointers, and only then touch the table.
MySQL index_merge in practice
MySQL's index_merge access method has three algorithms, visible in the Extra column of EXPLAIN. Intersection (Using intersect) handles AND conditions by intersecting rowid sets retrieved from multiple indexes. Union (Using union) handles OR conditions across different indexes. Sort-union (Using sort_union) handles OR conditions with range predicates, fetching and sorting row ids before the union. Each algorithm has applicability rules; intersection, for example, wants equality conditions covering the full key of each secondary index involved, with more freedom only on primary key ranges.
EXPLAIN
SELECT id, customer_id
FROM orders
WHERE status = 'pending'
OR region = 'eu';When index_merge works, it works. My operational complaint from running MySQL is that it is hard to rely on. The applicability rules are narrow enough that a small predicate change silently drops the plan back to a single index plus filtering. The cost model around it has known rough edges, and enough teams have been burned by bad intersection choices that disabling index_merge_intersection via optimizer_switch, or steering individual queries with NO_INDEX_MERGE hints, is a recognized tuning move. The standard MySQL advice follows from this: do not design for index_merge; if a multi-column filter matters, build the composite index and treat a merge plan in EXPLAIN as a hint that a better index is missing.
PostgreSQL bitmap scans in practice
PostgreSQL's mechanism is more general because it is built into the executor as ordinary plan nodes. A Bitmap Index Scan reads one index and builds an in-memory bitmap of matching tuple locations. BitmapAnd and BitmapOr nodes combine any number of these bitmaps. A Bitmap Heap Scan then visits the matching heap pages in physical page order, rechecking conditions where needed. Because the combination happens on bitmaps rather than through per-algorithm rules, arbitrary boolean combinations of indexable conditions are fair game, including mixes of AND and OR across different indexes and index types.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id
FROM orders
WHERE status = 'pending'
OR region = 'eu';A representative plan shows a BitmapOr over two Bitmap Index Scans feeding a Bitmap Heap Scan, with a Recheck Cond line on the heap scan. One implementation detail matters operationally: bitmaps live in work_mem. When the bitmap fits, it tracks individual tuples, and EXPLAIN ANALYZE reports exact heap blocks. When it does not fit, PostgreSQL degrades the bitmap to page granularity, the plan reports lossy blocks, and every row on each fetched page must be rechecked against the original condition. A query whose bitmap goes lossy gets slower without changing its plan shape, which makes lossy block counts worth watching, and occasionally makes a work_mem bump a legitimate fix.
The other honest caveat: a bitmap heap scan discards index order. Sorting on top of it is an explicit Sort node, so for ORDER BY queries with LIMIT, a plain index scan on a well-chosen composite index still beats any bitmap combination.
Where the two engines diverge
The practical difference is reliability of the escape hatch. In MySQL, multi-index combination is a special-case access method with narrow entry conditions; in PostgreSQL, it is a first-class plan shape the planner reaches for routinely, costed with the same machinery as everything else. Watching plans on both engines, my experience is that PostgreSQL uses bitmap combination far more often and more predictably, especially for OR predicates, which are index_merge union's territory in MySQL and frequently miss it.
This shows up during migrations in a pleasant way: OR-heavy queries that needed UNION rewrites or resigned themselves to full scans in MySQL often just work on PostgreSQL with the same single-column indexes. It also shows up in a dangerous way: because bitmap scans paper over missing composite indexes so competently, the pressure that forced you to build the right composite index in MySQL is weaker, and mediocre index designs survive longer than they should. A BitmapAnd appearing in a hot query's plan deserves the same reading in PostgreSQL as index_merge intersect did in MySQL: the query runs, and a purpose-built index would run it faster.
Composite index design after the move
The core rules travel intact. Both engines use B-tree multicolumn indexes left to right: equality conditions on leading columns, then at most one range, and columns after the range column stop narrowing the scan. An index on (status, region, created_at) serves status + region + a created_at range beautifully in either engine, and serves a query filtering only on created_at poorly in both.
What changes is the surrounding pressure. On PostgreSQL you can afford fewer, better composite indexes for the proven hot paths, and let bitmap combination carry the long tail of ad hoc filter combinations, which is a real strategy rather than a failure mode. Two PostgreSQL-specific notes sweeten it further: partial indexes let you index only the rows a hot filter cares about, for example WHERE status = 'pending', which is often a better answer than any wide composite; and remember from the storage-design side that every additional index in PostgreSQL taxes updates by defeating HOT, so the minimalism has a second justification. When deciding which composites earn a place against a real workload, an index advisor working from observed query traffic gives you a defensible shortlist instead of a guess.
Diagnosing multi-column filters in each engine
On MySQL, the checklist is: does EXPLAIN show index_merge where you expected it, which algorithm, and is the merge actually cheaper than the composite index you have been avoiding? On PostgreSQL, the equivalent questions come out of EXPLAIN (ANALYZE, BUFFERS): is there a BitmapAnd or BitmapOr in the hot path, how do exact and lossy heap block counts look, how many rows does the Recheck Cond discard, and does the row estimate at the bitmap nodes match reality? Estimate errors matter double here, because multi-column filters are exactly where the planner's column-independence assumption breaks; if the combined estimate is far off, extended statistics on the correlated columns fix the input rather than the symptom. Walking these plans is much faster with a query plan analyzer that surfaces recheck losses and estimate divergence per node instead of leaving you to diff text blocks.
Where MonPG fits once you run on PostgreSQL
Bitmap scan behavior is workload-dependent in ways that a one-time review cannot pin down: a query flips from exact to lossy bitmaps as data grows past work_mem, a BitmapAnd appears in a query family after a deploy changes a predicate, an index you meant to retire turns out to feed OR queries every hour. MonPG, which monitors PostgreSQL exclusively, keeps that evidence over time: query family history with timing and buffer trends, plan context for regressions, and index usage statistics that show which indexes actually participate in your bitmap combinations before you drop or replace them. If you are consolidating indexes after a MySQL migration, do it with PostgreSQL monitoring in place first, so every removal is an observed decision rather than a hopeful one.