Generally I'd say do the same as you would with your compiler when trying to optimize your code: spend time with the query analyzer.

What the query optimizer will or won't do depends on your database software and your data, in addition to your query of course.

For example, the article states "publish_date < now() - interval '3 days'" is always faster than "publish_date + interval '3 days' < now()".

Well not for the database server we use, because it evaluates now() per row turning both into table scans... yay.

In addition to the suggestions in the article, I'd say use common table expressions (CTE), or sub-query if your database server doesn't have CTEs or treats them as an optimization barrier.

I've found the optimizer tries to be smart but doesn't recognize which of the joins will be the most specific filter, causing it to join tons of rows, just to throw most of them away with a later join. Using a CTE or sub-query can force the optimizers hand.

Also I've often found it useful to use CTEs or sub-queries for returning row values, typically aggregates. This can especially work well if you're limiting the rows returned using TOP or LIMIT. Again this can avoid the DB throwing away work.