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(at)yahoo(dot)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