To examine the query plan PostgreSQL is using for a prepared statement, use EXPLAIN, for example This setting is primarily useful if the generic plan's cost estimate is badly off for some reason, allowing it to be chosen even though its actual cost is much more than that of a custom plan. This heuristic can be overridden, forcing the server to use either generic or custom plans, by setting plan_cache_mode to force_generic_plan or force_custom_plan respectively. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)īy default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable.Ī prepared statement can be executed with either a generic plan or a custom plan. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g., if the query involves a join of many tables or requires the application of several rules. Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. Prepared statements can be manually cleaned up using the DEALLOCATE command. This also means that a single prepared statement cannot be used by multiple simultaneous database clients however, each client can create their own prepared statement to use. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again. Prepared statements only last for the duration of the current database session. Refer to EXECUTE for more information about that. When executing the statement, specify the actual values for these parameters in the EXECUTE statement. When a parameter's data type is not specified or is declared as unknown, the type is inferred from the context in which the parameter is first referenced (if possible). A corresponding list of parameter data types can optionally be specified. When creating the prepared statement, refer to parameters by position, using $1, $2, etc. Prepared statements can take parameters: values that are substituted into the statement when it is executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. A prepared statement is a server-side object that can be used to optimize performance.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |