firtst release
| Revisión | 8d3aee59429e6b9ea54f7d5557b970ef22834dfc (tree) |
|---|---|
| Tiempo | 2013-02-14 18:50:53 |
| Autor | naoki_kishi_b1 <kishi_naoki_b1@lab....> |
| Commiter | naoki_kishi_b1 |
英語のドキュメント0版
doc/pg_hint_plan.html (diff)| @@ -0,0 +1,425 @@ | ||
| 1 | +<!DOCTYPE HTML PUBLIC "-//W3C//DTD html 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> | |
| 2 | +<html> | |
| 3 | +<head> | |
| 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"> | |
| 7 | +--> | |
| 8 | +<link rel="stylesheet" type="text/css" href="style.css"> | |
| 9 | +<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> | |
| 10 | +</head> | |
| 11 | + | |
| 12 | +<body> | |
| 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> | |
| 16 | +</div> | |
| 17 | +<hr> | |
| 18 | + | |
| 19 | +<div class="index"> | |
| 20 | +<ol> | |
| 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> | |
| 31 | +</div> | |
| 32 | + | |
| 33 | +<h2 id="name">name</h2> | |
| 34 | +<p>pg_hint_plan -- controls execution plan with Hint information in a particular comment</p> | |
| 35 | + | |
| 36 | +<h2 id="synopsis">Synopsis</h2> | |
| 37 | +<p>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.</p> | |
| 38 | +<p>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.</p> | |
| 39 | + | |
| 40 | +<h2 id="description">Description</h2> | |
| 41 | + | |
| 42 | +<h3 id="hint-rule">How-to</h3> | |
| 43 | +<p>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. </p> | |
| 44 | + | |
| 45 | +<p>In the example below , Hash Join execution plan is selected for the SeqScan for pgbench_accounts table from HashJoin and SeqScan hint</p> | |
| 46 | +<pre> | |
| 47 | +postgres=# /*+ | |
| 48 | +postgres*# <span class="strong">HashJoin(a b)</span> | |
| 49 | +postgres*# <span class="strong">SeqScan(a)</span> | |
| 50 | +postgres*# */ | |
| 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; | |
| 55 | + QUERY PLAN | |
| 56 | +--------------------------------------------------------------------------------------- | |
| 57 | + Sort (cost=31465.84..31715.84 rows=100000 width=197) | |
| 58 | + Sort Key: a.aid | |
| 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) | |
| 64 | +(7 rows) | |
| 65 | + | |
| 66 | +postgres=# </pre> | |
| 67 | + | |
| 68 | +<h3 id="hint-group">hint group</h3> | |
| 69 | +<p>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 <a href="hint_list.html">Hint list</a></p> | |
| 70 | + | |
| 71 | +<h4>Scan method </h4> | |
| 72 | +<p>With these hints , you can force or restraint the optimizer to select specific scan method for tables or columns.It includes 'SeqScan' , 'IndexScan' etc.</p> | |
| 73 | +<p>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.</p> | |
| 74 | + | |
| 75 | + | |
| 76 | +<h4>Join method</h4> | |
| 77 | +<p>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.</p> | |
| 78 | +<p>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.</p> | |
| 79 | + | |
| 80 | +<h4>Join Order</h4> | |
| 81 | +<p>‘Leading’hint forces join order in which table is specified.</p> | |
| 82 | + | |
| 83 | +<h4>GUC</h4> | |
| 84 | +<p>'Set' hint changes GUC parameters just while creating execution plan for this query.</p> | |
| 85 | +<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> | |
| 86 | +<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> | |
| 87 | + | |
| 88 | +<h3 id="hint-GUC">GUC of pg_hint_plan</h3> | |
| 89 | +<p>The GUC parameters for pg_hint_planpg_hint_plan is below.</p> | |
| 90 | +<table> | |
| 91 | +<thead> | |
| 92 | +<tr> | |
| 93 | +<tr><th>GUC</th><th>discription</th><th>Default</th></tr> | |
| 94 | +</tr></thead> | |
| 95 | +<tbody> | |
| 96 | +<tr><td>pg_hint_plan.enable_hint</td> | |
| 97 | + <td>Enbles or disables the function of pg_hint_plan.</td><td>on</td></tr> | |
| 98 | +<tr><td>pg_hint_plan.debug_print</td> | |
| 99 | + <td>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. </td><td>off</td></tr> | |
| 100 | +<tr><td>pg_hint_plan.parse_messages</td> | |
| 101 | + <td>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. </td></tr> | |
| 102 | +</tbody> | |
| 103 | +</table> | |
| 104 | +<p> | |
| 105 | +For PostgreSQL 9.1 , it is required to set these parameters in postgresql.conf file to add 'pg_hint_plan' for <a href="http://www.postgresql.org/docs/current/static/runtime-config-custom.html#GUC-CUSTOM-VARIABLE-CLASSES">custom_variable_classes</a>. Typical usage example of this is below. | |
| 106 | +</p> | |
| 107 | +<pre> | |
| 108 | +# postgresql.conf | |
| 109 | +shared_preload_libraries = 'pg_hint_plan' | |
| 110 | + | |
| 111 | +custom_variable_classes = 'pg_hint_plan' # not required for 9.2 or after | |
| 112 | +pg_hint_plan.parse_messages = 'debug2' | |
| 113 | +</pre> | |
| 114 | +<p> | |
| 115 | +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 | |
| 116 | +</p> | |
| 117 | + | |
| 118 | +<h2 id="install">Install</h2> | |
| 119 | +<p>Installation of pg_hint_plan. </p> | |
| 120 | + | |
| 121 | +<h3 id="build">build</h3> | |
| 122 | +<p>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. </p> | |
| 123 | +<p>Example of build is given below</p> | |
| 124 | +<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 | |
| 130 | +</pre> | |
| 131 | + | |
| 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, be careful as it needs to be installed in $libdir/plugins aswell. | |
| 134 | +<pre> | |
| 135 | +postgres=# LOAD 'pg_hint_plan'; | |
| 136 | +LOAD | |
| 137 | +postgres=# </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> | |
| 139 | + | |
| 140 | +<h2 id="uninstall">Unistall</h2> | |
| 141 | +<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 in which PostgreSQL is installed. </p> | |
| 142 | +<p>Example of un-install below</p> | |
| 143 | +<pre> | |
| 144 | +$ cd pg_hint_plan-1.0.0 | |
| 145 | +$ su | |
| 146 | +# make uninstall | |
| 147 | +</pre> | |
| 148 | + | |
| 149 | +<h2 id="examples">How to use </h2> | |
| 150 | +<h3>Scan method</h3> | |
| 151 | +<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. | |
| 152 | +<pre> | |
| 153 | +postgres=# /*+ | |
| 154 | +postgres*# SeqScan(t1) | |
| 155 | +postgres*# IndexScan(t2 t2_pkey) | |
| 156 | +postgres*# */ | |
| 157 | +postgres-# SELECT * FROM table1 t1 JOIN table table2 t2 ON (t1.key = t2.key); | |
| 158 | +... | |
| 159 | +</pre> | |
| 160 | +</p> | |
| 161 | + | |
| 162 | +<h3>Join method or order</h3> | |
| 163 | +<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. | |
| 164 | +<pre> | |
| 165 | +postgres=# /*+ | |
| 166 | +postgres*# NestLoop(t1 t2) | |
| 167 | +postgres*# MergeJoin(t1 t2 t3) | |
| 168 | +postgres*# Leading(t1 t2 t3) | |
| 169 | +postgres*# */ | |
| 170 | +postgres-# SELECT * FROM table1 t1 | |
| 171 | +postgres-# JOIN table table2 t2 ON (t1.key = t2.key) | |
| 172 | +postgres-# JOIN table table3 t3 ON (t2.key = t3.key); | |
| 173 | +... | |
| 174 | +</pre> | |
| 175 | +</p> | |
| 176 | + | |
| 177 | +<h3>GUC</h3> | |
| 178 | +<p> | |
| 179 | +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. | |
| 180 | +<pre> | |
| 181 | +postgres=# /*+ | |
| 182 | +postgres*# Set(random_page_cost 2.0) | |
| 183 | +postgres*# */ | |
| 184 | +postgres-# SELECT * FROM table1 t1 WHERE key = 'value'; | |
| 185 | +... | |
| 186 | +</pre> | |
| 187 | +</p> | |
| 188 | + | |
| 189 | +<h2 id="restrictions">Restrictions</h2> | |
| 190 | +<h3>Rule of writing hints</h3> | |
| 191 | +<dl> | |
| 192 | +<dt>Position of Hints</dt> | |
| 193 | +<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> | |
| 194 | +<pre> | |
| 195 | +postgres=# /*+ | |
| 196 | +postgres*# <span class="strong">HashJoin(a b)</span> | |
| 197 | +postgres*# <span class="strong">SeqScan(a)</span> | |
| 198 | +postgres*# */ | |
| 199 | +postgres-# /*+ IndexScan(a) */ | |
| 200 | +postgres-# EXPLAIN SELECT /*+ MergeJoin(a b) */ * | |
| 201 | +postgres-# FROM pgbench_branches b | |
| 202 | +postgres-# JOIN pgbench_accounts a ON b.bid = a.bid | |
| 203 | +postgres-# ORDER BY a.aid; | |
| 204 | + QUERY PLAN | |
| 205 | +--------------------------------------------------------------------------------------- | |
| 206 | + Sort (cost=31465.84..31715.84 rows=100000 width=197) | |
| 207 | + Sort Key: a.aid | |
| 208 | + -> <span class="strong">Hash Join</span> (cost=1.02..4016.02 rows=100000 width=197) | |
| 209 | + Hash Cond: (a.bid = b.bid) | |
| 210 | + -> <span class="strong">Seq Scan on pgbench_accounts a</span> (cost=0.00..2640.00 rows=100000 width=97) | |
| 211 | + -> Hash (cost=1.01..1.01 rows=1 width=100) | |
| 212 | + -> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=100) | |
| 213 | +(7 rows) | |
| 214 | + | |
| 215 | +postgres=# </pre> | |
| 216 | +</dd> | |
| 217 | +<dt>Object name in inverted commas</dt> | |
| 218 | +<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. | |
| 219 | +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"’). | |
| 220 | +</dd> | |
| 221 | +<dt>Classification of tables with same name </dt> | |
| 222 | +<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. | |
| 223 | +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. | |
| 224 | +</p> | |
| 225 | +<pre> | |
| 226 | +postgres=# /*+ <span class="strong">HashJoin(t1 t1)</span>*/ | |
| 227 | +postgres-# EXPLAIN SELECT * FROM s1.t1 | |
| 228 | +postgres-# JOIN public.t1 ON (s1.t1.id=public.t1.id); | |
| 229 | +INFO: hint syntax error at or near "HashJoin(t1 t1)" | |
| 230 | +<span class="strong">DETAIL: Relation name "t1" is ambiguous.</span> | |
| 231 | + QUERY PLAN | |
| 232 | +------------------------------------------------------------------ | |
| 233 | + Merge Join (cost=337.49..781.49 rows=28800 width=8) | |
| 234 | + Merge Cond: (s1.t1.id = public.t1.id) | |
| 235 | + -> Sort (cost=168.75..174.75 rows=2400 width=4) | |
| 236 | + Sort Key: s1.t1.id | |
| 237 | + -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) | |
| 238 | + -> Sort (cost=168.75..174.75 rows=2400 width=4) | |
| 239 | + Sort Key: public.t1.id | |
| 240 | + -> Seq Scan on t1 (cost=0.00..34.00 rows=2400 width=4) | |
| 241 | +(8 行) | |
| 242 | + | |
| 243 | +postgres=# /*+ <span class="strong">HashJoin(pt st)</span> */ | |
| 244 | +postgres-# EXPLAIN SELECT * FROM s1.t1 st | |
| 245 | +postgres-# JOIN public.t1 pt ON (st.id=pt.id); | |
| 246 | + QUERY PLAN | |
| 247 | +--------------------------------------------------------------------- | |
| 248 | + <span class="strong">Hash Join</span> (cost=64.00..1112.00 rows=28800 width=8) | |
| 249 | + Hash Cond: (st.id = pt.id) | |
| 250 | + -> Seq Scan on t1 st (cost=0.00..34.00 rows=2400 width=4) | |
| 251 | + -> Hash (cost=34.00..34.00 rows=2400 width=4) | |
| 252 | + -> Seq Scan on t1 pt (cost=0.00..34.00 rows=2400 width=4) | |
| 253 | +(5 行) | |
| 254 | + | |
| 255 | +postgres=#</pre> | |
| 256 | +</p> | |
| 257 | +</dd> | |
| 258 | +<dt>Limitations when VALUES command is specified in FROM clause</dt> | |
| 259 | +<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> | |
| 260 | +</dl> | |
| 261 | +<h3>Target application of Hint</h3> | |
| 262 | +<dl> | |
| 263 | +<dt>Hint application for the table which is not clarified in query </dt> | |
| 264 | +<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> | |
| 265 | +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. | |
| 266 | +<pre> | |
| 267 | +postgres=# CREATE VIEW view1 AS SELECT * FROM table1 <span class="strong">t1</span>; | |
| 268 | +CREATE TABLE | |
| 269 | +postgres=# /*+ SeqScan(<span class="strong">t1</span>) */ | |
| 270 | +postgres=# EXPLAIN SELECT * FROM table1 <span class="strong">t1</span> JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1; | |
| 271 | + QUERY PLAN | |
| 272 | +----------------------------------------------------------------- | |
| 273 | + Nested Loop (cost=0.00..358.01 rows=1 width=16) | |
| 274 | + -> Seq Scan on table1 <span class="strong">t1</span> (cost=0.00..179.00 rows=1 width=8) | |
| 275 | + Filter: (key = 1) | |
| 276 | + -> Seq Scan on table1 <span class="strong">t1</span> (cost=0.00..179.00 rows=1 width=8) | |
| 277 | + Filter: (key = 1) | |
| 278 | +(5 rows) | |
| 279 | + | |
| 280 | +postgres=# /*+ SeqScan(<span class="strong">t3</span>) */ | |
| 281 | +postgres=# EXPLAIN SELECT * FROM table1 <span class="strong">t3</span> JOIN view1 t2 ON (t1.key = t2.key) WHERE t2.key = 1; | |
| 282 | + QUERY PLAN | |
| 283 | +-------------------------------------------------------------------------------- | |
| 284 | + Nested Loop (cost=0.00..187.29 rows=1 width=16) | |
| 285 | + -> Seq Scan on table1 <span class="strong">t3</span> (cost=0.00..179.00 rows=1 width=8) | |
| 286 | + Filter: (key = 1) | |
| 287 | + -> Index Scan using foo_pkey on table1 t1 (cost=0.00..8.28 rows=1 width=8) | |
| 288 | + Index Cond: (key = 1) | |
| 289 | +(5 rows) | |
| 290 | + | |
| 291 | +</pre> | |
| 292 | +</dd> | |
| 293 | +<dt>Hint for inherit table </dt> | |
| 294 | +<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> | |
| 295 | + | |
| 296 | +<dt>Hint for query using view or rule </dt> | |
| 297 | +<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 | |
| 298 | +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> | |
| 299 | + | |
| 300 | +<dt>Applicable scope of hint in the query using rule</dt> | |
| 301 | +<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> | |
| 302 | + | |
| 303 | +<dt>Applicable scope of hint in multi statement </dt> | |
| 304 | +<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> | |
| 305 | + | |
| 306 | +<dt>Specify IndexOnlyScan hint (PostgreSQL 9.2 onwards)</dt> | |
| 307 | +<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> | |
| 308 | + | |
| 309 | +<dt>Precaution points for NoIndexScan hint (PostgreSQL 9.2 onwards)</dt> | |
| 310 | +<dd>When NoIndexScan hint is specified from PostgreSQL 9.2, Index Scan and Index Only Scan are not selected.</dd> | |
| 311 | + | |
| 312 | +</dl> | |
| 313 | + | |
| 314 | +<h3>Handling Hint specification error</h3> | |
| 315 | +<dt>Syntax error </dt> | |
| 316 | +<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. | |
| 317 | +<ul> | |
| 318 | +<li>Hint name is incorrect. </li> | |
| 319 | +<li>Object specification cannot be included into brackets, correctly.</li> | |
| 320 | +<li>Object name is not separated in blank。</li> | |
| 321 | +</ul> | |
| 322 | +</dd> | |
| 323 | +<dt>Object specification error </dt> | |
| 324 | +<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 | |
| 325 | +<ul> | |
| 326 | +<li>There is table name with same title or table with alias name in query, specified hint for that. </li> | |
| 327 | +<li>Same object name is specified multiple times to combination method or combination order hint </li> | |
| 328 | +</ul> | |
| 329 | +</dd> | |
| 330 | +<dt>Redundant hint types to be specified </dt> | |
| 331 | +<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> | |
| 332 | + | |
| 333 | + | |
| 334 | +<dt>Nested block comment</dt> | |
| 335 | +<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> | |
| 336 | + | |
| 337 | +<dt>Message Output level </dt> | |
| 338 | +<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> | |
| 339 | + | |
| 340 | + | |
| 341 | +<h3>Functional limitations</h3> | |
| 342 | +<dt>Impact of standard GUC parameter</dt> | |
| 343 | +<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> | |
| 344 | +<dt>Case which cannot be controlled in hint </dt> | |
| 345 | +<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. | |
| 346 | +<ul> | |
| 347 | +<li>Nest Loop is not treated as candidate path in FULL OUTER JOIN</li> | |
| 348 | +<li>Index that contains only row which is not specified to WHERE clause or JOIN clause is not treated as candidate path</li> | |
| 349 | +<li>When ctid is not specified to search conditions, Tid Scan is not treated as candidate path.</li> | |
| 350 | +</ul> | |
| 351 | +</dd> | |
| 352 | +<dt>Limitations in PL/pgSQL</dt> | |
| 353 | +<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> | |
| 354 | +<dt>Limitations in ECPG</dt> | |
| 355 | +<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. | |
| 356 | +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. | |
| 357 | +</dd> | |
| 358 | +<dt>Specify fetch psql count</dt> | |
| 359 | +<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 | |
| 360 | +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. | |
| 361 | +</dd> | |
| 362 | +<dt>Change of finger print by Hin</dt> | |
| 363 | +<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> | |
| 364 | +<pre> | |
| 365 | +postgres=# /*+ <span class="strong">MergeJoin(a *VALUES*)</span> */ | |
| 366 | +postgres-# EXPLAIN SELECT * | |
| 367 | +postgres-# FROM pgbench_accounts a | |
| 368 | +postgres-# JOIN (VALUES (1,1),(2,2)) <span class="strong">v</span> (vid, vbalance) ON a.aid = v.vid | |
| 369 | +postgres-# ORDER BY a.aid; | |
| 370 | + QUERY PLAN | |
| 371 | +--------------------------------------------------------------------------------------------------------------- | |
| 372 | + <span class="strong">Merge Join</span> (cost=0.04..4497.33 rows=2 width=105) | |
| 373 | + Merge Cond: (a.aid = "*VALUES*".column1) | |
| 374 | + -> Index Scan using pgbench_accounts_pkey on pgbench_accounts a (cost=0.00..4247.26 rows=100000 width=97) | |
| 375 | + -> Sort (cost=0.04..0.04 rows=2 width=8) | |
| 376 | + Sort Key: "*VALUES*".column1 | |
| 377 | + -> Values Scan on <span class="strong">"*VALUES*"</span> (cost=0.00..0.03 rows=2 width=8) | |
| 378 | +(6 rows) | |
| 379 | + | |
| 380 | +postgres=#</pre> | |
| 381 | +<dt>Limitations of Set Hint </dt> | |
| 382 | +<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. | |
| 383 | +<ul> | |
| 384 | +<li>When pg_hint_plan.enable_hint and pg_hint_plan.debug_print are specified, they will be ignored. </li> | |
| 385 | +<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> | |
| 386 | +</ul> | |
| 387 | +</dd> | |
| 388 | + | |
| 389 | +</dl> | |
| 390 | + | |
| 391 | +<h2 id="requirement">requirement</h2> | |
| 392 | +<dl> | |
| 393 | +<dt>PostgreSQL</dt> | |
| 394 | + <dd>Version 9.1.4、9.2.1</dd> | |
| 395 | +<dt>OS</dt> | |
| 396 | + <dd>RHEL 6.1</dd> | |
| 397 | +</dl> | |
| 398 | + | |
| 399 | +<h2 id="seealso">See also</h2> | |
| 400 | +<h3 id="postgresql_document">PostgreSQL documents</h3> | |
| 401 | +<a href="http://www.postgresql.org/docs/current/static/sql-explain.html">EXPLAIN</a> | |
| 402 | +<a href="http://www.postgresql.org/docs/current/static/sql-set.html">SET</a> | |
| 403 | +<a href="http://www.postgresql.org/docs/current/static/runtime-config.html">Server Config</a> | |
| 404 | +<hr> | |
| 405 | + | |
| 406 | +<div class="navigation"> | |
| 407 | + <a href="pg_hint_plan.html">pg_hint_plan</a> | |
| 408 | +</div> | |
| 409 | + | |
| 410 | +<p class="footer">Copyright (c) 2012, NIPPON TELEGRAPH AND TELEPHONE CORPORATION</p> | |
| 411 | + | |
| 412 | +<!-- | |
| 413 | +<script type="text/javascript"> | |
| 414 | +var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www."); | |
| 415 | +document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E")); | |
| 416 | +</script><script src="pg_statsinfo-ja_files/ga.js" type="text/javascript"></script> | |
| 417 | +<script type="text/javascript"> | |
| 418 | +try{ | |
| 419 | +var pageTracker = _gat._getTracker("UA-10244036-6"); | |
| 420 | +pageTracker._trackPageview(); | |
| 421 | +} catch(err) {} | |
| 422 | +</script> | |
| 423 | +--> | |
| 424 | +</body> | |
| 425 | +</html> |
Fork