Uso del plan de EXPLAIN para mejorar el rendimiento de las consultas en Babelfish

A partir de la versión 2.1.0, Babelfish incluye dos funciones que utilizan de forma transparente el optimizador de PostgreSQL para generar planes de consulta estimados y reales para consultas T-SQL en el puerto de TDS. Estas funciones son similares al uso de SET STATISTICS PROFILE o SET SHOWPLAN_ALL con las bases de datos de SQL Server para identificar y mejorar las consultas de ejecución lenta.

nota

Actualmente no se admite la obtención de planes de consulta a partir de funciones, flujos de control y cursores.

En la tabla puede encontrar una comparación de las funciones de EXPLAIN del plan de consulta en SQL Server, Babelfish y PostgreSQL.

SQL Server	Babelfish	PostgreSQL
SHOWPLAN_ALL	BABELFISH_SHOWPLAN_ALL	EXPLAIN
STATISTICS PROFILE	BABELFISH_STATISTICS PROFILE	EXPLAIN ANALYZE
Utiliza el optimizador de SQL Server	Utiliza el optimizador de PostgreSQL	Utiliza el optimizador de PostgreSQL
Formato de entrada y salida de SQL Server	Formato de entrada de SQL Server y salida de PostgreSQL	Formato de entrada y salida de PostgreSQL
Se establece para la sesión	Se establece para la sesión	Se aplica a una instrucción específica
Admite lo siguiente: SELECT INSERT UPDATE DELETE CURSOR CREATE EXECUTE EXEC y funciones, incluido el flujo de control (CASE, WHILE-BREAK-CONTINUE, WAITFOR, BEGIN-END, IF-ELSE, etc.)	Admite lo siguiente: SELECT INSERT UPDATE DELETE CREATE EXECUTE EXEC RAISEERROR THROW PRINT USE	Admite lo siguiente: SELECT INSERT UPDATE DELETE CURSOR CREATE EXECUTE

Utilice las funciones de Babelfish de la siguiente manera:

• SET BABELFISH_SHOWPLAN_ALL [ON|OFF]: configúrela en ON para generar un plan de ejecución de consultas estimado. Esta función implementa el comportamiento del comando EXPLAIN de PostgreSQL. Utilice este comando para obtener el plan de EXPLAIN de una consulta determinada.

• SET BABELFISH_STATISTICS PROFILE [ON|OFF]: configúrela en ON para los planes de ejecución de consultas reales. Esta función implementa el comportamiento del comando EXPLAIN ANALYZE de PostgreSQL.

Para obtener más información sobre EXPLAIN y EXPLAIN ANALYZE de PostgreSQL, consulte EXPLAIN en la documentación de PostgreSQL.

nota

A partir de la versión 2.2.0, puede configurar el parámetro escape_hatch_showplan_all en ignore a fin de evitar el uso del prefijo BABELFISH_ en la sintaxis de SQL Server para los comandos SET SHOWPLAN_ALL y STATISTICS PROFILE.

Por ejemplo, la siguiente secuencia de comandos activa la planificación de consultas y, a continuación, devuelve un plan de ejecución de consultas estimado para la sentencia SELECT sin ejecutar la consulta. En este ejemplo se utiliza la base de datos northwind de muestra de SQL Server mediante la herramienta de línea de comandos sqlcmd para consultar el puerto de TDS:

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)

Cuando termine de revisar y ajustar la consulta, desactive la función como se muestra a continuación:

1> SET BABELFISH_SHOWPLAN_ALL OFF

Con BABELFISH_STATISTICS PROFILE configurado en ON, cada consulta ejecutada devuelve su conjunto de resultados habitual seguido de un conjunto de resultados adicional que muestra los planes de ejecución de consulta reales. Babelfish genera el plan de consulta que proporciona el conjunto de resultados más rápido cuando invoca la instrucción SELECT.

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

Se devuelve el conjunto de resultados y el plan de consulta (en este ejemplo se muestra solo el plan de consulta).

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)

Para obtener más información sobre cómo analizar sus consultas y los resultados que devuelve el optimizador de PostgreSQL, consulte explain.depesz.com. Para obtener más información sobre EXPLAIN y EXPLAIN ANALYZE de PostgreSQL, consulte EXPLAIN en la documentación de PostgreSQL.

Parámetros que controlan las opciones de EXPLAIN de Babelfish

Puede utilizar los parámetros que se muestran en la tabla siguiente para controlar el tipo de información que muestra el plan de consulta.

Parámetro	Descripción
babelfishpg_tsql.explain_buffers	Un valor booleano que activa (y desactiva) la información de uso del búfer para el optimizador. (Valor predeterminado: on; permitido: off, on).
babelfishpg_tsql.explain_costs	Un valor booleano que activa (y desactiva) la información estimada de inicio y el coste total para el optimizador. (Valor predeterminado: on; permitido: off, on).
babelfishpg_tsql.explain_format	Especifica el formato de salida del plan de EXPLAIN. (Valor predeterminado: text; permitido: text, xml, json, yaml).
babelfishpg_tsql.explain_settings	Un valor booleano que activa (o desactiva) la inclusión de información sobre los parámetros de configuración en la salida del plan de EXPLAIN. (Valor predeterminado: on; permitido: off, on).
babelfishpg_tsql.explain_summary	Un valor booleano que activa (o desactiva) la información de resumen, como el tiempo total después del plan de consulta. (Valor predeterminado: on; permitido: off, on).
babelfishpg_tsql.explain_timing	Un valor booleano que activa (o desactiva) el tiempo de inicio real y el tiempo transcurrido en cada nodo de la salida. (Valor predeterminado: on; permitido: off, on).
babelfishpg_tsql.explain_verbose	Un valor booleano que activa (o desactiva) la versión más detallada de un plan de EXPLAIN. (Valor predeterminado: on; permitido: off, on).
babelfishpg_tsql.explain_wal	Un valor booleano que activa (o desactiva) la generación de información del registro WAL como parte de un plan de EXPLAIN. (Valor predeterminado: on; permitido: off, on).

Puede consultar los valores de cualquier parámetro relacionado con Babelfish en su sistema mediante el cliente de PostgreSQL o el cliente de SQL Server. Ejecute el siguiente comando para obtener los valores de los parámetros actuales:

1> execute sp_babelfish_configure '%explain%';
2> GO

En la siguiente salida, puede ver que todas las opciones de configuración de este clúster de base de datos de Babelfish en concreto están en sus valores predeterminados. En este ejemplo no se muestra toda la salida.

             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)

Puede cambiar la configuración de estos parámetros con sp_babelfish_configure, tal y como se muestra en el siguiente ejemplo.

1> execute sp_babelfish_configure 'explain_verbose', 'on';
2> GO

Si desea que la configuración sea permanente a nivel de todo el clúster, incluya la palabra clave server, como se muestra en el siguiente ejemplo.

1> execute sp_babelfish_configure 'explain_verbose', 'on', 'server';
2> GO