Utilizzo del piano explain per migliorare le prestazioni delle query Babelfish

A partire dalla versione 2.1.0, Babelfish include due funzioni che utilizzano in modo trasparente l'ottimizzatore PostgreSQL per generare piani di query effettivi per query T-SQL sulla porta TDS. Queste funzioni sono simili all'utilizzo di SET STATISTICS PROFILE o SET SHOWPLAN_ALL con i database SQL Server per identificare e migliorare le query a esecuzione lenta.

Nella tabella è disponibile un confronto tra le funzioni explain del piano di query in SQL Server, Babelfish e PostgreSQL.

Utilizza le funzioni Babelfish come segue:

Per ulteriori informazioni su EXPLAIN e EXPLAIN ANALYZE PostgreSQL, consulta EXPLAIN nella documentazione di PostgreSQL.

Ad esempio, la sequenza di comandi seguente attiva la pianificazione delle query e quindi restituisce un piano di esecuzione delle query stimato per l'istruzione SELECT senza eseguire la query. In questo esempio, viene utilizzato il database SQL Server northwind di esempio che utilizza lo strumento a riga di comando sqlcmd per eseguire query sulla porta 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)

Al termine della revisione e della regolazione della query, disattiva la funzione come illustrato di seguito:

1> SET BABELFISH_SHOWPLAN_ALL OFF

Con BABELFISH_STATISTICS PROFILE impostato su ON, ogni query eseguita restituisce il set di risultati regolare seguito da un set di risultati aggiuntivo che mostra i piani di esecuzione effettivi delle query. Babelfish genera il piano di query che fornisce il set di risultati più rapido quando richiama l'istruzione 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

Vengono restituiti il set di risultati e il piano di query (questo esempio mostra solo il piano di query).

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)

Per ulteriori informazioni su come analizzare le query e i risultati restituiti dall'ottimizzatore PostgreSQL, consulta explain.depesz.com. Per ulteriori informazioni su PostgreSQL EXPLAIN e EXPLAIN ANALYZE, consulta EXPLAIN nella documentazione di PostgreSQL.

Parametri che controllano le opzioni explain di Babelfish

Puoi utilizzare i parametri mostrati nella tabella seguente per controllare il tipo di informazioni visualizzate dal piano di query.

Puoi verificare i valori di qualsiasi parametro relativo a Babelfish sul sistema utilizzando il client PostgreSQL o il client SQL Server. Esegui il comando seguente per ottenere i valori dei parametri correnti:

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

Nell'output seguente, puoi vedere che tutte le impostazioni di questo particolare cluster database Babelfish sono sui loro valori predefiniti. Non tutto l'output è mostrato in questo esempio.

             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)

Puoi modificare l'impostazione di questi parametri utilizzando sp_babelfish_configure, come mostrato nell'esempio seguente.

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

Per rendere le impostazioni permanenti a livello di cluster, includi la parola chiave server, come nell'esempio seguente.

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