I have a search form that has a lot of search options. How would I index all these columns? Is it ok to create an index with many columns?
Rails example:
add_index :cars, [:brand, :fuel, :year_from, :year_to, :price_from, :price_to] and so on...
Is is "ok" to include a whole lot of columns in a single index?
P.S I'm using newest Postgresql D.S
It depends on how you are planning to query the database table. Are you planning to run a query that will ever have all the fields in the clause ?
In the case the query will contain all the fields in the clause, creating a multicolumns index is the right way, but if the fields that you will filter in the where clause could be only some of these (for example sometimes only brand and fuels, and other times only year_from and year_to)and in different combinations, you better create a single index for each field ...
A many-column index would be very large, probably too large to be of any use. PostgreSQL has a good planner, and it would probably use the first few columns of the index and then scan the rows instead of scanning the remaining bulk of the index. And this is assuming the first few columns of the index are given as search criteria.
Note that the documentation has this to say about multi-column indexes,
Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized.
and use single-column indexes.
You may want to experiment with adding a 2-column index or two in the future, when you have a good idea of what criteria are most likely to be searched on.
