• R/O
  • HTTP
  • SSH

pg_hint_plan: Commit

firtst release

Commit MetaInfo

Revision0f46b55c42597c9e50b03eefd668233e6ab1bb2c (tree)
Time2013-03-04 17:48:59
Authornaoki_kishi_b1 <kishi_naoki_b1@lab....>

Log Message

Signed-off-by: naoki_kishi_b1 <kishi_naoki_b1@lab.ntt.co.jp>

Change Summary

Incremental Difference

--- a/doc/pg_hint_plan.html
+++ b/doc/pg_hint_plan.html
@@ -113,29 +113,34 @@ Pg_hint_plan can tweak planner for table scan methods, join methods, join orders
113113 </tbody>
114114 </table>
116-<h2 id="install">Install</h2>
117-<p>Installation of pg_hint_plan. </p>
119-<h3 id="build">build</h3>
120-<p>To build pg_hint_plan from source code, in the pg_hint_plan's source directory "make → make install"
121- by the user who installed PostgreSQL.
122- And building with RPMs needs postgresql-devel package because pgxs is used for building of pg_hint_plan.</p>
123-<p>Example of build is given below</p>
116+<h2 id="install">Installation</h2>
118+<h3 id="build">Build</h3>
119+<p>do 'make' in the base directory of pg_hint_plan then 'make
120+ install' as the same OS user as PostgreSQL. If you installed
121+ PostgreSQL by RPM, it should be installed from postgresq-devel*
122+ package.Example of build is given below</p>
124124 <pre>
125-$ tar xzvf pg_hint_plan-1.0.0.tar.gz
126-$ cd pg_hint_plan-1.0.0
127-$ make
128-$ su
129-# make install
125+user$ tar xzvf pg_hint_plan-1.0.0.tar.gz
126+user$ cd pg_hint_plan-1.0.0
127+user$ make
128+user$ su postgres
129+posrgres$ make install
130130 </pre>
132-<h3 id="hint-load">Loding pg_hint_plan</h3>
133-<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.
132+<h3 id="hint-load">Loading pg_hint_plan</h3>
133+<p>If you want to use pg_hint_plan only in current session, you can
134+ load it by LOAD command of PostgreSQL like the example session
135+ below. As described in PostgreSQL documentation, non-superusers
136+ can only apply LOAD to library files located in $libdir/plugins.
134137 <pre>
135138 postgres=# LOAD 'pg_hint_plan';
136139 LOAD
137140 </pre></p>
138-<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>
141+<p>If you want to load pg_hint_plan automatically in every session,
142+ add 'pg_hint_plan' to shared_preload_libraries in
143+ postgresql.conf and restart the server.</p>
139144 <p>
140145 You can see the typical setting in postgresql.conf below.
141146 </p>
@@ -152,8 +157,8 @@ When you use PostgreSQL 9.1 with pg_hint_plan, setting custom_variable_classes i
154159 <h2 id="uninstall">Unistall</h2>
155-<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>
156-<p>Example of un-install below</p>
160+<p>do 'make uninstall' in the same directory as installation and as
161+ the same user as PostgreSQL.</p>
157162 <pre>
158163 $ cd pg_hint_plan-1.0.0
159164 $ su
@@ -161,8 +166,10 @@ $ su
161166 </pre>
163168 <h2 id="examples">Examples</h2>
164-<h3>Scan method</h3>
165-<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.
169+<h3>Tweaking scan methods.</h3>
170+<p>In the example below, SeqScan(t1) tells that the user wants to
171+ scan t1 with sequential scan, and IndexScan(t2 t2_pkey) tells
172+ that index scan using t2_pkey is preferred to scan t2.
166173 <pre>
167174 postgres=# /*+
168175 postgres*# SeqScan(t1)
@@ -173,8 +180,15 @@ postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
173180 </pre>
174181 </p>
176-<h3>Join method or order</h3>
177-<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.
183+<h3>Tweaking join method and joining order.</h3>
184+<p>NestLoop(t1 t2) tells the user wants that neted loop should be
185+ applied to the join operation covering t1 and t2, and
186+ MergeJoin(t1 t2 t3) tells merge join is preferred for the join
187+ operation covering all of the three tables t1, t2, t3, not the
188+ part of them. Leading(t1 t2 t3) tells that join operations
189+ should take place in the order even if the planner could find
190+ the cheaper plan doing joins in another order. The planner
191+ finally picks up the plan shown below.
178192 <pre>
179193 postgres=# /*+
180194 postgres*# NestLoop(t1 t2)
@@ -188,9 +202,11 @@ postgres-# JOIN table table3 t3 ON (t2.key = t3.key);
188202 </pre>
189203 </p>
205+<h3>Temporary GUC setting</h3>
192206 <p>
193-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.
207+Set(random_page_cost 2.0) tells that the planner gets 2.0 as the
208+ value of random_page_cost for planning the subsequent
209+ statements.
194210 <pre>
195211 postgres=# /*+
196212 postgres*# Set(random_page_cost 2.0)
@@ -201,10 +217,13 @@ postgres-# SELECT * FROM table1 t1 WHERE key = 'value';
201217 </p>
203219 <h2 id="restrictions">Restrictions</h2>
204-<h3>Rule of writing hints</h3>
220+<h3>This section describes the miscellaneous restrictions on scripting hints.</h3>
205221 <dl>
206-<dt>Position of Hints</dt>
207-<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>
222+<dt>Position of the hints</dt>
223+<dd>pg_hint_plan reads only the first block commnet in the query
224+ string. So no hint written after that has any effect for
225+ pg_hint_plan. In the following example, HashJoin and SeqScan is
226+ in effect but IndexScan is not.</p>
208227 <pre>
209228 postgres=# /*+
210229 postgres*# <span class="strong">HashJoin(a b)</span>
@@ -228,13 +247,20 @@ postgres-# ORDER BY a.aid;
229248 postgres=# </pre>
230249 </dd>
231-<dt>Object name in inverted commas</dt>
232-<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.
233-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"’).
250+<dt>Object names in quotes</dt>
251+<dd>Following the PostgreSQL's lexical structure, names can be
252+ quoted in order to contain some special characters in
253+ pg_hint_plan.
234254 </dd>
235-<dt>Classification of tables with same name </dt>
236-<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.
237-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.
255+<dt>Distinguishing multiple tables with same name.</dt>
256+<dd>pg_hint_plan identifies objects by only its names, not the
257+ qualified names. So pg_hint_plan can not distinguish between
258+ tables with the same name in multiple schemas. In these cases,
259+ aliasing the ambiguous tables should help. In the first example
260+ below, pg_hint_plan has found multiple candidates for the given
261+ name 't1' so logs that and the hint is ignored. In the next
262+ example, pg_hint_plan could distinguish the two tables since
263+ they are given as diferrent alias names.
238264 </p>
239265 <pre>
240266 postgres=# /*+ <span class="strong">HashJoin(t1 t1)</span>*/
@@ -269,14 +295,31 @@ postgres-# JOIN public.t1 pt ON (st.id=pt.id);
269295 postgres=#</pre>
270296 </p>
271297 </dd>
272-<dt>Limitations when VALUES command is specified in FROM clause</dt>
273-<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>
298+<dt>Limitations of VALUES Expressions in FROM</dt>
299+<dd> Specify "*VALUES*" as a object name to tweak
300+ any method on a result of VALUES Expression in FROM clause.
301+ Because alias name for VALUES result is substituted to "*VALUES*" by PostgreSQL,
302+ So when using multiple VALUES,
303+ target of the hint cannot be specified ,so execution plan cannot be controlled. </dd>
274304 </dl>
275-<h3>Target application of Hint</h3>
305+<h3>Specifying Target objects of Hint</h3>
276306 <dl>
277-<dt>Hint application for the table which is not clarified in query </dt>
278-<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>
279-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.
307+<dt>Tables not clarified in query as that in views or in SQL functions</dt>
308+<dd>Since pg_hint_plan identifies objects by the names or aliases
309+ the planner sees, the names in views or quiries in SQL functions
310+ appear in the target query are also valid as identifiers.
311+ So, within the whole extent of the query where the planner sees
312+ at once, the given name is regarded as ambiguous when it refers
313+ diferrent objects in the portions implicitly included into the
314+ query, say, views or SQL functions. Conversely, multiple views
315+ with the same logical definition with the diferrent alias sets
316+ given can be applied diferrent hints using the aliases.</br>
317+In the first of the following examples, alias 't1' is refers
318+both table1 in view1 and same table1 in the main query. So t1 is
319+not ambiguous and SeqScan(t1) was applied to both the view and
320+the main query. In the second expample, table1 in the main query
321+aliased as 't3' which is different to 't1' in the view. So
322+SeqScan(t3) is applied only to the table1 in main query.
280323 <pre>
281324 postgres=# CREATE VIEW view1 AS SELECT * FROM table1 <span class="strong">t1</span>;
@@ -304,77 +347,111 @@ postgres=# EXPLAIN SELECT * FROM table1 <span class="strong">t3</span> JOIN view
305348 </pre>
306349 </dd>
307-<dt>Hint for inherit table </dt>
308-<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>
350+<dt>Applying hints to child tables.</dt>
351+<dd>pg_hint_plan is conscious of inheritances. The hints about scans
352+ on the parent table is also applied automatically on the childs
353+ if it is applicable.Now , It's only for table-name.</dd>
310-<dt>Hint for query using view or rule </dt>
311-<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
312-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>
355+<dt>Hint scope for RULE</dt>
356+<dd>The statements triggered by RULEs share the same hints with the
357+ triggering query.</dd>
314-<dt>Applicable scope of hint in the query using rule</dt>
315-<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>
359+<dt>Hint scope for multi statement</dt>
360+<dd>Each queries in multi statement share the same hints in the
361+ first block commnet before the first queriy. Hint notations in
362+ other places are ignored.</dd>
317364 <dt>Applicable scope of hint in multi statement </dt>
318365 <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  c option in psql command </dd>
320-<dt>Specify IndexOnlyScan hint (PostgreSQL 9.2 onwards)</dt>
321-<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>
367+<dt>IndexOnlyScan hint (PostgreSQL 9.2 onwards)</dt>
368+<dd>IndexOnlyScan for the tables which has multiple indexes and
369+ without explicit index specification sometimes failes to force
370+ index only scan since the planner decided to use another index
371+ on which index only scan cannot be performed. Explicit index
372+ specification could help the case</dd>
323-<dt>Precaution points for NoIndexScan hint (PostgreSQL 9.2 onwards)</dt>
324-<dd>When NoIndexScan hint is specified from PostgreSQL 9.2, Index Scan and Index Only Scan are not selected.</dd>
374+<dt>NoIndexScan on PostgreSQL 9.2 or the newer.</dt>
375+<dd>NoIndexScan inhibits both index scan and index only scan on
376+ PostgrteSQL 9.2 or the newer.</dd>
326378 </dl>
328-<h3>Handling Hint specification error</h3>
380+<h3>Handling parse errors of hints</h3>
329381 <dt>Syntax error </dt>
330-<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.
382+<dd>pg_hint_plan immediately stops hint parsing on parse error and
383+ then runs the query body applying the hints successfully parsed
384+ so far. The details of the error is logged in server log which
385+ has the error level in pg_hint_plan.parse_messages.
386+ Typical syntax errors follows,
331387 <ul>
332388 <li>Hint name is incorrect. </li>
333-<li>Object specification cannot be included into brackets, correctly.</li>
334-<li>Object name is not separated in blank。</li>
389+<li>Targetted object name is not in brackets, correctly .</li>
390+<li>Object name is not separated by blank。</li>
335391 </ul>
336392 </dd>
337-<dt>Object specification error </dt>
338-<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
393+<dt>Object not found</dt>
394+<dd>pg_hint_plan skips the hint and to parse the next hint on the
395+ object in the hint is not found. The details of the error is
396+ logged. Example below.
339397 <ul>
340-<li>There is table name with same title or table with alias name in query, specified hint for that. </li>
341-<li>Same object name is specified multiple times to combination method or combination order hint </li>
398+<li>Duplicate name or alias name of targetted table in a query, specified hint for that. </li>
399+<li>Redundant object names found in one hint.</li>
342400 </ul>
343401 </dd>
344-<dt>Redundant hint types to be specified </dt>
345-<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>
402+<dt>Conflicting between the hints with same targets.</dt>
403+<dd>If two or more hints in conflicting types occurs with same
404+ target list, the last one is in effect.</dd>
406+<dt>Nested comment in the comment for hints</dt>
407+<dd>pg_hint_plan cease to parse the hint comment when encountering
408+ the start of another block comment, abandon all hint information
409+ and then run the query body without applying any hint. The
410+ details of the error is logged.</dd>
348-<dt>Nested block comment</dt>
349-<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>
351-<dt>Message Output level </dt>
352-<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>
412+<dt>Log level for pg_hint_plan. </dt>
413+<dd>pg_hint_plan puts almost informations into system log with the
414+ log level in the GUC pg_hint_plan.parse_messages. Only the
415+ messages informing overlength of the database identifiers (max
416+ 63 bytes) will always logged as NOTICE.</dd>
355419 <h3>Functional limitations</h3>
356-<dt>Impact of standard GUC parameter</dt>
357-<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>
358-<dt>Case which cannot be controlled in hint </dt>
359-<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.
420+<dt>Limitaion by GUC parameters</dt>
421+<dd>The hints for JOIN are ignored when the length of the target
422+ list of the hints exceeding from_collapse_limit or
423+ join_collapse_limit.</dd>
424+<dt>The cases when hints does not work as expected.</dt>
425+<dd>Every hint could not be in effect because of the nature of the
426+ planner's calculations. For instance, the following reasons
427+ might be guessed.
360428 <ul>
361-<li>Nest Loop is not treated as candidate path in FULL OUTER JOIN</li>
362-<li>Index that contains only row which is not specified to WHERE clause or JOIN clause is not treated as candidate path</li>
363-<li>When ctid is not specified to search conditions, Tid Scan is not treated as candidate path.</li>
429+<li>Nested loop does not become a candidate path in FULL OUTER JOIN.</li>
430+<li>Indexes consists only of the columns which does not appear in
431+ WHERE or JOIN clauses does not become a candidate path.</li>
432+<li>TidScan hint requires using ctid in search conditions to become
433+ a candidate path.</li>
364434 </ul>
365435 </dd>
366436 <dt>Limitations in PL/pgSQL</dt>
367-<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>
437+<dd>Hints cannot be used in PL/pgSQL because the comments are not passed to
438+ planner on execution.
368440 <dt>Limitations in ECPG</dt>
369-<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.
370-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.
441+<dd>Hints cannot be used also in ECPG because the comments are not
442+ passed to the server.
371443 </dd>
372444 <dt>Specify fetch psql count</dt>
373-<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
374-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.
445+<dd> psql attaches DECLARE statement to the top of existing query
446+ string when the FETCH_COUNT variable is a positive value . So,
447+ the hints seems dissapearing for pg_hint_plan.
375448 </dd>
376-<dt>Change of finger print by Hin</dt>
377-<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>
449+<dt>Difference in the fingerprint calculation</dt>
450+<dd>On PosrgreSQL 9.1, query cache key is calculated including
451+ comments, so same queries with different hints spoils it. But
452+ 9.1 and after calculates query cache key excluding comments so
453+ the query cache can work effectively for the same queries with
454+ different hints.</dd>
378455 <pre>
379456 postgres=# /*+ <span class="strong">MergeJoin(a *VALUES*)</span> */
380457 postgres-# EXPLAIN SELECT *
@@ -393,10 +470,18 @@ postgres-# ORDER BY a.aid;
394471 postgres=#</pre>
395472 <dt>Limitations of Set Hint </dt>
396-<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.
473+<dd>Although you can put the Set hint for <a href="#hint-GUC">GUC parameters such like 'pg_hint_plan' </a>
474+which has no effect for planner behavior, you
475+ will find it doesn't work in your favor. So it is discuraged
476+ without specific intention and knowledge.
477+ Actual actions when specified are shown below.
397479 <ul>
398-<li>When pg_hint_plan.enable_hint and pg_hint_plan.debug_print are specified, they will be ignored. </li>
399-<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>
480+<li>pg_hint_plan.enable_hint and pg_hint_plan.debug_print in Set
481+ hints notations will be intentionary ignored.</li>
482+<li>pg_hint_plan.parse_messages in Set hints is ignored
483+ in message for Syntax error and a part of the error about Set Hint,
484+ and applies the other messages. </li>
400485 </ul>
401486 </dd>
@@ -421,7 +506,7 @@ postgres=#</pre>
421506 <a href="pg_hint_plan.html">pg_hint_plan</a>
422507 </div>
424-<p class="footer">Copyright (c) 2012, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
509+<p class="footer">Copyright (c) 2013, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
426511 <!--
427512 <script type="text/javascript">
Show on old repository browser