[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.