# Function reference for Aurora PostgreSQL query plan management
<a name="AuroraPostgreSQL.Optimize.Functions"></a>

The `apg_plan_mgmt` extension provides the following functions.

**Topics**
+ [apg\$1plan\$1mgmt.copy\$1outline](#AuroraPostgreSQL.Optimize.Functions.copy_outline)
+ [apg\$1plan\$1mgmt.delete\$1plan](#AuroraPostgreSQL.Optimize.Functions.delete_plan)
+ [apg\$1plan\$1mgmt.evolve\$1plan\$1baselines](#AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines)
+ [apg\$1plan\$1mgmt.get\$1explain\$1plan](#AuroraPostgreSQL.Optimize.Functions.get_explain_plan)
+ [apg\$1plan\$1mgmt.plan\$1last\$1used](#AuroraPostgreSQL.Optimize.Functions.plan_last_used)
+ [apg\$1plan\$1mgmt.reload](#AuroraPostgreSQL.Optimize.Functions.reload)
+ [apg\$1plan\$1mgmt.set\$1plan\$1enabled](#AuroraPostgreSQL.Optimize.Functions.set_plan_enabled)
+ [apg\$1plan\$1mgmt.set\$1plan\$1status](#AuroraPostgreSQL.Optimize.Functions.set_plan_status)
+ [apg\$1plan\$1mgmt.update\$1plans\$1last\$1used](#AuroraPostgreSQL.Optimize.Functions.update_plans_last_used)
+ [apg\$1plan\$1mgmt.validate\$1plans](#AuroraPostgreSQL.Optimize.Functions.validate_plans)

## apg\$1plan\$1mgmt.copy\$1outline
<a name="AuroraPostgreSQL.Optimize.Functions.copy_outline"></a>

Copy a given SQL plan hash and plan outline to a target SQL plan hash and outline, thereby overwriting the target's plan hash and outline. This function is available in `apg_plan_mgmt` 2.3 and higher releases. 

**Syntax**

```
apg_plan_mgmt.copy_outline(
    source_sql_hash,
    source_plan_hash,
    target_sql_hash,
    target_plan_hash,
    force_update_target_plan_hash
)
```

**Return value**  
Returns 0 when the copy is successful. Raises exceptions for invalid inputs.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| source\$1sql\$1hash  | The sql\$1hash ID associated with the plan\$1hash to copy to the target query. | 
| source\$1plan\$1hash  | The plan\$1hash ID to copy to the target query. | 
| target\$1sql\$1hash | The sql\$1hash ID of the query to update with the source plan hash and outline. | 
| target\$1plan\$1hash | The plan\$1hash ID of the query to update with the source plan hash and outline. | 
| force\$1update\$1target\$1plan\$1hash | (Optional) The target\$1plan\$1hash ID of the query is updated even if the source plan isn't reproducible for the target\$1sql\$1hash. When set to true, the function can be used to copy plans across schemas where relation names and columns are consistent. | 

**Usage notes**

This function allows you to copy a plan hash and plan outline that uses hints to other, similar statements, and thus saves you from needing to use in-line hint statements at every occurrence in the target statements. If the updated target query results in an invalid plan, this function raises an error and rolls back the attempted update. 

## apg\$1plan\$1mgmt.delete\$1plan
<a name="AuroraPostgreSQL.Optimize.Functions.delete_plan"></a>

Delete a managed plan. 

**Syntax**

```
apg_plan_mgmt.delete_plan(
    sql_hash,
    plan_hash
)
```

**Return value**  
Returns 0 if the delete was successful or -1 if the delete failed.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash  | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. | 

 

## apg\$1plan\$1mgmt.evolve\$1plan\$1baselines
<a name="AuroraPostgreSQL.Optimize.Functions.evolve_plan_baselines"></a>

Verifies whether an already approved plan is faster or whether a plan identified by the query optimizer as a minimum cost plan is faster.

**Syntax**

```
apg_plan_mgmt.evolve_plan_baselines(
    sql_hash, 
    plan_hash,
    min_speedup_factor,
    action
)
```

**Return value**

The number of plans that were not faster than the best approved plan. 

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. Use NULL to mean all plans that have the same sql\$1hash ID value. | 
| min\$1speedup\$1factor |  The *minimum speedup factor* can be the number of times faster that a plan must be than the best of the already approved plans to approve it. Alternatively, this factor can be the number of times slower that a plan must be to reject or disable it. This is a positive float value.  | 
| action |  The action the function is to perform. Valid values include the following. Case does not matter.  [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html)  | 

** Usage notes**

Set specified plans to approved, rejected, or disabled based on whether the planning plus execution time is faster than the best approved plan by a factor that you can set. The action parameter might be set to `'approve'` or `'reject'` to automatically approve or reject a plan that meets the performance criteria. Alternatively, it might be set to '' (empty string) to do the performance experiment and produce a report, but take no action.

You can avoid pointlessly rerunning of the `apg_plan_mgmt.evolve_plan_baselines` function for a plan on which it was recently run. To do so, restrict the plans to just the recently created unapproved plans. Alternatively, you can avoid running the `apg_plan_mgmt.evolve_plan_baselines` function on any approved plan that has a recent `last_verified` timestamp.

Conduct a performance experiment to compare the planning plus execution time of each plan relative to the other plans in the baseline. In some cases, there is only one plan for a statement and the plan is approved. In such a case, compare the planning plus execution time of the plan to the planning plus execution time of using no plan.

The incremental benefit (or disadvantage) of each plan is recorded in the `apg_plan_mgmt.dba_plans` view in the `total_time_benefit_ms` column. When this value is positive, there is a measurable performance advantage to including this plan in the baseline.

In addition to collecting the planning and execution time of each candidate plan, the `last_verified` column of the `apg_plan_mgmt.dba_plans` view is updated with the `current_timestamp`. The `last_verified` timestamp might be used to avoid running this function again on a plan that recently had its performance verified.

## apg\$1plan\$1mgmt.get\$1explain\$1plan
<a name="AuroraPostgreSQL.Optimize.Functions.get_explain_plan"></a>

Generates the text of an `EXPLAIN` statement for the specified SQL statement. 

**Syntax**

```
apg_plan_mgmt.get_explain_plan(
    sql_hash,
    plan_hash,
    [explainOptionList]
)
```

**Return value**  
Returns runtime statistics for the specified SQL statements. Use without `explainOptionList` to return a simple `EXPLAIN` plan.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash  | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. | 
| explainOptionList | A comma-separated list of explain options. Valid values include `'analyze'`, `'verbose'`, `'buffers'`, `'hashes'`, and `'format json'`. If the `explainOptionList` is NULL or an empty string (''), this function generates an `EXPLAIN` statement, without any statistics.  | 

 

**Usage notes**

For the `explainOptionList`, you can use any of the same options that you would use with an `EXPLAIN` statement. The Aurora PostgreSQL optimizer concatenates the list of options that you provide to the `EXPLAIN` statement.

## apg\$1plan\$1mgmt.plan\$1last\$1used
<a name="AuroraPostgreSQL.Optimize.Functions.plan_last_used"></a>

Returns the `last_used` date of the specified plan from shared memory. 

**Note**  
The value in shared memory is always current on the primary DB instance in the DB cluster. The value is only periodically flushed to the `last_used` column of the `apg_plan_mgmt.dba_plans` view.

**Syntax**

```
apg_plan_mgmt.plan_last_used(
    sql_hash,
    plan_hash
)
```

**Return value**  
Returns the `last_used` date.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash  | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. | 

 

## apg\$1plan\$1mgmt.reload
<a name="AuroraPostgreSQL.Optimize.Functions.reload"></a>

Reload plans into shared memory from the `apg_plan_mgmt.dba_plans` view. 

**Syntax**

```
apg_plan_mgmt.reload()
```

**Return value**

None.

**Parameters**

None.

** Usage notes**

Call `reload` for the following situations:
+ Use it to refresh the shared memory of a read-only replica immediately, rather than wait for new plans to propagate to the replica.
+ Use it after importing managed plans.



## apg\$1plan\$1mgmt.set\$1plan\$1enabled
<a name="AuroraPostgreSQL.Optimize.Functions.set_plan_enabled"></a>

Enable or disable a managed plan.

**Syntax**

```
apg_plan_mgmt.set_plan_enabled(
    sql_hash, 
    plan_hash, 
    [true | false]
)
```

**Return value**

Returns 0 if the setting was successful or -1 if the setting failed.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. | 
| enabled |  Boolean value of true or false: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html)  | 

 

## apg\$1plan\$1mgmt.set\$1plan\$1status
<a name="AuroraPostgreSQL.Optimize.Functions.set_plan_status"></a>

Set a managed plan's status to `Approved`, `Unapproved`, `Rejected`, or `Preferred`.

**Syntax**

```
apg_plan_mgmt.set_plan_status(
    sql_hash, 
    plan_hash, 
    status
)
```

**Return value**

Returns 0 if the setting was successful or -1 if the setting failed.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. | 
| status |  A string with one of the following values: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html) The case you use does not matter, however the status value is set to initial uppercase in the `apg_plan_mgmt.dba_plans` view. For more information about these values, see `status` in [Reference for the apg\$1plan\$1mgmt.dba\$1plans view for Aurora PostgreSQL-Compatible Edition](AuroraPostgreSQL.Optimize.dba_plans_view_Reference.md).   | 

 

## apg\$1plan\$1mgmt.update\$1plans\$1last\$1used
<a name="AuroraPostgreSQL.Optimize.Functions.update_plans_last_used"></a>

Immediately updates the plans table with the `last_used` date stored in shared memory.

**Syntax**

```
apg_plan_mgmt.update_plans_last_used()
```

**Return value**

None.

**Parameters**

None.

** Usage notes**

Call `update_plans_last_used` to make sure queries against the `dba_plans.last_used` column use the most current information. If the `last_used` date isn't updated immediately, a background process updates the plans table with the `last_used` date once every hour (by default).

For example, if a statement with a certain `sql_hash` begins to run slowly, you can determine which plans for that statement were executed since the performance regression began. To do that, first flush the data in shared memory to disk so that the `last_used` dates are current, and then query for all plans of the `sql_hash` of the statement with the performance regression. In the query, make sure the `last_used` date is greater than or equal to the date on which the performance regression began. The query identifies the plan or set of plans that might be responsible for the performance regression. You can use `apg_plan_mgmt.get_explain_plan` with `explainOptionList` set to `verbose, hashes`. You can also use `apg_plan_mgmt.evolve_plan_baselines` to analyze the plan and any alternative plans that might perform better.

The `update_plans_last_used` function has an effect only on the primary DB instance of the DB cluster.

## apg\$1plan\$1mgmt.validate\$1plans
<a name="AuroraPostgreSQL.Optimize.Functions.validate_plans"></a>

Validate that the optimizer can still recreate plans. The optimizer validates `Approved`, `Unapproved`, and `Preferred` plans, whether the plan is enabled or disabled. `Rejected` plans are not validated. Optionally, you can use the `apg_plan_mgmt.validate_plans` function to delete or disable invalid plans.

**Syntax**

```
apg_plan_mgmt.validate_plans(
    sql_hash, 
    plan_hash, 
    action)
            
apg_plan_mgmt.validate_plans(
    action)
```

**Return value**

The number of invalid plans.

**Parameters**


****  

| Parameter | Description | 
| --- | --- | 
| sql\$1hash | The sql\$1hash ID of the plan's managed SQL statement. | 
| plan\$1hash | The managed plan's plan\$1hash ID. Use NULL to mean all plans for the same sql\$1hash ID value. | 
| action |  The action the function is to perform for invalid plans. Valid string values include the following. Case does not matter. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Functions.html) Any other value is treated like the empty string.  | 

**Usage notes**

Use the form `validate_plans(action)` to validate all the managed plans for all the managed statements in the entire `apg_plan_mgmt.dba_plans` view.

Use the form `validate_plans(sql_hash, plan_hash, action)` to validate a managed plan specified with `plan_hash`, for a managed statement specified with `sql_hash`. 

Use the form `validate_plans(sql_hash, NULL, action)` to validate all the managed plans for the managed statement specified with `sql_hash`.