Link Search Menu Expand Document Documentation Menu

T-SQL query hints to improve Babelfish query performance

Starting with version 2.3.0, Babelfish supports the use of T-SQL query hints using pg_hint_plan. For more information about the PostgreSQL extension pg_hint_plan, see (https://github.com/ossc-db/pg_hint_plan).

The PostgreSQL query optimizer is well-designed to find the optimal execution plan for a SQL statement. When selecting a plan, the query optimizer considers both the engine’s cost model, and column and table statistics. However, the suggested plan might not meet your needs. Thus, T-SQL query hints address the performance issues by helping the query optimizer improve execution plans. A query hint is syntax added to the SQL standard that instructs the database engine about how to execute the query. For example, a hint may instruct the engine to follow a sequential scan and override any plan that the query optimizer had selected.

Installing pg_hint_plan

Babelfish version 2.3.0 supports version 1.4 of pg_hint_plan. To install pg_hint_plan please follow the instructions in (https://github.com/ossc-db/pg_hint_plan#building-binary-module).

Turning on T-SQL query hints in Babelfish

Currently, Babelfish ignores all T-SQL hints by default. To apply T-SQL hints, run the command sp_babelfish_configure with the enable_pg_hint value as ON.

EXECUTE sp_babelfish_configure 'enable_pg_hint', 'on' [, 'server']

You can make the settings permanent on a cluster-wide level by including the server keyword. To configure the setting for the current session only, don’t use server.

After enable_pg_hint is ON, Babelfish applies the following T-SQL hints.

  • INDEX hints

  • JOIN hints

  • FORCE ORDER hint

  • MAXDOP hint

For example, the following command sequence turns on pg_hint_plan.

1> CREATE TABLE t1 (a1 INT PRIMARY KEY, b1 INT);
2> CREATE TABLE t2 (a2 INT PRIMARY KEY, b2 INT);
3> GO    
1> EXECUTE sp_babelfish_configure 'enable_pg_hint', 'on';
2> GO
1> SET BABELFISH_SHOWPLAN_ALL ON;
2> GO
1> SELECT * FROM t1 JOIN t2 ON t1.a1 = t2.a2; --NO HINTS (HASH JOIN)
2> GO

No hint is applied to the SELECT statement. The query plan with no hint is returned.

QUERY PLAN                                                                                                                                                                                                                                
---------------------------------------------------------------------------
Query Text: SELECT * FROM t1 JOIN t2 ON t1.a1 = t2.a2
Hash Join (cost=60.85..99.39 rows=2260 width=16)
 Hash Cond: (t1.a1 = t2.a2)
 -> Seq Scan on t1 (cost=0.00..32.60 rows=2260 width=8)
 -> Hash (cost=32.60..32.60 rows=2260 width=8)
 -> Seq Scan on t2 (cost=0.00..32.60 rows=2260 width=8)
1> SELECT * FROM t1 INNER MERGE JOIN t2 ON t1.a1 = t2.a2;
2> GO

The query hint is applied to the SELECT statement. The following output shows that the query plan with merge join is returned.

QUERY PLAN                                                                                                                                                                                                                                
---------------------------------------------------------------------------
Query Text: SELECT/*+ MergeJoin(t1 t2) Leading(t1 t2)*/ * FROM t1 INNER JOIN t2 ON t1.a1 = t2.a2
Merge Join (cost=0.31..190.01 rows=2260 width=16)
 Merge Cond: (t1.a1 = t2.a2)
 -> Index Scan using t1_pkey on t1 (cost=0.15..78.06 rows=2260 width=8)
 -> Index Scan using t2_pkey on t2 (cost=0.15..78.06 rows=2260 width=8)
1> SET BABELFISH_SHOWPLAN_ALL OFF;
2> GO

##Limitations

While using the query hints, consider the following limitations:

  • If a query plan is cached before enable_pg_hint is turned on, hints won’t be applied in the same session. It will be applied in the new session .

  • If schema names are explicitly given, then hints can’t be applied. You can use table aliases as a workaround.

  • A query hint can’t be applied to views and sub-queries.

  • Hints don’t work for UPDATE/DELETE statements with JOINs.

  • An index hint for a non-existing index or table is ignored.

  • The FORCE ORDER hint doesn’t work for HASH JOINs and non-ANSI JOINs.

Babelfish for PostgreSQL Links