Guidelines on best indexing strategy for varying searches on 20+ columns

Question

I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation.

Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search.

The problem:

The table containing the cars holds a around 1,5 million rows. People that searches for cars needs different criteria to search by. Some search by brand/model, some by year, some by mileage, some by price and some by special equipment etc. etc. - and often they combine a whole bunch of criteria together. Of cause some, like brand/mode and price, are used more frequently than others. In total we offer: 9 category criteria like brand/model or body type, plus 5 numeric criteria like price or mileage, plus 12 boolean criteria like equipment. Lastly people can order the results by different columns (year, price, mileage and a score we create about the cars). By default we order by our own generated score.

What I’ve done so far:

I have analyzed the usage of the criteria “lightly”, and created a few indexes (10). Among those, are e.g. indexes on price, mileage and a combined index on brand/model. Since we are only interested in showing results for cars which is actually for sale, the indexes are made as partial indexes on a sales state column.

Questions:

1. How would you go about analyzing and determining what columns should be indexed, and how?
2. What is the best strategy when optimizing indexes for searches happening on 20 + columns, where the use and the combinations varies a lot? (To just index everything, to index some of the columns, to do combined indexes, to only do single column indexes etc. etc.)
3. I expect that it does not make sense to index all columns?
4. I expect it does not make sense to index boolean columns?
5. Is it better to do a combined index on 5 frequently used columns rather than having individual indexes on each of them?
6. Would it be a good idea to have all indexes sorted by my default sorting?
7. Do you have so experiences with other approaches that could greatly improve performance (e.g. forcing indexes to stay in memory etc.)?

Answer 1

1. Capture the queries that people actually make.
2. Analyze data from #1; then devise short, multi-column, indexes (2, maybe 3, columns each)
3. No. Especially not for yes/no flags. It's ok to combine such, as in #2.
4. See #3.
5. The order of columns is important in an index. A 5-column index is (roughly) equivalent to the 1-col index on its first column, but is not useful if you don't query on that column.
6. Probably.
7. Forcing into memory is counter-productive because it takes away space for caching other stuff.

More discussion: http://mysql.rjweb.org/doc.php/eav