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 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.
- How would you go about analyzing and determining what columns should be indexed, and how?
- 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.)
- I expect that it does not make sense to index all columns?
- I expect it does not make sense to index boolean columns?
- Is it better to do a combined index on 5 frequently used columns rather than having individual indexes on each of them?
- Would it be a good idea to have all indexes sorted by my default sorting?
- Do you have so experiences with other approaches that could greatly improve performance (e.g. forcing indexes to stay in memory etc.)?