From 04011fe2b352d3c561663645b71a368dc65b9f85 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi
Date: Tue, 10 Oct 2017 12:17:58 +0900
Subject: [PATCH] Added a notice in the manual.
Added an item that explains about letter case handling of object names
in hints.
---
doc/pg_hint_plan-ja.html | 17 +++++++++-
doc/pg_hint_plan.html | 84 ++++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 100 insertions(+), 1 deletion(-)
diff --git a/doc/pg_hint_plan-ja.html b/doc/pg_hint_plan-ja.html
index 0f1a844..187be96 100755
--- a/doc/pg_hint_plan-ja.html
+++ b/doc/pg_hint_plan-ja.html
@@ -493,7 +493,9 @@ postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
-ãªãã¸ã§ã¯ãåã®å¼ç¨ç¬¦ä»ã
+ãã³ãå¥å
ã®ãªãã¸ã§ã¯ãåã®æåã±ã¼ã¹
+PostgreSQL ã¯å¼ç¨ç¬¦ã§å²ãããªããªãã¸ã§ã¯ãåãæåã±ã¼ã¹ãç¡è¦ãã¦æ±ãã¾ãããpg_hint_plan ã¯æå®ããããªãã¸ã§ã¯ãåã®æåã±ã¼ã¹ã¯ãã®ã¾ã¾ PostgreSQL ã®å
é¨è¡¨ç¾ã¨æ¯è¼ãã¾ããã¤ã¾ãããã³ãå¥ã§ TBL ã¨æå®ããå ´åããã¼ã¿ãã¼ã¹ä¸ã§ "TBL" ã¨å®ç¾©ãããã®ã®ã¿ã¨åè´ã, TBL, tbl, Tbl ãªã©å¼ç¨ç¬¦ã§å²ãããªããªãã¸ã§ã¯ãåã¨ã¯åè´ãã¾ããã
+ãã³ãå¥å
ã®ãªãã¸ã§ã¯ãåã®å¼ç¨ç¬¦ä»ã
ãã³ãã«è¨è¿°ãããªãã¸ã§ã¯ãåãå¥åãæ¬å¼§ï¼(ã)ã®ããããï¼ãäºéå¼ç¨ç¬¦ï¼"ï¼ã空ç½ï¼ã¹ãã¼ã¹ãã¿ããæ¹è¡ã®ããããï¼ãå«ãå ´åã¯ãé常ã®SQLæã§ä½¿ãå ´åã¨åãããã«äºéå¼ç¨ç¬¦(")ã§å²ãã§ãã ãããäºéå¼ç¨ç¬¦ãå«ããªãã¸ã§ã¯ãåã¯ãå
¨ä½ãäºéå¼ç¨ç¬¦ã§æ¬ã£ãããã§ãå
é¨ã«å«ãäºéå¼ç¨ç¬¦ãäºéå¼ç¨ç¬¦ã§ã¨ã¹ã±ã¼ããã¦ãã ãã(ä¾: ãquoted"table"nameãâã"quoted""table""name"ã)ã
åä¸å称ãã¼ãã«ã®åºå¥
ã¹ãã¼ãéããåä¸ãã¼ãã«ã®è¤æ°å使ç¨ãªã©ã§ã¯ã¨ãªä¸ã«åä¸å称ã®ãã¼ãã«ãè¤æ°ååºç¾ããå ´åã¯ããã¼ãã«ã«å¥åãã¤ãã¦ããããã®ãã¼ãã«ãåºå¥ãã¦ãã ããã以ä¸ã®ä¾ã®1ã¤ç®ã®SQLæã§ã¯ãHashJoin(t1 t1)ããã³ãã«æå®ããã¨ãããã³ãå¥å¯¾è±¡ã®ãªãã¸ã§ã¯ããç¹å®ã§ããã«ã¨ã©ã¼ã«ãªã£ã¦ãã¾ãã2ã¤ç®ã®SQLæã§ã¯ãåãã¼ãã«ã«ptãstã¨ããå¥åãã¤ãã¦ãããããå®è¡è¨ç»ä½ææã«ãã³ãã§æå®ããéãã«Hash Joinãé¸æãã¦ãã¾ãã
@@ -626,7 +628,20 @@ postgres=# WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
postgres=#
+<<<<<<< HEAD
ä¸ã¤ã®ã¯ã¨ãªã§ä¸è¨ã®ãããªå¯åãåãããè¤æ°ä½¿ç¨ãã¦ããå ´åã¯ããANY_subqueryãã¨æå®ãã¦ã対象ãç¹å®ã§ããªãããããã³ãå¥ã¯ã¨ã©ã¼ã¨ãªãç¡è¦ããã¾ãã
+=======
+
+IndexOnlyScanãã³ãå¥
+ãã³ãå¥ã®å¯¾è±¡ã¨ãªããã¼ãã«ã«Index Only Scanãå¯è½ãªã¤ã³ããã¯ã¹ã¨Index Only Scanãä¸å¯è½ãªã¤ã³ããã¯ã¹ãåæã«åå¨ããå ´åãIndex Only Scanãå¯è½ãªã¤ã³ããã¯ã¹ããã¼ãã«ã«å¯¾ãã¦IndexOnlyScanãã³ãå¥ã追å ã§æå®ããªãã¨Index Scanãé¸æããããã¨ãããã¾ãã
+
+NoIndexScanãã³ãã®æåã«ã¤ãã¦
+NoIndexScanãã³ãå¥ãæå®ããå ´åã¯ãIndex Scanã ãã§ãªãIndex Only Scanãé¸æããã¾ããã
+
+
+UNION ã«å¯¾ãã並åå®è¡ãã³ã
+UNIONã¯ç´ä¸ã®ãµãã¯ã¨ãªãå
¨ã¦ä¸¦åå®è¡å¯è½ãªå ´åã«ã ã並åå®è¡ãè¡ãã¾ããä¸æ¹ã§ãã¹ã¦ã®ãµãã¯ã¨ãªã並åå®è¡å¯è½ãªå ´åã¯ããã®ãã¡ã®ä¸ã¤ã§ä¸¦åå®è¡ãå¼·å¶ããã¨ã³ã¹ãæ¯è¼ã®çµæUNIONå
¨ä½ã並åå®è¡ããããã¨ã«ãªãã¾ãããã ã並åå®è¡ãã³ãã«ãã並åå®è¡ã®ç¦æ¢ãè¡ã£ãå ´åã¯ãã®ã¹ãã£ã³ã¯ä¸¦åå®è¡ä¸å¯ã¨ãªãã¾ãã
+>>>>>>> c6204a7... Added a notice in the manual.
IndexOnlyScanãã³ãå¥ã®æå®(PostgreSQL 9.2以é)
ãã³ãå¥ã®å¯¾è±¡ã¨ãªããã¼ãã«ã«Index Only Scanãå¯è½ãªã¤ã³ããã¯ã¹ã¨Index Only Scanãä¸å¯è½ãªã¤ã³ããã¯ã¹ãåå¨ããå ´åãIndex Only Scanãå¯è½ãªã¤ã³ããã¯ã¹ããã¼ãã«ã¨ä½µãã¦IndexOnlyScanãã³ãå¥ã«æå®ããªãã¨Index Scanãé¸æããããã¨ãããã¾ãã
diff --git a/doc/pg_hint_plan.html b/doc/pg_hint_plan.html
index c0ccbda..b99526b 100755
--- a/doc/pg_hint_plan.html
+++ b/doc/pg_hint_plan.html
@@ -237,6 +237,44 @@ postgres-# ORDER BY a.aid;
(7 rows)
postgres=#
+
+
+Using with PL/pgSQL
+pg_hint_plan works for queries in PL/pgSQL scripts with some restrictions.
+
+ - Hints affect only on the following kind of queires.
+
+ - Queries that returns one row. (SELECT, INSERT, UPDATE and DELETE)
+ - Queries that returns multiple rows. (RETURN QUERY)
+ - Dynamic SQL statements. (EXECUTE)
+ - Cursor open. (OPEN)
+ - Loop over result of a query (FOR)
+
+
+ - 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.
+
+
+postgres=# CREATE FUNCTION hints_func(integer) RETURNS integer AS $$
+postgres$# DECLARE
+postgres$# id integer;
+postgres$# cnt integer;
+postgres$# BEGIN
+postgres$# SELECT /*+ NoIndexScan(a) */ aid
+postgres$# INTO id FROM pgbench_accounts a WHERE aid = $1;
+postgres$# SELECT /*+ SeqScan(a) */ count(*)
+postgres$# INTO cnt FROM pgbench_accounts a;
+postgres$# RETURN id + cnt;
+postgres$# END;
+postgres$# $$ LANGUAGE plpgsql;
+
+
+
+Letter case in a hinted object
+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.
+
+
Escaping special chacaters in object names
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.
@@ -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=#
Restrictions
@@ -295,6 +334,42 @@ postgres=#
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.
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.
This mechanism also applies on rewritten queries by rules.
+=======
+Underlying tables of views or rules
+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.
+
+postgres=# CREATE VIEW v1 AS SELECT * FROM t2;
+postgres=# EXPLAIN /*+ HashJoin(t1 v1) */
+ 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)
+
+
+
+Inheritance tables
+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.
+
+
+Hinting on multistatements
+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.
+
+VALUES expressions
+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.
postgres=# CREATE VIEW view1 AS SELECT * FROM table1 t1;
CREATE TABLE
@@ -355,9 +430,18 @@ postgres=# WHERE aid IN (SELECT bid FROM pgbench_accounts a2 LIMIT 10);
Using IndexOnlyScan hint (PostgreSQL 9.2 and later)
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.
+<<<<<<< HEAD
Precaution points for NoIndexScan hint (PostgreSQL 9.2 and later)
NoIndexScan hint involes NoIndexOnlyScan.
+=======
+Parallel hint and UNION
+
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.
+>>>>>>> c6204a7... Added a notice in the manual.
Errors of hints
pg_hint_plan stops parsing on any error and uses hints already parsed on the most cases. Followings are the typical errors.
--
2.11.0