Thread: Multi-column index vs index on individual columns
Hi,
I was going through Postgres documentation on multi-column indexes and came across the recommendation at the bottom states: "Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time".
In my experience typical webapps the queries are known ahead of time and lot of the times the queries are restricted to a single table (no joins). Does this recommendation that still apply in this use-case? I was under the impression that instead of scanning multiple indexes and then combining the results, it may be more performant to simply use a single multi-index column.
Thanks,
Shantanu
In a typical web storefront or catalog app, there are a fairly small number of queries that are run often that are more complex and slower. It is perfectly appropriate to consider creating compound/partial indexes to speed up those queries. Just be aware that indexes trade off slower insert/update time against faster search time. I would read use-the-index-luke.com for detailed advice about using compound and partial indexes effectively.
On Dec 26, 2020, 12:18 -0800, Shantanu Shekhar <shekharshan@yahoo.com>, wrote:
Hi,I was going through Postgres documentation on multi-column indexes and came across the recommendation at the bottom states: "Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time".In my experience typical webapps the queries are known ahead of time and lot of the times the queries are restricted to a single table (no joins). Does this recommendation that still apply in this use-case? I was under the impression that instead of scanning multiple indexes and then combining the results, it may be more performant to simply use a single multi-index column.Thanks,Shantanu