# Using explain plan to improve Babelfish query performance
Starting with version 2.1.0, Babelfish includes two functions that transparently use the PostgreSQL optimizer to generate estimated and actual query plans for T-SQL queries on the TDS port. These functions are similar to using SET STATISTICS PROFILE or SET SHOWPLAN\$1ALL with SQL Server databases to identify and improve slow running queries.
**Note**
Getting query plans from functions, control flows, and cursors isn't currently supported.
In the table you can find a comparison of query plan explain functions across SQL Server, Babelfish, and PostgreSQL.
| SQL Server | Babelfish | PostgreSQL |
| --- | --- | --- |
| SHOWPLAN\$1ALL | BABELFISH\$1SHOWPLAN\$1ALL | EXPLAIN |
| STATISTICS PROFILE | BABELFISH\$1STATISTICS PROFILE | EXPLAIN ANALYZE |
| Uses the SQL Server optimizer | Uses the PostgreSQL optimizer | Uses the PostgreSQL optimizer |
| SQL Server input and output format | SQL Server input and PostgreSQL output format | PostgreSQL input and output format |
| Set for the session | Set for the session | Apply to a specific statement |
| Supports the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/working-with-babelfish-usage-notes-features.using.explain.html) | Supports the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/working-with-babelfish-usage-notes-features.using.explain.html) | Supports the following: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/working-with-babelfish-usage-notes-features.using.explain.html) |
Use the Babelfish functions as follows:
+ SET BABELFISH\$1SHOWPLAN\$1ALL [ON\$1OFF] – Set to ON to generate an estimated query execution plan. This function implements the behavior of the PostgreSQL `EXPLAIN` command. Use this command to obtain the explain plan for given query.
+ SET BABELFISH\$1STATISTICS PROFILE [ON\$1OFF] – Set to ON for actual query execution plans. This function implements the behavior of PostgreSQL's `EXPLAIN ANALYZE` command.
For more information about PostgreSQL `EXPLAIN` and `EXPLAIN ANALYZE` see [EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html) in the PostgreSQL documentation.
**Note**
Starting with version 2.2.0, you can set the `escape_hatch_showplan_all` parameter to *ignore* in order to avoid the use of *BABELFISH\$1* prefix in the SQL Server syntax for `SHOWPLAN_ALL` and `STATISTICS PROFILE` SET commands.
For example, the following command sequence turns on query planning and then returns an estimated query execution plan for the SELECT statement without running the query. This example uses the SQL Server sample `northwind` database using the `sqlcmd` command-line tool to query the TDS port:
```
1> SET BABELFISH_SHOWPLAN_ALL ON
2> GO
1> SELECT t.territoryid, e.employeeid FROM
2> dbo.employeeterritories e, dbo.territories t
3> WHERE e.territoryid=e.territoryid ORDER BY t.territoryid;
4> GO
QUERY PLAN
------------------------------------------------------------------------------------
Query Text: SELECT t.territoryid, e.employeeid FROM
dbo.employeeterritories e, dbo.territories t
WHERE e.territoryid=e.territoryid ORDER BY t.territoryid
Sort (cost=6231.74..6399.22 rows=66992 width=10)
Sort Key: t.territoryid NULLS FIRST
-> Nested Loop (cost=0.00..861.76 rows=66992 width=10)
-> Seq Scan on employeeterritories e (cost=0.00..22.70 rows=1264 width=4)
Filter: ((territoryid)::"varchar" IS NOT NULL)
-> Materialize (cost=0.00..1.79 rows=53 width=6)
-> Seq Scan on territories t (cost=0.00..1.53 rows=53 width=6)
```
When you finish reviewing and adjusting your query, you turn off the function as shown following:
```
1> SET BABELFISH_SHOWPLAN_ALL OFF
```
With BABELFISH\$1STATISTICS PROFILE set to ON, each executed query returns its regular result set followed by an additional result set that shows actual query execution plans. Babelfish generates the query plan that provides the fastest result set when it invokes the SELECT statement.
```
1> SET BABELFISH_STATISTICS PROFILE ON
1>
2> GO
1> SELECT e.employeeid, t.territoryid FROM
2> dbo.employeeterritories e, dbo.territories t
3> WHERE t.territoryid=e.territoryid ORDER BY t.territoryid;
4> GO
```
The result set and the query plan are returned (this example shows only the query plan).
```
QUERY PLAN
---------------------------------------------------------------------------
Query Text: SELECT e.employeeid, t.territoryid FROM
dbo.employeeterritories e, dbo.territories t
WHERE t.territoryid=e.territoryid ORDER BY t.territoryid
Sort (cost=42.44..43.28 rows=337 width=10)
Sort Key: t.territoryid NULLS FIRST
-> Hash Join (cost=2.19..28.29 rows=337 width=10)
Hash Cond: ((e.territoryid)::"varchar" = (t.territoryid)::"varchar")
-> Seq Scan on employeeterritories e (cost=0.00..22.70 rows=1270 width=36)
-> Hash (cost=1.53..1.53 rows=53 width=6)
-> Seq Scan on territories t (cost=0.00..1.53 rows=53 width=6)
```
To learn more about how to analyze your queries and the results returned by the PostgreSQL optimizer, see [explain.depesz.com](https://www.depesz.com/2013/04/16/explaining-the-unexplainable/). For more information about PostgreSQL EXPLAIN and EXPLAIN ANALYZE, see [EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html) in the PostgreSQL documentation.
## Parameters that control Babelfish explain options
You can use the parameters shown in the following table to control the type of information that's displayed by your query plan.
| Parameter | Description |
| --- | --- |
| babelfishpg\$1tsql.explain\$1buffers | A boolean that turns on (and off) buffer usage information for the optimizer. (Default: off) (Allowable: off, on) |
| babelfishpg\$1tsql.explain\$1costs | A boolean that turns on (and off) estimated startup and total cost information for the optimizer. (Default: on) (Allowable: off, on) |
| babelfishpg\$1tsql.explain\$1format | Specifies the output format for the `EXPLAIN` plan. (Default: text) (Allowable: text, xml, json, yaml) |
| babelfishpg\$1tsql.explain\$1settings | A boolean that turns on (or off) the inclusion of information about configuration parameters in the EXPLAIN plan output. (Default: off) (Allowable: off, on) |
| babelfishpg\$1tsql.explain\$1summary | A boolean that turns on (or off) summary information such as total time after the query plan. (Default: on) (Allowable: off, on) |
| babelfishpg\$1tsql.explain\$1timing | A boolean that turns on (or off) actual startup time and time spent in each node in the output. (Default: on) (Allowable: off, on) |
| babelfishpg\$1tsql.explain\$1verbose | A boolean that turns on (or off) the most detailed version of an explain plan. (Default: off) (Allowable: off, on) |
| babelfishpg\$1tsql.explain\$1wal | A boolean that turns on (or off) generation of WAL record information as part of an explain plan. (Default: off) (Allowable: off, on) |
You can check the values of any Babelfish-related parameters on your system by using either PostgreSQL client or SQL Server client. Run the following command to get your current parameter values:
```
1> execute sp_babelfish_configure '%explain%';
2> GO
```
In the following output, you can see that all settings on this particular Babelfish DB cluster are at their default values. Not all output is shown in this example.
```
name setting short_desc
---------------------------------- -------- --------------------------------------------------------
babelfishpg_tsql.explain_buffers off Include information on buffer usage
babelfishpg_tsql.explain_costs on Include information on estimated startup and total cost
babelfishpg_tsql.explain_format text Specify the output format, which can be TEXT, XML, JSON, or YAML
babelfishpg_tsql.explain_settings off Include information on configuration parameters
babelfishpg_tsql.explain_summary on Include summary information (e.g.,totaled timing information) after the query plan
babelfishpg_tsql.explain_timing on Include actual startup time and time spent in each node in the output
babelfishpg_tsql.explain_verbose off Display additional information regarding the plan
babelfishpg_tsql.explain_wal off Include information on WAL record generation
(8 rows affected)
```
You can change the setting for these parameters using `sp_babelfish_configure`, as shown in the following example.
```
1> execute sp_babelfish_configure 'explain_verbose', 'on';
2> GO
```
If you want make the setting permanent on a cluster-wide level, include the keyword *server*, as shown in the following example.
```
1> execute sp_babelfish_configure 'explain_verbose', 'on', 'server';
2> GO
```