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
This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such as: RPC:Completed
, SP:Completed
, and SQL:BatchCompleted
. You also need to select the "TextData" field as the values will be in there.
The difference between my answer and @Kin's answer on this Question is that @Kin's answer (unless I am mistaken, in which case I will remove this) focuses on getting either:
- your own query plan (in which case it can have the runtime parameter info in it, but not for other Sessions/SPIDs), or
- plans from the DMVs (in which case they should only have the compiled parameter values, which are not runtime values).
My answer focuses on getting the parameter values for other sessions that are currently running. When relying on the DMVs, there is no way to know if the runtime parameter value is the same as the compiled parameter value. And the context of this question is tracking down the runtime value of queries being submitted via other Sessions/SPIDs (and in SQL Server 2005, whereas Extended Events were introduced in SQL Server 2008).
Best Answer
Derived from https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan
Note: The "NOT LIKE" is there to ensure that you don't find the statement you are running, which is inherently self referencing.