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_ALL with SQL Server databases to identify and improve slow running queries.

In the table you can find a comparison of query plan explain functions across SQL Server, Babelfish, and PostgreSQL.

Use the Babelfish functions as follows:

For more information about PostgreSQL EXPLAIN and EXPLAIN ANALYZE see EXPLAIN in the PostgreSQL documentation.

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_STATISTICS 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. For more information about PostgreSQL EXPLAIN and EXPLAIN ANALYZE, see EXPLAIN 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.

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