Usar o plano de explicação para melhorar a performance das consultas do Babelfish

A partir da versão 2.1.0, o Babelfish inclui duas funções que usam de forma transparente o otimizador do PostgreSQL para gerar planos de consulta estimados e reais para consultas T-SQL na porta do TDS. Essas funções são semelhantes ao uso de SET STATISTICS PROFILE ou SET SHOWPLAN_ALL com bancos de dados do SQL Server para identificar e melhorar consultas de execução lenta.

Na tabela, você pode encontrar uma comparação das funções de explicação do plano de consulta no SQL Server, no Babelfish e no PostgreSQL.

Use as funções do Babelfish da seguinte forma:

Para obter mais informações sobre EXPLAIN e EXPLAIN ANALYZE do PostgreSQL, consulte EXPLAIN na documentação do PostgreSQL.

Por exemplo, a sequência de comandos a seguir ativa o planejamento de consulta e, depois, retorna um plano de execução de consulta estimado para a instrução SELECT sem executar a consulta. Esse exemplo usa o exemplo de banco de dados do SQL Server northwind usando a ferramenta de linha de comando sqlcmd para consultar a porta do 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)

Quando você terminar de revisar e ajustar sua consulta, desative a função como mostrado a seguir:

1> SET BABELFISH_SHOWPLAN_ALL OFF

Com BABELFISH_STATISTICS PROFILE definido como ON (Ativo), cada consulta executada retorna seu conjunto de resultados regular seguido de um conjunto de resultados adicional que mostra planos de execução de consulta reais. O Babelfish gera o plano de consulta que fornece o conjunto de resultados mais rápido quando invoca a instrução 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

O conjunto de resultados e o plano de consulta são retornados (esse exemplo mostra apenas o plano 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 saber mais sobre como analisar suas consultas e os resultados retornados pelo otimizador do PostgreSQL, consulte explain.depesz.com. Para obter mais informações sobre EXPLAIN e EXPLAIN ANALYZE do PostgreSQL, consulte EXPLAIN na documentação do PostgreSQL.

Parâmetros que controlam as opções de explicação do Babelfish

Você pode usar os parâmetros mostrados na tabela a seguir para controlar o tipo de informação que é exibida pelo plano de consulta.

Você pode conferir os valores de quaisquer parâmetros relacionados ao Babelfish em seu sistema usando o cliente do PostgreSQL ou do SQL Server. Execute o seguinte comando para obter os valores atuais dos parâmetros:

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

Na saída a seguir, você pode ver que todas as configurações nesse cluster de banco de dados do Babelfish específico estão com seus valores padrão. Nem todas as saídas são mostradas neste exemplo.

             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)

Você pode alterar a configuração desses parâmetros usando sp_babelfish_configure, conforme mostrado no exemplo a seguir.

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

Se quiser tornar a configuração permanente em todo o cluster, inclua a palavra-chave server, conforme mostrado a seguir:

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