Our multi-tenant application uses Postgres database. The application was created many years ago, and now we are seeing some performance issues when load increases.
Every database table we have has at least two indexes - Id (PK) and TenantId (FK). On every select we added TenantId filter, and when we fetch an item by Id there also TenantId is added in the where clause.
Now, my question is that did we do it wrong. Instead of adding two indexes we should have added index on (TenantId, Id) and that would have improved our query performance? Would this have improved both the scenarios - when selecting using tenantId and tenantId + Id?