PostgreSQL advanced server supports some optimizer hints.

Optimizer Hints are very useful feature for query performance tuning.

There is diversity of opinion that use of optimizer hints. But, It’s true that optimizer hints are very convenient way to statement tuning. So, almost DBMS vendors support optimizer hint. But, PostgreSQL doesn’t suppot optimizer hint. Instead of, PostgreSQL supports “planner method configuration”. For example, in case of join method, PostgreSQL supports “enable_hashjoin”, “enable_nestloop”, “enable_mergejoin”. So, if you need more than two join methods in a statement, “planner method configuration” maybe can’t control query execution plan that you want. That is very dangerous from a performance perspective.

Fortunately, PostgreSQL advanced server supports some optimizer hints. For example, ORDERED, INDEX, NO_INDEX, FULL, FIRST_ROWS, USE_NL, USE_HASH and so on. That is very basic hints for join order, access method and join method. And, there isn’t hints for sub query, view merge, push predicate and so on. But, basic hints can cover a lot of bad sql statement. Below is the result of my simple test.

1. Create a table and data loading.

create table dummy (c1 number);
 
begin 
 for i in 1..100000 loop
   insert into dummy values (i);
 end loop;
end;
/

create table t1 (c1 number, c2 number, c3 number, dummy char(100));
create index t1_idx1 on t1 (c1, c2, c3);
create index t1_idx2 on t1 (c2, c3);
create index t1_idx3 on t1 (c3);
 
create table t2 (c1 number, c2 number, c3 number, dummy char(100));
create index t2_idx1 on t2 (c1, c2, c3);
create index t2_idx2 on t2 (c2, c3);
create index t2_idx3 on t2 (c3);
 
create table t3 (c1 number, c2 number, c3 number, dummy char(100));
create index t3_idx1 on t3 (c1, c2, c3);
create index t3_idx2 on t3 (c2, c3);
create index t3_idx3 on t3 (c3);
 
create table t4 (c1 number, c2 number, c3 number, dummy char(100));
create index t4_idx1 on t1 (c1, c2, c3);
create index t4_idx2 on t1 (c2, c3);
create index t4_idx3 on t1 (c3);
 
insert into t1 select 1, mod(c1,100), mod(c1,1000), to_char('dummy'||i) from dummy where rownum<=100000;
insert into t2 select 1, mod(c1,100), mod(c1,1000), to_char('dummy'||i) from dummy where rownum<=10000;
insert into t3 select 1, mod(c1,100), mod(c1,1000), to_char('dummy'||i) from dummy where rownum<=100;
insert into t4 select 1, mod(c1,100), mod(c1,1000), to_char('dummy'||i) from dummy where rownum<=100;  

2. The test of INDEX hint.
As you can see, we can decide index that we want to use.

3. The test of FULL hint.
Using FULL hint, you can table full scan, even if table has indexes.

4. The test of hint about JOIN.
we can control join methods using ORDERED, USE_NL and USE_HASH hints. The optimizer hints perfectly applied to the execution plan.

sample 1>

sample 2>

sample 3>
In some case, Optimizer ignore the hint. But don’t worry!

Insert 100,000 rows into t3 table. And then re-parsing the statement. The optimizer hints perfectly applied to the execution plan.