2

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.

1
  • 1
    In Postgres, check the execution plan (using explain (analyze) select ....) Sep 9, 2016 at 8:39

1 Answer 1

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.

2
  • Postgres will happily use more then one index in a single query. It will even use more then one index for a single table in a query (if that promises to speed up things) Sep 9, 2016 at 14:29
  • Up until recently, there were no joins in MongoDB, and even now they are extremely limited, so all of the above applies to a query within a single collection/table rather than across multiple
    – Adam C
    Sep 9, 2016 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.