[dhd415]

There are other possibilities as well. For example, if your partitioning strategy is such that it improves the selectivity of an index, it could improve query plans for queries that were on an index that was less selective. As an example, I once had a table with over a billion rows distributed among ~100 tenants on which queries were typically run by tenant and date range. Partitioning that table by tenant dramatically improved the performance of those queries because those queries no longer had to scan through rows of which only ~1% were for the tenant of interest.

[bigato]

If you had built composite index instead, wouldn't it work just the same towards improving the performance? Well I can see that an composite index would occupy more space while partitioning wouldn't, and that should be something to take into consideration. But performance-wise, wouldn't it be the same?

[dhd415]

No, for example, a composite index on (tenant, date) would be highly non-selective whereas an index on date in each individual tenant partition is highly selective and therefore higher-performance (in my case, much higher performance).

[bigato]

What if you made an index on (date, tenant) instead?

[Jweb_Guru]

The issue he or she is referring to is that the underlying rows are fragmented within each fetched page (so you might need to fetch 40 pages for 40 index entries, even if they would all fit on one partitioned page). Fiddling with the index order isn't going to change that (the current index order is already optimal for that type of query). There's another solution, which is to expand the index to include any covered rows so you can use index-only scans and not hit the main rows at all, but that's trading away a large amount of index size, which you usually want to avoid unless you have no other choice.