12.1.1 Optimizer Hints

This section explains the basic feature content of the optimizer hint (pg_hint_plan).

Refer to the open-source software webpage for information on pg_hint_plan.

In Symfoware Server, the optimizer hints can be specified in all application interfaces.

Description

You can specify a query plan in each SQL statement.

List of Features

The main query plans that can be specified using this feature are as follows:

Query methods

Specify which method to use to query the specified table.

The main features are as follows:

Note

If the specified index does not exist, or is not related to the search condition column specified in the WHERE clause, for example, SeqScan will be used.
Even if IndexOnlyScan is specified, IndexScan may be used if it is necessary to access the table because a row was updated, for example.
If multiple query methods were specified for the same table, the method specified last will be used.

Join methods

Specify the join method.

The main features are as follows:

Note

These cannot be specified for view tables and subqueries.
If multiple methods were specified for the same table combination, the method specified last will be used.

Join sequences

The tables will be joined in the specified table sequence.

Specify the information using the following method:

Leading ((table table))
The method used to specify [table] is as follows:
table = tableName or ( table table )

Note

If multiple sequences were specified for the same table combination, the sequence specified last will be used.

Usage method

The use of this feature is explained below.

Preparation

The following preparation is required to use this feature.

Run CREATE EXTENSION for the database that uses this feature.
The target database is described as "postgres" here.
Use the psql command to connect to the "postgres" database.
Example
```
Postgres=# CREATE EXTENSION pg_hint_plan;
CREATE EXTENSION
```
Information
Hereafter, also perform this preparatory task for the "template1" database when creating a new database, so that this feature can be used by default.
Set the postgresql.conf file parameters.
Add "pg_hint_plan" to the "shared_preload_libraries" parameter.
Restart Symfoware Server.

Method used to define this feature

Define this feature by specifying the format (block comment) " /*+ ... */".

To specify hint clauses in each SELECT statement, for example when there are multiple SELECT statements in the SQL statement, define all hint clauses in the first block comment.
Example
Specifying hint clauses for the emp table and the dept table
```
WITH /*+ IndexScan(emp emp_age_index) IndexScan(dept dept_deptno_index) */ age30
AS (SELECT * FROM emp WHERE age BETWEEN 30 AND 39)
SELECT * FROM age30, dept WHERE age30.deptno = dept.deptno;
```
To specify separate hint clauses for the same object in the SQL statement, define aliases in each object, and then specify hint clauses for those aliases.
Example
Specifying separate hint clauses for the emp table
```
WITH /*+ SeqScan(ta) IndexScan(tb) */ over100
AS (SELECT empno FROM emp ta WHERE salary > 1000000)
SELECT * FROM emp tb, over100 WHERE tb.empno = over100.empno AND tb.age < 30
```
When using embedded SQL in C, the locations in which the hint clause block comment is specified are restricted. Refer to "6.4.2 Compiling Applications" for details.

Usage notes

If a hint clause was specified in multiple block comments in the SQL statement, the hint clause specified in the second block comment and thereafter will be ignored.
If characters other than those listed below appear before the hint clause in the SQL statement, they will be invalid even for hint clause block comments.
- Space, tab, line feed
- Letter (uppercase and lowercase), number
- Underscore, comma
- Brackets ()