Database Indexing: Single vs Composite Index Query Performance

Q&A: Given a table with separate single-column indexes on A and B (not composite), how does the backend process a query like SELECT * WHERE A=10 AND B=20? Would a composite index on (A,B) perform better, and why (time/space tradeoffs)?

With separate indexes, the query planner typically picks the more selective single index to narrow candidates, then filters the rest in-memory (or intersects both index scans) -- generally less efficient than a single lookup. A composite index on (A,B) lets the database satisfy the whole predicate via one index scan when the query filters on the index's leading column(s), making it faster and more space-efficient for that specific access pattern, at the cost of only helping queries that use the indexed column prefix.