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 <shekhars...@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