Yes!

Distributed systems introduce severe restrictions on what can be reasonably done at scale. Having a single connection string is one thing, being able to do a massive JOIN is another (and should only be ever done in analytical databases).

The question is not "when sharding becomes counter-productive" but "when it starts making sense".

With sharding something somewhere has to know how to route queries to subsets of data. So it is a complexity price paid for being able to scale. If one can avoid paying this cost then he should.

And USUALLY cross-shard queries are not just expensive but simply impossible in operational clusters. Like, if you do COUNT on a table, you only count within a single db shard table.