</tbody>
</table>
-<h2 id="install">Install</h2>
-<p>Installation of pg_hint_plan. </p>
-
-<h3 id="build">build</h3>
-<p>To build pg_hint_plan from source code, in the pg_hint_plan's source directory "make → make install"
- by the user who installed PostgreSQL.
- And building with RPMs needs postgresql-devel package because pgxs is used for building of pg_hint_plan.</p>
-<p>Example of build is given below</p>
+<h2 id="install">Installation</h2>
+
+<h3 id="build">Build</h3>
+<p>do 'make' in the base directory of pg_hint_plan then 'make
+ install' as the same OS user as PostgreSQL. If you installed
+ PostgreSQL by RPM, it should be installed from postgresq-devel*
+ package.Example of build is given below</p>
+
<pre>
-$ tar xzvf pg_hint_plan-1.0.0.tar.gz
-$ cd pg_hint_plan-1.0.0
-$ make
-$ su
-# make install
+user$ tar xzvf pg_hint_plan-1.0.0.tar.gz
+user$ cd pg_hint_plan-1.0.0
+user$ make
+user$ su postgres
+posrgres$ make install
</pre>
-<h3 id="hint-load">Loding pg_hint_plan</h3>
-<p>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, it needs to be installed in $libdir/plugins aswell.
+<h3 id="hint-load">Loading pg_hint_plan</h3>
+<p>If you want to use pg_hint_plan only in current session, you can
+ load it by LOAD command of PostgreSQL like the example session
+ below. As described in PostgreSQL documentation, non-superusers
+ can only apply LOAD to library files located in $libdir/plugins.
<pre>
postgres=# LOAD 'pg_hint_plan';
LOAD
</pre></p>
-<p>To activate pg_hint_plan in all sessions, add ‘pg_hint_plan’ to shared_preload_libraries GUC parameter and then re-start the server. </p>
+<p>If you want to load pg_hint_plan automatically in every session,
+ add 'pg_hint_plan' to shared_preload_libraries in
+ postgresql.conf and restart the server.</p>
<p>
You can see the typical setting in postgresql.conf below.
</p>
<h2 id="uninstall">Unistall</h2>
-<p>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 who installed pg_hint_plan. </p>
-<p>Example of un-install below</p>
+<p>do 'make uninstall' in the same directory as installation and as
+ the same user as PostgreSQL.</p>
<pre>
$ cd pg_hint_plan-1.0.0
$ su
</pre>
<h2 id="examples">Examples</h2>
-<h3>Scan method</h3>
-<p>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.
+<h3>Tweaking scan methods.</h3>
+<p>In the example below, SeqScan(t1) tells that the user wants to
+ scan t1 with sequential scan, and IndexScan(t2 t2_pkey) tells
+ that index scan using t2_pkey is preferred to scan t2.
<pre>
postgres=# /*+
postgres*# SeqScan(t1)
</pre>
</p>
-<h3>Join method or order</h3>
-<p>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.
+<h3>Tweaking join method and joining order.</h3>
+<p>NestLoop(t1 t2) tells the user wants that neted loop should be
+ applied to the join operation covering t1 and t2, and
+ MergeJoin(t1 t2 t3) tells merge join is preferred for the join
+ operation covering all of the three tables t1, t2, t3, not the
+ part of them. Leading(t1 t2 t3) tells that join operations
+ should take place in the order even if the planner could find
+ the cheaper plan doing joins in another order. The planner
+ finally picks up the plan shown below.
<pre>
postgres=# /*+
postgres*# NestLoop(t1 t2)
</pre>
</p>
-<h3>GUC</h3>
+<h3>Temporary GUC setting</h3>
<p>
-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.
+Set(random_page_cost 2.0) tells that the planner gets 2.0 as the
+ value of random_page_cost for planning the subsequent
+ statements.
<pre>
postgres=# /*+
postgres*# Set(random_page_cost 2.0)
</p>
<h2 id="restrictions">Restrictions</h2>
-<h3>Rule of writing hints</h3>
+<h3>This section describes the miscellaneous restrictions on scripting hints.</h3>
<dl>
-<dt>Position of Hints</dt>
-<dd>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. </p>
+<dt>Position of the hints</dt>
+<dd>pg_hint_plan reads only the first block commnet in the query
+ string. So no hint written after that has any effect for
+ pg_hint_plan. In the following example, HashJoin and SeqScan is
+ in effect but IndexScan is not.</p>
<pre>
postgres=# /*+
postgres*# <span class="strong">HashJoin(a b)</span>
postgres=# </pre>
</dd>
-<dt>Object name in inverted commas</dt>
-<dd>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"’).
+<dt>Object names in quotes</dt>
+<dd>Following the PostgreSQL's lexical structure, names can be
+ quoted in order to contain some special characters in
+ pg_hint_plan.
</dd>
-<dt>Classification of tables with same name </dt>
-<dd>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.
+<dt>Distinguishing multiple tables with same name.</dt>
+<dd>pg_hint_plan identifies objects by only its names, not the
+ qualified names. So pg_hint_plan can not distinguish between
+ tables with the same name in multiple schemas. In these cases,
+ aliasing the ambiguous tables should help. In the first example
+ below, pg_hint_plan has found multiple candidates for the given
+ name 't1' so logs that and the hint is ignored. In the next
+ example, pg_hint_plan could distinguish the two tables since
+ they are given as diferrent alias names.
</p>
<pre>
postgres=# /*+ <span class="strong">HashJoin(t1 t1)</span>*/
postgres=#</pre>
</p>
</dd>
-<dt>Limitations when VALUES command is specified in FROM clause</dt>
-<ddSpecify “*VALUES*” to hint object name when VALUES command is specified to FROM clause. This is because name is substituted to “*VALUES*” at PostgreSQL side, even if separate name is specified to VALUES result. So when using multiple VALUES, hint target cannot be specified and thus execution plan cannot be controlled. </dd>
+<dt>Limitations of VALUES Expressions in FROM</dt>
+<dd> Specify "*VALUES*" as a object name to tweak
+ any method on a result of VALUES Expression in FROM clause.
+ Because alias name for VALUES result is substituted to "*VALUES*" by PostgreSQL,
+ So when using multiple VALUES,
+ target of the hint cannot be specified ,so execution plan cannot be controlled. </dd>
</dl>
-<h3>Target application of Hint</h3>
+<h3>Specifying Target objects of Hint</h3>
<dl>
-<dt>Hint application for the table which is not clarified in query </dt>
-<dd>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. </br>
-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.
+<dt>Tables not clarified in query as that in views or in SQL functions</dt>
+<dd>Since pg_hint_plan identifies objects by the names or aliases
+ the planner sees, the names in views or quiries in SQL functions
+ appear in the target query are also valid as identifiers.
+ So, within the whole extent of the query where the planner sees
+ at once, the given name is regarded as ambiguous when it refers
+ diferrent objects in the portions implicitly included into the
+ query, say, views or SQL functions. Conversely, multiple views
+ with the same logical definition with the diferrent alias sets
+ given can be applied diferrent hints using the aliases.</br>
+In the first of the following examples, alias 't1' is refers
+both table1 in view1 and same table1 in the main query. So t1 is
+not ambiguous and SeqScan(t1) was applied to both the view and
+the main query. In the second expample, table1 in the main query
+aliased as 't3' which is different to 't1' in the view. So
+SeqScan(t3) is applied only to the table1 in main query.
<pre>
postgres=# CREATE VIEW view1 AS SELECT * FROM table1 <span class="strong">t1</span>;
CREATE TABLE
</pre>
</dd>
-<dt>Hint for inherit table </dt>
-<dd>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. </dd>
+<dt>Applying hints to child tables.</dt>
+<dd>pg_hint_plan is conscious of inheritances. The hints about scans
+ on the parent table is also applied automatically on the childs
+ if it is applicable.Now , It's only for table-name.</dd>
-<dt>Hint for query using view or rule </dt>
-<dd>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.</dd>
+<dt>Hint scope for RULE</dt>
+<dd>The statements triggered by RULEs share the same hints with the
+ triggering query.</dd>
-<dt>Applicable scope of hint in the query using rule</dt>
-<dd>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.</dd>
+<dt>Hint scope for multi statement</dt>
+<dd>Each queries in multi statement share the same hints in the
+ first block commnet before the first queriy. Hint notations in
+ other places are ignored.</dd>
<dt>Applicable scope of hint in multi statement </dt>
<dd>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 \13 c option in psql command </dd>
-<dt>Specify IndexOnlyScan hint (PostgreSQL 9.2 onwards)</dt>
-<dd>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. </dd>
+<dt>IndexOnlyScan hint (PostgreSQL 9.2 onwards)</dt>
+<dd>IndexOnlyScan for the tables which has multiple indexes and
+ without explicit index specification sometimes failes to force
+ index only scan since the planner decided to use another index
+ on which index only scan cannot be performed. Explicit index
+ specification could help the case</dd>
-<dt>Precaution points for NoIndexScan hint (PostgreSQL 9.2 onwards)</dt>
-<dd>When NoIndexScan hint is specified from PostgreSQL 9.2, Index Scan and Index Only Scan are not selected.</dd>
+<dt>NoIndexScan on PostgreSQL 9.2 or the newer.</dt>
+<dd>NoIndexScan inhibits both index scan and index only scan on
+ PostgrteSQL 9.2 or the newer.</dd>
</dl>
-<h3>Handling Hint specification error</h3>
+<h3>Handling parse errors of hints</h3>
<dt>Syntax error </dt>
-<dd>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.
+<dd>pg_hint_plan immediately stops hint parsing on parse error and
+ then runs the query body applying the hints successfully parsed
+ so far. The details of the error is logged in server log which
+ has the error level in pg_hint_plan.parse_messages.
+ Typical syntax errors follows,
<ul>
<li>Hint name is incorrect. </li>
-<li>Object specification cannot be included into brackets, correctly.</li>
-<li>Object name is not separated in blank。</li>
+<li>Targetted object name is not in brackets, correctly .</li>
+<li>Object name is not separated by blank。</li>
</ul>
</dd>
-<dt>Object specification error </dt>
-<dd>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
+<dt>Object not found</dt>
+<dd>pg_hint_plan skips the hint and to parse the next hint on the
+ object in the hint is not found. The details of the error is
+ logged. Example below.
<ul>
-<li>There is table name with same title or table with alias name in query, specified hint for that. </li>
-<li>Same object name is specified multiple times to combination method or combination order hint </li>
+<li>Duplicate name or alias name of targetted table in a query, specified hint for that. </li>
+<li>Redundant object names found in one hint.</li>
</ul>
</dd>
-<dt>Redundant hint types to be specified </dt>
-<dd>If same group hint is made redundant and then specified for same object then the last hint specified in each group is used.</dd>
+<dt>Conflicting between the hints with same targets.</dt>
+<dd>If two or more hints in conflicting types occurs with same
+ target list, the last one is in effect.</dd>
+<dt>Nested comment in the comment for hints</dt>
+<dd>pg_hint_plan cease to parse the hint comment when encountering
+ the start of another block comment, abandon all hint information
+ and then run the query body without applying any hint. The
+ details of the error is logged.</dd>
-<dt>Nested block comment</dt>
-<dd>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.</dd>
-
-<dt>Message Output level </dt>
-<dd>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</dd>
+<dt>Log level for pg_hint_plan. </dt>
+<dd>pg_hint_plan puts almost informations into system log with the
+ log level in the GUC pg_hint_plan.parse_messages. Only the
+ messages informing overlength of the database identifiers (max
+ 63 bytes) will always logged as NOTICE.</dd>
<h3>Functional limitations</h3>
-<dt>Impact of standard GUC parameter</dt>
-<dd>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. </dd>
-<dt>Case which cannot be controlled in hint </dt>
-<dd>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.
+<dt>Limitaion by GUC parameters</dt>
+<dd>The hints for JOIN are ignored when the length of the target
+ list of the hints exceeding from_collapse_limit or
+ join_collapse_limit.</dd>
+<dt>The cases when hints does not work as expected.</dt>
+<dd>Every hint could not be in effect because of the nature of the
+ planner's calculations. For instance, the following reasons
+ might be guessed.
<ul>
-<li>Nest Loop is not treated as candidate path in FULL OUTER JOIN</li>
-<li>Index that contains only row which is not specified to WHERE clause or JOIN clause is not treated as candidate path</li>
-<li>When ctid is not specified to search conditions, Tid Scan is not treated as candidate path.</li>
+<li>Nested loop does not become a candidate path in FULL OUTER JOIN.</li>
+<li>Indexes consists only of the columns which does not appear in
+ WHERE or JOIN clauses does not become a candidate path.</li>
+<li>TidScan hint requires using ctid in search conditions to become
+ a candidate path.</li>
</ul>
</dd>
<dt>Limitations in PL/pgSQL</dt>
-<dd>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.</dd>
+<dd>Hints cannot be used in PL/pgSQL because the comments are not passed to
+ planner on execution.
+</dd>
<dt>Limitations in ECPG</dt>
-<dd>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.
+<dd>Hints cannot be used also in ECPG because the comments are not
+ passed to the server.
</dd>
<dt>Specify fetch psql count</dt>
-<dd> 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.
+<dd> psql attaches DECLARE statement to the top of existing query
+ string when the FETCH_COUNT variable is a positive value . So,
+ the hints seems dissapearing for pg_hint_plan.
</dd>
-<dt>Change of finger print by Hin</dt>
-<dd>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.</dd>
+<dt>Difference in the fingerprint calculation</dt>
+<dd>On PosrgreSQL 9.1, query cache key is calculated including
+ comments, so same queries with different hints spoils it. But
+ 9.1 and after calculates query cache key excluding comments so
+ the query cache can work effectively for the same queries with
+ different hints.</dd>
<pre>
postgres=# /*+ <span class="strong">MergeJoin(a *VALUES*)</span> */
postgres-# EXPLAIN SELECT *
postgres=#</pre>
<dt>Limitations of Set Hint </dt>
-<dd>ou can use <a href="#hint-GUC">GUC of pg_hint_plan</a> 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.
+<dd>Although you can put the Set hint for <a href="#hint-GUC">GUC parameters such like 'pg_hint_plan' </a>
+which has no effect for planner behavior, you
+ will find it doesn't work in your favor. So it is discuraged
+ without specific intention and knowledge.
+ Actual actions when specified are shown below.
+
<ul>
-<li>When pg_hint_plan.enable_hint and pg_hint_plan.debug_print are specified, they will be ignored. </li>
-<li>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. </li>
+<li>pg_hint_plan.enable_hint and pg_hint_plan.debug_print in Set
+ hints notations will be intentionary ignored.</li>
+<li>pg_hint_plan.parse_messages in Set hints is ignored
+ in message for Syntax error and a part of the error about Set Hint,
+ and applies the other messages. </li>
</ul>
</dd>
<a href="pg_hint_plan.html">pg_hint_plan</a>
</div>
-<p class="footer">Copyright (c) 2012, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
+<p class="footer">Copyright (c) 2013, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
<!--
<script type="text/javascript">
OSDN Git Service
