2

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?

1
7

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:

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
3
  • Thanks for your answer Craig! If a user cannot save a plan to disk, then what about the PREPARE statement in PostgreSQL? According to document, If a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. So the generic plan must be saved in a file in disk, right? Any chance you know where the file is saved and what is the file name? – zli89 Feb 11 '14 at 15:13
  • 1
    @ZhanLi No, the behaviour the docs describe there is per-session. Plans are not saved across sessions. It's possible to dump and reload parse trees, as this is used to implement views (see ./src/backend/nodes/outfuncs.c) but plans are never saved to disk and reloaded, they're kept in memory only. It's theoretically possible to add such a feature - making the plancache persistent and shared - but it'd be a big programming job, you'd need to spend months learning the PostgreSQL code, and your idea would probably get rejected. – Craig Ringer Feb 11 '14 at 22:35
  • @ZhanLi You need to let go of the idea that you can save and restore a plan. Instead, look at how you can guide (or if necessary force) the desired plan choice. – Craig Ringer Feb 11 '14 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.