The canister’s state is on the disk, unless it was recently updated. There is a slight overhead of running a query across multiple canisters, as each canister runs in a separate sandbox. Also please keep in mind that the composite queries work only within the same subnet, i.e. they won’t scale across subnets.
By the way, we have a Scalability and Performance Working Group. We could discuss your setup there, or you could find inspiration from past presentations.