Postgresql – the difference in performance between a two single-field indexes and one compound index

indexmongodbpostgresql

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.

Best Answer

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.

Related Solutions

Mongodb – How to index dynamic attributes in MongoDB

I also asked this same question (in a bit expanded form) on the mongodb-user mailing list, where I got an answer. Read from there to get more details. The short answer is, that the strategy used in the question should work fine, but there's an issue that makes it very inefficient. Hopefully, the issue will be fixed soon.

For my case, I only need to query for exact matches for the tuple {n,v}, so I can create a multikey index:

db.mycollection.ensureIndex({"attrs":1})

and make they query like this:

db.mycollection.find({"attrs": {n: "subject", v: "Some subject"}})

which works great and uses the index very effectively.

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

Best Answer

Related Solutions

Mongodb – How to index dynamic attributes in MongoDB

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

Related Question