설명 계획을 사용하여 Babelfish 쿼리 성능 향상

버전 2.1.0부터 Babelfish에는 PostgreSQL 옵티마이저를 투명하게 사용하여 TDS 포트에서 T-SQL 쿼리에 대한 예상 및 실제 쿼리 계획을 생성하는 두 함수가 포함됩니다. 이러한 함수는 SQL Server 데이터베이스에서 SET STATISTICS PROFILE 또는 SET SHOWPLAN_ALL을 사용하여 실행 속도가 느린 쿼리를 식별하고 개선하는 것과 유사합니다.

이 테이블에서는 SQL Server, Babelfish, PostgreSQL의 쿼리 계획 설명 함수 비교 정보를 확인할 수 있습니다.

다음과 같이 Babelfish 함수를 사용합니다.

PostgreSQL EXPLAIN 및 EXPLAIN ANALYZE에 대한 자세한 내용은 PostgreSQL 설명서의 EXPLAIN을 참조하세요.

예를 들어, 다음 명령 시퀀스는 쿼리 계획을 설정한 다음 쿼리를 실행하지 않고 SELECT 문에 대한 예상 쿼리 실행 계획을 반환합니다. 이 예에서는 sqlcmd 명령줄 도구를 통해 SQL Server 샘플 northwind 데이터베이스를 사용하여 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)

쿼리 검토 및 조정을 마치면 다음과 같이 함수를 해제합니다.

1> SET BABELFISH_SHOWPLAN_ALL OFF

BABELFISH_STATISTICS PROFILE을 ON으로 설정하면 실행된 각 쿼리는 일반 결과 세트를 반환한 후 실제 쿼리 실행 계획을 보여주는 추가 결과 세트를 반환합니다. Babelfish는 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

결과 세트와 쿼리 계획이 반환되며, 이 예에는 쿼리 계획만 표시되어 있습니다.

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)

PostgreSQL 옵티마이저에서 반환한 쿼리 및 결과를 분석하는 방법에 대한 자세한 내용은 explain.depesz.com을 참조하세요. PostgreSQL EXPLAIN 및 EXPLAIN ANALYZE에 대한 자세한 내용은 PostgreSQL 설명서의 EXPLAIN을 참조하세요.

Babelfish 설명 옵션을 제어하는 파라미터

다음 테이블에 표시된 파라미터를 사용하여 쿼리 계획에 표시되는 정보 유형을 제어할 수 있습니다.

PostgreSQL 클라이언트 또는 SQL Server 클라이언트를 사용하여 시스템에서 Babelfish 관련 파라미터 값을 확인할 수 있습니다. 다음 명령을 실행하여 현재 파라미터 값을 가져옵니다.

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

다음 출력에서 이 특정 Babelfish DB 클러스터의 모든 설정이 기본값임을 알 수 있습니다. 이 예에서는 일부 출력이 표시되지 않습니다.

             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)

다음 예제와 같이 sp_babelfish_configure를 사용하여 이러한 파라미터의 설정을 변경할 수 있습니다.

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

클러스터 전체 수준에서 설정을 영구적으로 만들려면 다음 예제와 같이 키워드 server를 포함합니다.

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