You're going to be doing 3 types of tuning, 1 reactive and 2 proactive.
Out of the blue, some query starts causing you problems. It could be because of an application bug or feature, a table growing in excess of expectations, a traffic spike, or the query optimizer getting "creative". This could be a middle-of-the-night oh-crap-the-site's-down type of affair, or it could be in response to system slowness of a non-critical nature. Either way, the defining character of reactive tuning is that you already have a problem. Needless to say, you want to be doing as little of this as possible. Which brings us to...
Type 1: Routine Maintenance
On some sort of schedule, every few months or weeks depending on how often your schema changes and how fast your data grows, you should review the output of your database's performance analysis tools (e.g. AWR reports for Oracle DBAs). You're look for incipient issues, that is things that on their way to requiring Reactive tuning, as well as low-hanging fruit, items that aren't likely to cause problems soon but can improved with little effort in the hopes of preventing far-future problems. How much time you should spend on this will depend on how much time you have, and what else you could be spending it on, but the optimal amount is never zero. However, you can easily reduce the amount you need to spend by doing more of...
Type 2: Proper Design
Knuth's admonition against "premature optimization" is widely known and duly respected. But the proper definition of "premature" must be used. Some application developers, when permitted to write their own queries, have a tendency to adopt the very first query they hit upon that is logically correct, and pay no mind whatsoever to performance, present or future. Or they may test against a development data set that simply isn't representative of the production environment (tip: Don't do this! Developers should always have access to realistic data for testing.). The point is that the proper time to tune a query is when it is first being deployed, not when it shows up on a list of poor-performing SQL, and definitely not when it causes a critical issue.
So what would qualify as a premature optimization in DBA land? At the top of my list would be sacrificing normalization without a demonstrated need. Sure you could maintain a sum on a parent row rather than calculating it at runtime from the child rows, but do you really need to? If you're Twitter or Amazon, strategic de-normalization and pre-calculation can be your best friends. If you're designing a little accounting database for 5 users, proper structure to facilitate data integrity needs to be top priority. Other premature optimizations are likewise a matter of priorities. Don't spend hours tweaking a query that gets run once a day and takes 10 seconds, even if you think you can cut it to 0.1 seconds. Maybe you have a report that runs for 6 hours daily, but explore scheduling it as a batch job before investing time in tuning it. Don't invest in a separate, real-time replicated reporting instance if your production load never floats above 10% (assuming you can manage the security).
By testing against realistic data, taking educated guesses at growth and traffic patterns (plus allowances for spikes), and applying your knowledge of your platform's optimizer quirks, you can deploy queries that run (close to) optimally not just now, but in the future, and under less-than-ideal conditions. When you apply the proper techniques, query performance can be accurately predicted, and optimized (in the sense of each component being as fast as it needs to be).
(And while you're at it, learn statistics!)