Usually when you design the schema for a relational database, you do so keeping in mind the kind of queries that will be performed. Even in NoSQL databases you usually structure your documents and split them (or duplicate information) across collections in way that benefits the queries for your use case.
An ad-hoc query in contrast, is something not known beforehand while structuring the database.
It may still be fast, even on large datasets, if the index for a previously anticipated use-case happens to help in execution of this query as well. But in many cases they may be impractical to run and query plan should always be inspected before running an adhoc query on a large production database.