What is the difference in performance between a two single-field indexes and one compound index?

Question

Suppose you have Users with an email and name, and you want to search by both email and name.

What is the difference in performance between having two indexes (on email and name) and having one index on both fields?

I read the following but did not find the answer:

Multicolumn Indexes (PostgreSQL documentation)
Multicolumn index and performance (DBA Q & A)
PostgreSQL single vs. multi-column indexing for optimal SELECT performance (Stack Overflow)
Multiple column index vs multiple indexes (Percona)

I am interested in the difference in performance for Postgres and MongoDB.

Answer 1

I'll answer on the MongoDB side (no Postgres knowledge, sorry). The classic answer is that a query only ever uses a single index, so if you have a compound index, good data cardinality and are not using multiple $or clauses, then the compound index will significantly outperform the two single indexes. Some exceptions to this would be those $or clauses (each clause uses a single index and merges the results, so for small data sets this might be a wash) and a data set where a single index fits in memory but the compound index does not.

MongoDB supports intersection as of version 2.6 but the only reliable way to tell if it will select intersection as a viable strategy is to have a sample data set and indexes and try it out. Compound indexes are a far more reliable strategy for now, and would be my recommendation for searching on two fields generally with MongoDB.