Commit: 04011fe2b352d3c561663645b71a368dc65b9f85 - amalsyah12 (git)

firtst release

Commit MetaInfo

• Revision: 04011fe2b352d3c561663645b71a368dc65b9f85 (tree)
• Time: 2017-10-10 12:42:13
• Author: Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
• Commiter: Kyotaro Horiguchi

Log Message

Added a notice in the manual.

Added an item that explains about letter case handling of object names
in hints.

Change Summary

• modified: doc/pg_hint_plan-ja.html
• modified: doc/pg_hint_plan.html

Incremental Difference

--- a/doc/pg_hint_plan-ja.html
+++ b/doc/pg_hint_plan-ja.html
@@ -493,7 +493,9 @@ postgres$# END;
 postgres$# $$ LANGUAGE plpgsql;
 </pre>
 </dd>
-<dt>オブジェクト名の引用符付け</dt>
+<dt>ヒント句内のオブジェクト名の文字ケース</dt>
+<dd>PostgreSQL は引用符で囲われないオブジェクト名を文字ケースを無視して扱いますが、pg_hint_plan は指定されたオブジェクト名の文字ケースはそのまま PostgreSQL の内部表現と比較します。つまり、ヒント句で TBL と指定した場合、データベース上で "TBL" と定義したもののみと合致し, TBL, tbl, Tbl など引用符で囲われないオブジェクト名とは合致しません。</dd>
+<dt>ヒント句内のオブジェクト名の引用符付け</dt>
 <dd>ヒントに記述するオブジェクト名や別名が括弧（(、)のいずれか）、二重引用符（"）、空白（スペース、タブ、改行のいずれか）を含む場合は、通常のSQL文で使う場合と同じように二重引用符(")で囲んでください。二重引用符を含むオブジェクト名は、全体を二重引用符で括ったうえで、内部に含む二重引用符を二重引用符でエスケープしてください(例: 「quoted"table"name」→「"quoted""table""name"」)。</dd>
 <dt>同一名称テーブルの区別</dt>
 <dd>スキーマ違いや同一テーブルの複数回使用などでクエリ中に同一名称のテーブルが複数回出現する場合は、テーブルに別名をつけてそれぞれのテーブルを区別してください。以下の例の1つ目のSQL文では、HashJoin(t1 t1)をヒントに指定したとき、ヒント句対象のオブジェクトが特定できずにエラーになっています。2つ目のSQL文では、各テーブルにptやstという別名をつけているため、実行計画作成時にヒントで指定した通りにHash Joinを選択しています。</p>
@@ -626,7 +628,20 @@ postgres=#   WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
 
 postgres=#
 </pre>
+<<<<<<< HEAD
 一つのクエリで上記のような副問い合わせを複数使用している場合は、「ANY_subquery」と指定しても対象を特定できないため、ヒント句はエラーとなり無視されます。</br>
+=======
+
+<dt><h3>IndexOnlyScanヒント句</h3></dt>
+<dd>ヒント句の対象となるテーブルにIndex Only Scanが可能なインデックスとIndex Only Scanが不可能なインデックスが同時に存在する場合、Index Only Scanが可能なインデックスをテーブルに対してIndexOnlyScanヒント句を追加で指定しないとIndex Scanが選択されることがあります。</dd>
+
+<dt><h3>NoIndexScanヒントの挙動について</h3></dt>
+<dd>NoIndexScanヒント句を指定した場合は、Index ScanだけでなくIndex Only Scanも選択されません。</dd>
+</dl>
+
+<dt><h3>UNION に対する並列実行ヒント</h3></dt>
+<dd>UNIONは直下のサブクエリが全て並列実行可能な場合にだけ並列実行を行います。一方ですべてのサブクエリが並列実行可能な場合は、そのうちの一つで並列実行を強制するとコスト比較の結果UNION全体が並列実行されることになります。ただし並列実行ヒントによる並列実行の禁止を行った場合はそのスキャンは並列実行不可となります。
+>>>>>>> c6204a7... Added a notice in the manual.
 </dd>
 <dt>IndexOnlyScanヒント句の指定(PostgreSQL 9.2以降)</dt>
 <dd>ヒント句の対象となるテーブルにIndex Only Scanが可能なインデックスとIndex Only Scanが不可能なインデックスが存在する場合、Index Only Scanが可能なインデックスをテーブルと併せてIndexOnlyScanヒント句に指定しないとIndex Scanが選択されることがあります。</dd>

--- a/doc/pg_hint_plan.html
+++ b/doc/pg_hint_plan.html
@@ -237,6 +237,44 @@ postgres-#   ORDER BY a.aid;
 (7 rows)
 
 postgres=# </pre>
+</dd>
+
+<h3>Using with PL/pgSQL</h3>
+<dd>pg_hint_plan works for queries in PL/pgSQL scripts with some restrictions.
+<ul>
+ <li>Hints affect only on the following kind of queires.
+ <ul>
+  <li>Queries that returns one row. (SELECT, INSERT, UPDATE and DELETE)</li>
+  <li>Queries that returns multiple rows. (RETURN QUERY)</li>
+  <li>Dynamic SQL statements. (EXECUTE)</li>
+  <li>Cursor open. (OPEN)</li>
+  <li>Loop over result of a query (FOR)</li>
+ </ul>
+
+ <li>A hint comment have to be placed after the first word in a query
+     as the following since preceding comments are not sent as a part
+     of the query.</li>
+</ul>
+<pre>
+postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
+postgres$# DECLARE
+postgres$#     id  integer;
+postgres$#     cnt integer;
+postgres$# BEGIN
+postgres$#     SELECT <b><u>/*+ NoIndexScan(a) */</u></b> aid
+postgres$#         INTO id FROM pgbench_accounts a WHERE aid = $1;
+postgres$#     SELECT <b><u>/*+ SeqScan(a) */</u></b> count(*)
+postgres$#         INTO cnt FROM pgbench_accounts a;
+postgres$#     RETURN id + cnt;
+postgres$# END;
+postgres$# $$ LANGUAGE plpgsql;
+</pre>
+</dd>
+
+<h3>Letter case in a hinted object</h3>
+<p>Unlike the way PostgreSQL handles object names, pg_hint_plan compares bare object names in hints against the database internal object names in case sensitive way. Therefore an object name TBL in a hint matches only "TBL" in database and does not match any unquoted names like TBL, tbl or Tbl.
+</p>
+
 <h3>Escaping special chacaters in object names</h3>
 <p>The objects as the hint parameter should be enclosed by double quotes if they includes parentheses, double quotes and white spaces. The escaping rule is the same as PostgreSQL.
 </p>
@@ -274,6 +312,7 @@ postgres-# JOIN public.t1 pt ON (st.id=pt.id);
          ->  Seq Scan on t1 pt  (cost=0.00..34.00 rows=2400 width=4)
 (5 行)
 
+<<<<<<< HEAD
 postgres=#</pre>
 </p>
 <h2 id="restrictions">Restrictions</h2>
@@ -295,6 +334,42 @@ postgres=#</pre>
 <p>Hints are effective on any objects with the target name even if they aren't aparent in the query, specifically objects in views. For that reason, you should create different views in which targetted objects have distinct aliases if you want to hint them differently from the first view.</p>
 <p>In the following examples, the first query is assigning the same name "t1" on the two occurrences of the table1 so the hint SeqScan(t1) affects both scans. On the other hand the second assignes the different name 't3' on the one of them so the hint affects only on the rest one.</p>
 <p>This mechanism also applies on rewritten queries by rules.</p>
+=======
+<h3>Underlying tables of views or rules</h3>
+<dd>Hints are not applicable on views itself, but they can affect the
+queries within if the object names match the object names in the
+expanded query on the view. Assigning aliases to the tables in a view
+enables them to be manipulated from outside the view.
+<pre>
+<b>postgres=#</b> CREATE VIEW v1 AS SELECT * FROM <b><u>t2</u></b>;
+<b>postgres=#</b> EXPLAIN <b>/*+ HashJoin(t1 v1) */</b>
+          SELECT * FROM t1 JOIN v1 ON (c1.a = v1.a);
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Hash Join  (cost=3.27..18181.67 rows=101 width=8)
+   Hash Cond: (t1.a = t2.a)
+   ->  Seq Scan on t1  (cost=0.00..14427.01 rows=1000101 width=4)
+   ->  Hash  (cost=2.01..2.01 rows=101 width=4)
+         ->  Seq Scan on t2  (cost=0.00..2.01 rows=101 width=4)
+</pre>
+</dd>
+
+<h3>Inheritance tables</h3>
+<dd>Hints can point only the parent of an inheritance tables and the
+hint affect all the inheritance. Hints simultaneously point directly
+to children are not in effect.
+</dd>
+
+<h3>Hinting on multistatements</h3>
+<dd>One multistatement can have exactly one hint comment and the hints affects all of the individual statement in the multistatement. Notice that the seemingly multistatement on the interactive interface of psql is internally a sequence of single statements so hints affects only on the statement just following.</dd>
+
+<h3>VALUES expressions</h3>
+<dd>VALUES expressions in FROM clause are named as *VALUES* internally
+so it is hintable if it is the only VALUES in a query. Two or more
+VALUES expressions in a query seems distinguishable looking its
+explain result. But in reality it is mere a cosmetic and they are not
+distinguisable.
+>>>>>>> c6204a7... Added a notice in the manual.
 <pre>
 postgres=# CREATE VIEW view1 AS SELECT * FROM table1 <span class="strong">t1</span>;
 CREATE TABLE
@@ -355,9 +430,18 @@ postgres=#   WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
 <h3>Using IndexOnlyScan hint (PostgreSQL 9.2 and later)</h3>
 <p>You shoud explicitly specify an index that can perform index only scan if you put IndexOnlyScan hint on a table that have other indexes that cannot perform index only scan. Or pg_hint_plan may select them. </p>
 
+<<<<<<< HEAD
 <h3>Precaution points for NoIndexScan hint (PostgreSQL 9.2 and later)</h3>
 <p>NoIndexScan hint involes NoIndexOnlyScan.</p>
 
+=======
+<h3>Parallel hint and UNION</h3>
+<dd>A UNION can run in parallel only when all underlying subqueries
+are parallel-safe. Conversely enforcing parallel on any of
+the subqueries let a parallel-executable UNION run in
+parallel. Meanwhile, a parallel hint with zero workers hinhibits a scan
+from executed in parallel.</dd>
+>>>>>>> c6204a7... Added a notice in the manual.
 
 <h2 id="errors">Errors of hints</h2>
 <p>pg_hint_plan stops parsing on any error and uses hints already parsed on the most cases. Followings are the typical errors.</p>