Single vs Composite Index Performance

Problem Given a table with separate single-column indices on A and B, how does the database process SELECT * WHERE A=10 AND B=20 - and would a composite index on (A,B) do better?

Be ready to discuss

• With two single-column indices: the planner usually picks the more selective one, scans it, then rechecks the other predicate against the fetched rows.
• Bitmap index intersection (BitmapAnd in Postgres, index_merge in MySQL): both indices are scanned and the results combined - possible, but it costs two scans plus the merge.
• Why the composite index on (A,B) wins here: one traversal lands on the exact key range satisfying both predicates, no recheck and no merge.
• The leftmost-prefix rule: (A,B) serves A=10 and A=10 AND B=20, but does nothing for a query filtering on B alone - column order is the whole ballgame.
• Choosing column order: equality columns before range columns, and generally the more selective column first.
• Covering indices and index-only scans: adding the selected columns lets the engine answer entirely from the index and skip the heap/table fetch.
• The costs: every extra index adds write amplification on INSERT/UPDATE/DELETE and consumes storage, so indices are a workload-specific bet.
• Selectivity and cardinality: if A=10 already matches one row, the second index buys you nothing - always reason from the data distribution.
• Verifying rather than guessing: read the plan with EXPLAIN/EXPLAIN ANALYZE and compare estimated vs actual rows.