Is there any way to save the execution plan in a file in PostgreSQL? By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query. 

For example, if the optimizer chooses to do a hash join for a two-table query and I manage to force the optimizer to use a nest loop join, is there any way to save the nest loop join plan and let the optimizer use it next time when I execute the same query?
No, there is no way to save a query plan to disk, force a query plan, etc.

Fix your optimizer parameters and make sure your stats are accurate. You didn't bother to mention your version, show explain analyze output, etc so I can't help you with that in detail. Start here: 

http://wiki.postgresql.org/wiki/Slow_Query_Questions

Check your rowcount estimates vs actual results in explain analyze output. http://explain.depesz.com/ makes this easier.

Check that cost estimates reflect reality. Consider adjusting random_page_cost / seq_page_cost if your random I/O is faster or slower than PostgreSQL's estimates. Also ensure effective_page_size reasonably reflects the amount of RAM available for caching.

If you continue to have issues after making appropriate changes per the manual and tuning guides, please report a detailed description of the problem and a self-contained test case to the pgsql-peform mailing list.

See:


Why PostgreSQL doesn't have query hints
Hinting at PostgreSQL
https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index
Why PostgreSQL actually already has hints
The wiki page on hints
a somewhat topical frothing rant on the topic


My personal opinion is that PostgreSQL's policy of having no hints (except actually there are hints, they're just uglier hacks than actual hints would be) is user-hostile and pointless. Most users will use whatever hacks they can to avoid actually understanding cost-based optimizers, so we're just forcing them to use uglier hacks. Users who want to understand and do it right will still find times the optimizer produces inappropriate results, and while it's good to fix those, it's also good for those users to have a workaround in the mean time. And users who're cost based optimizer purists don't have to use any hinting features that are there, including the not-hints that already exist.

This drives me especially nuts with common table expressions, where PostgreSQL's quirky interpretation of them as optimization fences is going to cause us pain and suffering down the road when we want to start optimizing into them, but discover everyone's been using them as a sort of query hint workaround for the query hints we don't have.

Examples of not-hints include:


The enable_ parameters
OFFSET 0
Optimization fence of CTE terms