Once you understand what a query is doing, getting it to do something better can be a difficult road to walk down. There are some common techniques that mesh with the areas PostgreSQL is known to be good and bad at.
Note that your first steps to improve queries should be to check whether the statistics the optimizer is working with seem reasonable. If it's bad, you may fix the problem by rewriting the query you have today to improve things. But, you can expect that future queries will run into the same class of issue, so fixing that from a statistics standpoint is the preferred way, the one that's more likely to continue reflecting future trends in the data. But improving the statistics about your data only goes so far some ...
