What does this even mean? For any query which relies on data from different tables, yes there are opportunities to tune the query, and there are opportunities to build different kinds of indexes, and there are opportunities to de-normalize the data and remove the join completely. This is independent of your RDBMS vendor.
The statement that Oracle/SQL Server are 'high quality' and the implication that open source RDBMSes are not, is flame-bait. All RDBMSes (that I have experience with - including Oracle and SQL server - so caveat emptor) will hit a performance wall when an index becomes invalid and the engine has to table-scan - and indexes can become invalid automatically as the amount of data in the tables changes - typically this will happen at 3am in the morning when you are on call.
There is also the hardware that the files are stored on, and the balancing act of price/performance of splitting tables onto separate spindles, using SSDs for semi-hot tables, cramming in more ram, upgrading the network connection between db and app servers etc etc. Every real world application needs constant tweaking from query to hardware as bottlenecks appear in different layers. Again these issues are almost entirely independent of the RDBMS (apart from with SQL Server that only runs on Windows, so the deployment platform is limited by what Windows server supports).
Nonrelational datastores can also hit performance walls too - the fact that some of the nonrelational stores are newer also has the added 'fun' that the performance characteristics are not completely known.