pg_hint_plan 1.0.0

Name
Synopsis
Description
Install
Uninstall
Examples
Restrictions
Requirements
See Also
Appendix A. Hints list

name

pg_hint_plan -- controls execution plan with Hint information in a particular comment

Synopsis

PostgreSQL uses cost based optimizer. Based on SQL statement and statistics, cost of possible access path is estimated and execution plan with lowest cost is selected. Optimizer tries to use best execution plan make as far as possible but as it does not refer to the correlation etc., between columns, it cannot be said that in case of complicated queries always proper plan will get selected.

If pg_hint_plan is used then without changing SQL statement or GUC parameter, execution plan can be controlled by adding block comment having description of hint, before the SQL sentence.

Description

How-to

Write the Hint in the beginning of query character string in block comment. In order to make the block comment recognize as Hint, need to specify plus (+) immediately in the opening of block comment. Specify the Hint targeted items by object name or alias name in brackets. Specify the object name by differentiating it by using space, tab or new line.

In the example below , Hash Join execution plan is selected for the SeqScan for pgbench_accounts table from HashJoin and SeqScan hint

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# EXPLAIN SELECT *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

postgres=#

hint group

Hint that can be used in pg_hint_plan, is divided into 4 groups which are scan method and integration method, integration sequence, GUC parameter. For specific Hint of each group see Hint list

Scan method

With these hints , you can force or restraint the optimizer to select specific scan method for tables or columns.It includes 'SeqScan' , 'IndexScan' etc.

Usual table, inheritance table, UNLOGGED table, temporary table, system catalog can be specified Scan method. And external table, table function, VALUES command result, CTE, View, Sub-enquiry result cannnot be.

Join method

With these hints , you can force or restraint the optimizer to select specific join method for combination of tables.It includes 'MergeJoin’ and ‘NestLoop’ etc.MergeJoin’ and ‘NestLoop’ etc.

usual tale, inheritance table, UNLOGGED table, temporary table, external table, system catalog, table function, VALUES command result, CTE can be specified join method. And view and sub inquiry result cannot be.

Join Order

‘Leading’hint forces join order in which table is specified.

GUC

'Set' hint changes GUC parameters just while creating execution plan for this query.

Only GUC parameter of Query Planning will have the effect. If Hint of multiple GUC parameters is specified for same GUC parameter, Hint specified in the last will be applicable.

You can use GUC of pg_hint_plan in 'Set' hint,but since it does not work as per expectation, it is recommended that not to specify. See also Restrictions.

GUC of pg_hint_plan

The GUC parameters for pg_hint_planpg_hint_plan is below.

GUC	discription	Default
pg_hint_plan.enable_hint	Enbles or disables the function of pg_hint_plan.	on
pg_hint_plan.debug_print	Enables the debug output of pg_hint_plan. Since message will be output at LOG message level, by default it will be output in server log and will not be passed to the client.	off
pg_hint_plan.parse_messages	When specified Hint cannot be interpret, then in which message level log should be output, is specified. Valid values are debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, or error. Fatal and panic cannot be specified.

For PostgreSQL 9.1 , it is required to set these parameters in postgresql.conf file to add 'pg_hint_plan' for custom_variable_classes. Typical usage example of this is below.

# postgresql.conf
shared_preload_libraries = 'pg_hint_plan'

custom_variable_classes = 'pg_hint_plan'    # not required for 9.2 or after
pg_hint_plan.parse_messages = 'debug2'

Since PostgreSQL 9.2 onwards custom_variable_classes are deleted, it can be written similarly as GUC parameter which is based on GUC parameter of pg_hint_plan standards

Install

Installation of pg_hint_plan.

build

To build pg_hint_plan from source code , in the pg_hint_plan's source directory "make → make install" by the OS user in which PostgreSQL is installed. Since pgxs is used for building of pg_hint_plan, postgresql-devel package is needed in the environment where PostgreSQL of RPM version is used.

Example of build is given below

$ tar xzvf pg_hint_plan-1.0.0.tar.gz
$ cd pg_hint_plan-1.0.0
$ make
$ su
# make install

Loding pg_hint_plan

When using the pg_hint_plan in a particular session only, load shared library of pg_hint_plan by using LOAD command as shown in the below example. When using as a general user, be careful as it needs to be installed in $libdir/plugins aswell.

postgres=# LOAD 'pg_hint_plan';
LOAD
postgres=#

To activate pg_hint_plan in all sessions, add ‘pg_hint_plan’ to shared_preload_libraries GUC parameter and then re-start the server.

Unistall

To uninstall pg_hint_plan, run ‘make uninstall’ in directory in which pg_hint_plan source is deployed. Run ‘make uninstall’ by the OS user in which PostgreSQL is installed.

Example of un-install below

$ cd pg_hint_plan-1.0.0
$ su
# make uninstall

How to use

Scan method

In the Hint of Scan method, table to be scanned is specified. When wish to use particular index in IndexScan Hint, index can also be specified in options. In the following example, table1 selects Seq Scan, table2 selects Index Scan in main key index.

postgres=# /*+
postgres*#     SeqScan(t1)
postgres*#     IndexScan(t2 t2_pkey)
postgres*#  */
postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
...

Join method or order

In the Hint of join method or join order list of tables to be joined is specified. Following example shows the case that make table1 and tabele2 join directory with Nested Loop and makes table1, table2 and table3 join with Merge Join . But, depending on the cost estimation, there can be a case wherein table1 and table2 cannot be integrated directly hence Leading Hint is used simultaneously in such way that table1 and table2 are joined first and then table3 is joined.

postgres=# /*+
postgres*#     NestLoop(t1 t2)
postgres*#     MergeJoin(t1 t2 t3)
postgres*#     Leading(t1 t2 t3)
postgres*#  */
postgres-# SELECT * FROM table1 t1
postgres-#     JOIN table table2 t2 ON (t1.key = t2.key)
postgres-#     JOIN table table3 t3 ON (t2.key = t3.key);
...

GUC

In the Hint of GUC parameter, pair of GUC parameter and value is specified. In the following example, only while creating execution plan of this query change random_page_cost to 2.0.

postgres=# /*+
postgres*#     Set(random_page_cost 2.0)
postgres*#  */
postgres-# SELECT * FROM table1 t1 WHERE key = 'value';
...

Restrictions

Rule of writing hints

Position of Hints

When writing multiple block comments before query, write the hint in the first block comment only. Block comments from 2nd onwards will not be noted as Hint and will be ignored. In the following example HashJoin(a b) and SeqScan(a) are considered as Hint and IndexScan(a) and MergeJoin(a b) are ignored.

postgres=# /*+
postgres*#    HashJoin(a b)
postgres*#    SeqScan(a)
postgres*#  */
postgres-# /*+ IndexScan(a) */
postgres-# EXPLAIN SELECT /*+ MergeJoin(a b) */ *
postgres-#    FROM pgbench_branches b
postgres-#    JOIN pgbench_accounts a ON b.bid = a.bid
postgres-#   ORDER BY a.aid;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Sort  (cost=31465.84..31715.84 rows=100000 width=197)
   Sort Key: a.aid
   ->  Hash Join  (cost=1.02..4016.02 rows=100000 width=197)
         Hash Cond: (a.bid = b.bid)
         ->  Seq Scan on pgbench_accounts a  (cost=0.00..2640.00 rows=100000 width=97)
         ->  Hash  (cost=1.01..1.01 rows=1 width=100)
               ->  Seq Scan on pgbench_branches b  (cost=0.00..1.01 rows=1 width=100)
(7 rows)

postgres=#

Object name in inverted commas

When closing bracket ()), double quotes (“), blank (any one from space, tab, new line) is included in object name or other name to be described in Hint in that case enclose it with double quotes (“) same as used for SQL sentence normally. For the object name having double quotes, apply double quotes to it and escape the double quotes with double quotes. (for example: ‘quoted"table"name’ → ‘"quoted""table""name"’).

Classification of tables with same name

When tables of same name are appeared multiple times during query because of using different schema or same tables multiple times, give alias name to the tables and classify the tables respectively. The example of first SQL sentence shown below is, when HashJoin (t1 t1) is specified in Hint, object targeted for Hint cannot be specified and error occurs. Second example of SQL sentence is, since for each table different names like pt and st are given, Hash Join is selected as specified in Hint while creating an execution plan.

postgres=# /*+ HashJoin(t1 t1)*/
postgres-# EXPLAIN SELECT * FROM s1.t1
postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id);
INFO:  hint syntax error at or near "HashJoin(t1 t1)"
DETAIL:  Relation name "t1" is ambiguous.
                            QUERY PLAN
------------------------------------------------------------------
 Merge Join  (cost=337.49..781.49 rows=28800 width=8)
   Merge Cond: (s1.t1.id = public.t1.id)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: s1.t1.id
         ->  Seq Scan on t1  (cost=0.00..34.00 rows=2400 width=4)
   ->  Sort  (cost=168.75..174.75 rows=2400 width=4)
         Sort Key: public.t1.id
         ->  Seq Scan on t1  (cost=0.00..34.00 rows=2400 width=4)
(8 行)

postgres=# /*+ HashJoin(pt st) */
postgres-# EXPLAIN SELECT * FROM s1.t1 st
postgres-# JOIN public.t1 pt ON (st.id=pt.id);
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=64.00..1112.00 rows=28800 width=8)
   Hash Cond: (st.id = pt.id)
   ->  Seq Scan on t1 st  (cost=0.00..34.00 rows=2400 width=4)
   ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
         ->  Seq Scan on t1 pt  (cost=0.00..34.00 rows=2400 width=4)
(5 行)

postgres=#

Limitations when VALUES command is specified in FROM clause

Target application of Hint

Hint application for the table which is not clarified in query

If it matches with the name specified in Hint, then also for the tables which are appeared in view definition or query in function etc., Hint will be applicable same as in query specifying the Hint.For this, when wish to change whether to apply Hint or Hint to be applied, to their respective tables, specify different alias name.
In the following example, by specifying Alias name ‘t1’ that has been used in View definition, in SeqScan Hint, Seq Scan is selected by both table scan and scan through View. In actual table by specifying ‘t1’ that is a different alias name and which has been used in View definition, scan method can be controlled individually.

postgres=# CREATE VIEW view1 AS SELECT * FROM table1 t1;
CREATE TABLE
postgres=# /*+ SeqScan(t1) */
postgres=# EXPLAIN SELECT * FROM table1 t1 JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
                           QUERY PLAN
-----------------------------------------------------------------
 Nested Loop  (cost=0.00..358.01 rows=1 width=16)
   ->  Seq Scan on table1 t1  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
   ->  Seq Scan on table1 t1  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
(5 rows)

postgres=# /*+ SeqScan(t3) */
postgres=# EXPLAIN SELECT * FROM table1 t3 JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..187.29 rows=1 width=16)
   ->  Seq Scan on table1 t3  (cost=0.00..179.00 rows=1 width=8)
         Filter: (key = 1)
   ->  Index Scan using foo_pkey on table1 t1  (cost=0.00..8.28 rows=1 width=8)
         Index Cond: (key = 1)
(5 rows)

Hint for inherit table

Specify parent table name or alias name as object name, when specifying scan method hint to inherit table. Same scan method is selected for all child tables. Separate scan method cannot be specified for each child table.

Hint for query using view or rule

When table that defined VIEW or RULE is used in multiple, hint target cannot be demarcated when alias name of table inside each view or alias name of query table post re-writing of rule, is redundant When want to demarcate, do not make alias name of table in each view or alias name of query table after rule is re-written, redundant.

Applicable scope of hint in the query using rule

If there are multiple queries due to re-writing of queries as per rule, hint specified to the block comment at the start is used for all queries.

Applicable scope of hint in multi statement

When query is implemented in multi statements, hint specified in the block comment at the beginning is used in all queries. Hint specified from 2nd query onwards is ignored. It is executed in multi statement when multiple queries is specified in c option in psql command

Specify IndexOnlyScan hint (PostgreSQL 9.2 onwards)

Even if IndexOnlyScan hint is specified, when there are multiple indexes in table targeted for hint sometimes Index Scan gets selected. In this case, specify also the index for which Index Only Scan is selected in table, and do not just specify table to IndexOnlyScan hint. Index Only Scan that used this index is selected.

Precaution points for NoIndexScan hint (PostgreSQL 9.2 onwards)

When NoIndexScan hint is specified from PostgreSQL 9.2, Index Scan and Index Only Scan are not selected.

Handling Hint specification error

Syntax error

When there is syntax error in Hint description, pg_hint_plan executes query ignoring the subsequent hint of erred description and validates only the hint before erred description. The erred details are recorded in server log in the level specified by pg_hint_plan.parse_messages.

Hint name is incorrect.
Object specification cannot be included into brackets, correctly.
Object name is not separated in blank。

Object specification error

When there is error in hint targeted object specification, pg_hint_plan ignores only the incorrect hint and executes query using other hints. The erred details are recorded in server log in the level specified by pg_hint_plan.parse_messages. Example of erred object specification is shown below

There is table name with same title or table with alias name in query, specified hint for that.
Same object name is specified multiple times to combination method or combination order hint

Redundant hint types to be specified

If same group hint is made redundant and then specified for same object then the last hint specified in each group is used.

Nested block comment

In pg_hint_plan, nested block comment cannot be included in block comment where Hint is specified. When nested block comment is included, information related to erred description will be output but it will not end by giving an error. Query will be executed by ignoring all Hints.

Message Output level

Message level output when there is error in hint is the level specified to pg_hint_plan.parse_messages. The length of object specified to hint if it exceeds the maximum length of identifier (63 byte by default) then it is output in NOTICE

Functional limitations

Impact of standard GUC parameter

Combination order hint is ignored when FROM list count is more than setting value of from_collapse_limit, or when FROM list count is much larger than setting value of join_collapse_limit. Increase these GUC parameter values to use hint.

Case which cannot be controlled in hint

Even if execution plan for which PostgreSQL planner cannot be considered as candidate is specified to hint, this execution plan cannot be generated. Example of execution plan for which PostgreSQL planner cannot be considered as execution plan is shown below.

Nest Loop is not treated as candidate path in FULL OUTER JOIN
Index that contains only row which is not specified to WHERE clause or JOIN clause is not treated as candidate path
When ctid is not specified to search conditions, Tid Scan is not treated as candidate path.

Limitations in PL/pgSQL

Hint is ignored even if hint is specified at the beginning to each query in function definition when user definition function in PL/pgSQL is implemented. Hint specified to SELECT command implementing user definition function is used. It is not necessary that the query specified in function definition is implemented as it is in PL/pgSQL so the behavior when hint is specified cannot be guaranteed.

Limitations in ECPG

Execution plan cannot be controlled for query issued from application implemented in ECPG in pg_hint_plan. This is because, all block comments are removed when C pre-processor is converted into C code. Execution plan can be controlled in ECPG by specifying hint at the beginning of query character string when there is dynamic SQL that implements query stored in C language character string by EXECUTE command.

Specify fetch psql count

Execution plan cannot be controlled in pg_hint_plan if integer value of more than 0 is specified to FETCH_COUNT variable of psql command. If integer value of more than 0 is specified to FETCH_COUNT variable then “DECLARE _psql_cursor NO SCROLL CURSOR FOR” is automatically added at the beginning of query specified by user and query is issued and hint disappears from the beginning of query.

Change of finger print by Hin

Since pg_hint_plan specifies the Hint in SQL comment, on the query cache etc., of SQL sentence fingerprint base, it is treated as different SQL statement if Hint is different. On 9.1, pg_stat_statement is also calculated as separate query. As on 9.2, comment is removed by query gathering function, query whose Hint itself is different is not handled as same query.

postgres=# /*+ MergeJoin(a *VALUES*) */
postgres-# EXPLAIN SELECT *
postgres-#    FROM pgbench_accounts a
postgres-#    JOIN (VALUES (1,1),(2,2)) v (vid, vbalance) ON a.aid = v.vid
postgres-#   ORDER BY a.aid;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=0.04..4497.33 rows=2 width=105)
   Merge Cond: (a.aid = "*VALUES*".column1)
   ->  Index Scan using pgbench_accounts_pkey on pgbench_accounts a  (cost=0.00..4247.26 rows=100000 width=97)
   ->  Sort  (cost=0.04..0.04 rows=2 width=8)
         Sort Key: "*VALUES*".column1
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8)
(6 rows)

postgres=#

Limitations of Set Hint

ou can use GUC of pg_hint_plan in 'Set' hint,but since it does not work as per expectation, it is recommended that not to specify.Actual actions when specified are shown below.

When pg_hint_plan.enable_hint and pg_hint_plan.debug_print are specified, they will be ignored.
When pg_hint_plan.parse_messages is specified, message regarding Syntax error and error of partial Set Hint is output at setting level while starting the query and messages other than this are output at level specified in Set Hint.

requirement

PostgreSQL: Version 9.1.4、9.2.1
OS: RHEL 6.1

pg_hint_plan 1.0.0

name

Synopsis

Description

How-to

hint group

Scan method

Join method

Join Order

GUC

GUC of pg_hint_plan

Install

build

Loding pg_hint_plan

Unistall

How to use

Scan method

Join method or order

GUC

Restrictions

Rule of writing hints

Target application of Hint

Handling Hint specification error

Functional limitations

requirement

See also

PostgreSQL documents