1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD html 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
4 <title>pg_hint_plan</title>
5 <!-- Uncoment after the tool has been hosted somewhere.
6 <link rel="home" title="pg_hint_plan" href="index.html">
8 <link rel="stylesheet" type="text/css" href="style.css">
9 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
13 <h1 id="pg_hint_plan">pg_hint_plan 1.0.0</h1>
14 <div class="navigation">
15 <a href="pg_hint_plan-ja.html">pg_hint_plan</a>
21 <li><a href="#name">Name</a></li>
22 <li><a href="#synopsis">Synopsis</a></li>
23 <li><a href="#description">Description</a>
24 <li><a href="#install">Install</a></li>
25 <li><a href="#uninstall">Uninstall</a></li>
26 <li><a href="#examples">Examples</a></li>
27 <li><a href="#restrictions">Restrictions</a></li>
28 <li><a href="#requirement">Requirements</a></li>
29 <li><a href="#seealso">See Also</a></li>
30 <li><a href="hint_list.html">Appendix A. Hints list</a></li>
33 <h2 id="name">name</h2>
34 <p>pg_hint_plan -- Tweaking planner decisions with user's hists.</p>
36 <h2 id="synopsis">Synopsis</h2>
37 <p>PostgreSQL executes queries using cost-based optimizer, and sometimes fails to choose most optimized execution plan for some types of queries.</p>
38 <p>In such cases, pg_hint_plan helps users to guide the planner to choose the plans they want by putting some instructions - call them hints - in the comments prefixed to the SQL statement body. No need to rewrite statement itself nor change some GUC parameters elsewhere.</p>
40 <h2 id="description">Description</h2>
42 <h3 id="hint-rule">General syntax</h3>
43 <p>Hints to planner should be scripted in a bit special comment surrounded by '/*+' and '*/' preceding target SQL statement body. Each hint is in the format of "HintName(target specs)" and separated from others by white spaces. "Target specs" is one table name for the hints for scans or table names separated with white spaces for the hints for joins or others. Set hint takes a pair of the GUC parameter name and the value to be set separated with white spaces.</p>
45 <p>In the example below, HashJoin(a b) instructs that hash join is preferable for joins covering table (designated with alias) a and b, and SeqScan(a) instructs that sequential scan is preferable for table a, and the selected plan is found to follow the order.</p>
48 postgres*# <span class="strong">HashJoin(a b)</span>
49 postgres*# <span class="strong">SeqScan(a)</span>
51 postgres-# EXPLAIN SELECT *
52 postgres-# FROM pgbench_branches b
53 postgres-# JOIN pgbench_accounts a ON b.bid = a.bid
54 postgres-# ORDER BY a.aid;
56 ---------------------------------------------------------------------------------------
57 Sort (cost=31465.84..31715.84 rows=100000 width=197)
59 -> <span class="strong">Hash Join</span> (cost=1.02..4016.02 rows=100000 width=197)
60 Hash Cond: (a.bid = b.bid)
61 -> <span class="strong">Seq Scan on pgbench_accounts a</span> (cost=0.00..2640.00 rows=100000 width=97)
62 -> Hash (cost=1.01..1.01 rows=1 width=100)
63 -> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=100)
68 <h3 id="hint-group">Tweakable operations</h3>
69 Pg_hint_plan can tweak planner for table scan methods, join methods, join orders, and temprary GUC setting during theplanner is running. For specific Hint of each group see <a href="hint_list.html">Hint list</a></p>
71 <h4>Tweaking scan method </h4>
72 <p>The hint for scan methods takes one table name or alias and will instruct planner to prefer the specific scan method for the table. You can use (No)SeqScan, (No)IndexScan, (No)TidScan, (No)BitmapScan.</p>
73 <p>These hints are applicable for ordinary tables including unlogged or temporary ones, and system catalogues. Other objects, for example, external tables, table functions, Views, CTE, or subquery results are not covered.</p>
75 <h4>Tweaking join method</h4>
76 <p>The hint for join methods takes two or more table names or alias and will instruct planner to prefer the specific method for the join operation covering all of the tables. For example, HashJoin(a b c) tells the planner to prefer hash join for both of join operations on a and the result of sub join on b and c, or on c and the result of sub join on a and b. You can use (No)NestLoop, (No)HashJoin, (No)MergeJoin.</p>
77 <p>These hints are applicable for ordinary tables including unlogged or temporary ones, system catalogues; besides, also applicable for table functions, VALUES clauses, CTEs. Views and subquery results are not covered.</p>
79 <h4>Tweaking join Order</h4>
80 <p>Leading(a b c ..) takes a list of objects which are join hints
81 are appliable to and suggests planner to do joins in that
82 order. For this example, join a and b first, then join the
83 result and c, and so on... In and out of each join operation
84 cannot be tweaked in current version.</p>
86 <h4>Temporary GUC setting</h4>
87 <p>The Set hint has somewhat different syntax to others. It takes a
88 pair of GUC parameter name and the value to be set, sets the
89 value to the parameter and restore to the previous value when
90 the planner finishes the work. So this hint has no effect on
91 execution. The last hint in Set hints for the same GUC parameter
94 <p> Only GUC parameter of <a href="http://www.postgresql.org/docs/current/static/runtime-config-query.html">Query Planning</a> will have the effect. If Hint of multiple GUC parameters is specified for same GUC parameter, Hint specified in the last will be applicable.</p>
95 <p>You 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. See also <a href="#restrictions">Restrictions</a>.</p>
97 <h3 id="hint-GUC">GUC parameters for pg_hint_plan</h3>
98 <p>GUC parameters to define the behaviors of pg_hint_plan are listed below.</p>
102 <tr><th>name</th><th>description</th><th>default value</th></tr>
105 <tr><td>pg_hint_plan.enable_hint</td>
106 <td>Enables or disables the all function of pg_hint_plan module.</td><td>on</td></tr>
107 <tr><td>pg_hint_plan.debug_print</td>
108 <td>Enables logging of pg_hint_plan's debugging informatin. The log records has the LOG LogLevel. </td><td>off</td></tr>
109 <tr><td>pg_hint_plan.parse_messages</td>
110 <td>Enables logging for parse failure of hints. The log level of the
111 records are specified by this parameter. Valid values are the
112 all loglevels smaller than fatal and null string.</td></tr>
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>
125 user$ tar xzvf pg_hint_plan-1.0.0.tar.gz
126 user$ cd pg_hint_plan-1.0.0
129 posrgres$ make install
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.
138 postgres=# LOAD 'pg_hint_plan';
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>
145 You can see the typical setting in postgresql.conf below.
149 shared_preload_libraries = 'pg_hint_plan'
151 custom_variable_classes = 'pg_hint_plan' # Only for 9.1
152 pg_hint_plan.parse_messages = 'debug2'
155 When you use PostgreSQL 9.1 with pg_hint_plan, setting custom_variable_classes is required for the class pg_hint_plan in postgresql.conf.
159 <h2 id="uninstall">Unistall</h2>
160 <p>do 'make uninstall' in the same directory as installation and as
161 the same user as PostgreSQL.</p>
163 $ cd pg_hint_plan-1.0.0
168 <h2 id="examples">Examples</h2>
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.
175 postgres*# SeqScan(t1)
176 postgres*# IndexScan(t2 t2_pkey)
178 postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key);
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.
194 postgres*# NestLoop(t1 t2)
195 postgres*# MergeJoin(t1 t2 t3)
196 postgres*# Leading(t1 t2 t3)
198 postgres-# SELECT * FROM table1 t1
199 postgres-# JOIN table table2 t2 ON (t1.key = t2.key)
200 postgres-# JOIN table table3 t3 ON (t2.key = t3.key);
205 <h3>Temporary GUC setting</h3>
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
212 postgres*# Set(random_page_cost 2.0)
214 postgres-# SELECT * FROM table1 t1 WHERE key = 'value';
219 <h2 id="restrictions">Restrictions</h2>
220 <h3>This section describes the miscellaneous restrictions on scripting hints.</h3>
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>
229 postgres*# <span class="strong">HashJoin(a b)</span>
230 postgres*# <span class="strong">SeqScan(a)</span>
232 postgres-# /*+ IndexScan(a) */
233 postgres-# EXPLAIN SELECT /*+ MergeJoin(a b) */ *
234 postgres-# FROM pgbench_branches b
235 postgres-# JOIN pgbench_accounts a ON b.bid = a.bid
236 postgres-# ORDER BY a.aid;
238 ---------------------------------------------------------------------------------------
239 Sort (cost=31465.84..31715.84 rows=100000 width=197)
241 -> <span class="strong">Hash Join</span> (cost=1.02..4016.02 rows=100000 width=197)
242 Hash Cond: (a.bid = b.bid)
243 -> <span class="strong">Seq Scan on pgbench_accounts a</span> (cost=0.00..2640.00 rows=100000 width=97)
244 -> Hash (cost=1.01..1.01 rows=1 width=100)
245 -> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=100)
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
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.
266 postgres=# /*+ <span class="strong">HashJoin(t1 t1)</span>*/
267 postgres-# EXPLAIN SELECT * FROM s1.t1
268 postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id);
269 INFO: hint syntax error at or near "HashJoin(t1 t1)"
270 <span class="strong">DETAIL: Relation name "t1" is ambiguous.</span>
272 ------------------------------------------------------------------
273 Merge Join (cost=337.49..781.49 rows=28800 width=8)
274 Merge Cond: (s1.t1.id = public.t1.id)
275 -> Sort (cost=168.75..174.75 rows=2400 width=4)
277 -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
278 -> Sort (cost=168.75..174.75 rows=2400 width=4)
279 Sort Key: public.t1.id
280 -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4)
283 postgres=# /*+ <span class="strong">HashJoin(pt st)</span> */
284 postgres-# EXPLAIN SELECT * FROM s1.t1 st
285 postgres-# JOIN public.t1 pt ON (st.id=pt.id);
287 ---------------------------------------------------------------------
288 <span class="strong">Hash Join</span> (cost=64.00..1112.00 rows=28800 width=8)
289 Hash Cond: (st.id = pt.id)
290 -> Seq Scan on t1 st (cost=0.00..34.00 rows=2400 width=4)
291 -> Hash (cost=34.00..34.00 rows=2400 width=4)
292 -> Seq Scan on t1 pt (cost=0.00..34.00 rows=2400 width=4)
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>
305 <h3>Specifying Target objects of Hint</h3>
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.
324 postgres=# CREATE VIEW view1 AS SELECT * FROM table1 <span class="strong">t1</span>;
326 postgres=# /*+ SeqScan(<span class="strong">t1</span>) */
327 postgres=# EXPLAIN SELECT * FROM table1 <span class="strong">t1</span> JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
329 -----------------------------------------------------------------
330 Nested Loop (cost=0.00..358.01 rows=1 width=16)
331 -> Seq Scan on table1 <span class="strong">t1</span> (cost=0.00..179.00 rows=1 width=8)
333 -> Seq Scan on table1 <span class="strong">t1</span> (cost=0.00..179.00 rows=1 width=8)
337 postgres=# /*+ SeqScan(<span class="strong">t3</span>) */
338 postgres=# EXPLAIN SELECT * FROM table1 <span class="strong">t3</span> JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1;
340 --------------------------------------------------------------------------------
341 Nested Loop (cost=0.00..187.29 rows=1 width=16)
342 -> Seq Scan on table1 <span class="strong">t3</span> (cost=0.00..179.00 rows=1 width=8)
344 -> Index Scan using foo_pkey on table1 t1 (cost=0.00..8.28 rows=1 width=8)
345 Index Cond: (key = 1)
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>
355 <dt>Hint scope for RULE</dt>
356 <dd>The statements triggered by RULEs share the same hints with the
357 triggering query.</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>
364 <dt>Applicable scope of hint in multi statement </dt>
365 <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>
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>
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>
380 <h3>Handling parse errors of hints</h3>
381 <dt>Syntax error </dt>
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,
388 <li>Hint name is incorrect. </li>
389 <li>Targetted object name is not in brackets, correctly .</li>
390 <li>Object name is not separated by blank。</li>
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.
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>
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>
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>
419 <h3>Functional limitations</h3>
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
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>
436 <dt>Limitations in PL/pgSQL</dt>
437 <dd>Hints cannot be used in PL/pgSQL because the comments are not passed to
438 planner on execution.
440 <dt>Limitations in ECPG</dt>
441 <dd>Hints cannot be used also in ECPG because the comments are not
442 passed to the server.
444 <dt>Specify fetch psql count</dt>
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.
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>
456 postgres=# /*+ <span class="strong">MergeJoin(a *VALUES*)</span> */
457 postgres-# EXPLAIN SELECT *
458 postgres-# FROM pgbench_accounts a
459 postgres-# JOIN (VALUES (1,1),(2,2)) <span class="strong">v</span> (vid, vbalance) ON a.aid = v.vid
460 postgres-# ORDER BY a.aid;
462 ---------------------------------------------------------------------------------------------------------------
463 <span class="strong">Merge Join</span> (cost=0.04..4497.33 rows=2 width=105)
464 Merge Cond: (a.aid = "*VALUES*".column1)
465 -> Index Scan using pgbench_accounts_pkey on pgbench_accounts a (cost=0.00..4247.26 rows=100000 width=97)
466 -> Sort (cost=0.04..0.04 rows=2 width=8)
467 Sort Key: "*VALUES*".column1
468 -> Values Scan on <span class="strong">"*VALUES*"</span> (cost=0.00..0.03 rows=2 width=8)
472 <dt>Limitations of Set Hint </dt>
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.
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>
490 <h2 id="requirement">requirement</h2>
493 <dd>Version 9.1.4、9.2.1</dd>
498 <h2 id="seealso">See also</h2>
499 <h3 id="postgresql_document">PostgreSQL documents</h3>
500 <a href="http://www.postgresql.org/docs/current/static/sql-explain.html">EXPLAIN</a>
501 <a href="http://www.postgresql.org/docs/current/static/sql-set.html">SET</a>
502 <a href="http://www.postgresql.org/docs/current/static/runtime-config.html">Server Config</a>
505 <div class="navigation">
506 <a href="pg_hint_plan.html">pg_hint_plan</a>
509 <p class="footer">Copyright (c) 2013, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p>
512 <script type="text/javascript">
513 var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
514 document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
515 </script><script src="pg_statsinfo-ja_files/ga.js" type="text/javascript"></script>
516 <script type="text/javascript">
518 var pageTracker = _gat._getTracker("UA-10244036-6");
519 pageTracker._trackPageview();