Top
Enterprise Postgres 14 SP1 Application Development Guide
FUJITSU Software

9.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 FUJITSU Enterprise Postgres, 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:

  • SeqScan (tableName)

  • BitMapScan (tableName [indexName ... ])

  • IndexScan (tableName [indexName ... ])

  • IndexOnlyScan (tableName [indexName ... ])

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:

  • NestLoop (tableName tableName [tableName ... ])

  • MergeJoin (tableName tableName [tableName ... ])

  • HashJoin (tableName tableName [tableName ... ])

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.


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 "5.4.2 Compiling Applications" for details.

Usage notes