Gabrielle Roth shares some advice:
For PGSQL Phriday #016, Ryan’s asked us to “…discuss your process for tuning difficult queries. Specifically, try to focus on that one problematic query that really challenged you and you always use it as an example when helping or teaching others your methods.”
Here are the generic steps I take, mostly in order.
Click through for those tips, as well as an example of using join_collapse_limit in practice to tame an unruly query.