From: Robert Treat Date: Tue, 18 Jan 2022 06:13:20 +0000 (+0900) Subject: Light-Weight spelling and grammar fixes X-Git-Tag: REL14_1_4_0~11 X-Git-Url: http://git.osdn.net/view?p=pghintplan%2Fpg_hint_plan.git;a=commitdiff_plain;h=857297af0369a677dc88515bb2e2acfc47a9e6dc Light-Weight spelling and grammar fixes Fix some fixes of spelling and grammar in the doc. --- diff --git a/doc/pg_hint_plan.html b/doc/pg_hint_plan.html index 3800a5b..e8d4b5a 100755 --- a/doc/pg_hint_plan.html +++ b/doc/pg_hint_plan.html @@ -2,7 +2,7 @@ pg_hint_plan - @@ -18,7 +18,7 @@ pre { margin: 0 2em 0 2em; padding:0.3em; border-style: solid; border-width:0.1e -

pg_hint_plan 1.4

pg_hint_plan 1.6

@@ -34,7 +34,7 @@ pre { margin: 0 2em 0 2em; padding:0.3em; border-style: solid; border-width:0.1e

Hint descriptions

Hint syntax

Restrictions

Techniques to hint on disired targets

Techniques to hint on desired targets

Errors of hints

Functional limitations

Requirements

@@ -46,15 +46,15 @@ pre { margin: 0 2em 0 2em; padding:0.3em; border-style: solid; border-width:0.1e

pg_hint_plan -- controls execution plan with hinting phrases in comment of special form.

Synopsis

PostgreSQL uses cost based optimizer, which utilizes data statistics, not static rules. The planner (optimizer) esitimates costs of each possible execution plans for a SQL statement then the execution plan with the lowest cost finally be executed. The planner does its best to select the best best execution plan, but not perfect, since it doesn't count some properties of the data, for example, correlation between columns.

PostgreSQL uses cost based optimizer, which utilizes data statistics, not static rules. The planner (optimizer) estimates costs of each possible execution plan for a SQL statement, and then the execution plan with the lowest cost is eventually executed. The planner does its best to select the best execution plan, but it is not perfect, since it doesn't know all properties of the data, for example, correlation between columns.

pg_hint_plan makes it possible to tweak execution plans using so-called "hints", which are simple descriptions in the SQL comment of special form.

Description

Basic Usage

pg_hint_plan reads hinting phrases in a comment of special form given with the target SQL statement. The special form is beginning by the character sequence "/*+" and ends with "*/". Hint phrases are consists of hint name and following parameters enclosed by parentheses and delimited by spaces. Each hinting phrases can be delimited by new lines for readability.

pg_hint_plan reads hinting phrases in a comment of special form given with the target SQL statement. The special form is beginning by the character sequence "/*+" and ends with "*/". Hint phrases consist of hint name and following parameters enclosed by parentheses and delimited by spaces. Each hinting phrases can be delimited by new lines for readability.

In the example below , hash join is selected as the joning method and scanning pgbench_accounts by sequential scan method.

In the example below , hash join is selected as the join method and scanning pgbench_accounts by sequential scan method.

 postgres=# /*+
 postgres*#    HashJoin(a b)
@@ -118,15 +118,14 @@ UPDATE 1
 DELETE 1
 postgres=#

The hint table is owned by the creator user and having the default previledges at the time of creation. -during CREATE EXTENSION. Table hints are prioritized than comment hits.

The hint table is owned by the creator user and having the default privileges at the time of creation during CREATE EXTENSION. Table hints are prioritized then comment hits.

The types of hints

Hinting phrases are classified into six types based on what kind of object and how they can affect planning. Scaning methods, join methods, joining order, row number correction, parallel query and GUC setting. You will see the lists of hint phrases of each type in Hint list.

Hints for scan methods

Scan method hints enforce specific scanning method on the target table. pg_hint_plan recognizes the target table by alias names if any. They are 'SeqScan' , 'IndexScan' and so on in this kind of hint.

Scan hints are effective on ordinary tables, inheritance tables, UNLOGGED tables, temporary tables and system catalogs. External(foreign) tables, table functions, VALUES clause, CTEs, views and subquiries are not affected.

 postgres=# /*+
 postgres*#     SeqScan(t1)
@@ -141,7 +140,7 @@ during CREATE EXTENSION. Table hints are prioritized than comment hits.
 This can affect on joins only on ordinary tables, inheritance tables, UNLOGGED tables, temporary tables, external (foreign) tables, system catalogs, table functions, VALUES command results and CTEs are allowed to be in the parameter list. But joins on views and sub query are not affected.
 
 Hint for joining order
- This hint "Leading" enforces the order of join on two or more tables. There are two ways of enforcing. One is enforcing specific order of joining but not restricting direction at each join level. Another enfoces join direction additionaly. Details are seen in the hint list table.
+ This hint "Leading" enforces the order of join on two or more tables. There are two ways of enforcing. One is enforcing specific order of joining but not restricting direction at each join level. Another enforces join direction additionally. Details are seen in the hint list table.
 
  postgres=# /*+
@@ -182,7 +181,7 @@ EXPLAIN SELECT * FROM a, b WHERE a.val = b.val;
 
 Hint for parallel plan
 This hint "Parallel" enforces parallel execution configuration on
-scans. The third parameter specifies the strength of enfocement. "soft" means that pg_hint_plan only changes max_parallel_worker_per_gather and leave all others to planner. "hard" changes other planner parameters so as to forcibly apply the number. This can affect on ordinary tables, inheritnce parents, unlogged
+scans. The third parameter specifies the strength of enforcement. "soft" means that pg_hint_plan only changes max_parallel_worker_per_gather and leave all others to planner. "hard" changes other planner parameters so as to forcibly apply the number. This can affect on ordinary tables, inheritance parents, unlogged
 tables and system catalogues. External tables, table functions, values clause, CTEs, views and subqueries are not affected. Internal tables of a view can be specified by its real name/alias as the target object. The following example shows that the query is enforced differently on each table.
 
 @@ -228,17 +227,17 @@ postgres-# SELECT * FROM table1 t1 WHERE key = 'value';
 
-
+
-  
+  
-  
+  
@@ -255,7 +254,7 @@ $ su
 # make install
 
 
-Loding pg_hint_plan
+Loading pg_hint_plan
Basically pg_hint_plan does not requires CREATE EXTENSION. Simply loading it by LOAD command will activate it and of course you can load it globally by setting shared_preload_libraries in postgresql.conf. Or you might be interested in ALTER USER SET/ALTER DATABASE SET for automatic loading for specific sessions. 
 
 postgres=# LOAD 'pg_hint_plan';
@@ -306,13 +305,13 @@ 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.
+ 
Hints affect only on the following kind of queries.
  
-  Queries that returns one row. (SELECT, INSERT, UPDATE and DELETE)
-  Queries that returns multiple rows. (RETURN QUERY)
+  Queries that return one row. (SELECT, INSERT, UPDATE and DELETE)
+  Queries that return multiple rows. (RETURN QUERY)
   Dynamic SQL statements. (EXECUTE)
   Cursor open. (OPEN)
-  Loop over result of a query (FOR)
+  Loop over results of a query (FOR)
  
 
  
A hint comment have to be placed after the first word in a query
@@ -339,14 +338,14 @@ postgres$# $$ LANGUAGE plpgsql;
 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
+Escaping special characters 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.
 
 
-Distinction between multiple occurances of a table
+Distinction between multiple occurences of a table
 pg_hint_plan identifies the target object by using aliases if
-exists. This behavior is usable to point a specific occurance
-among multiple occurances of one table.
+exists. This behavior is usable to point a specific occurence
+among multiple occurences of one table.
  postgres=# /*+ HashJoin(t1 t1) */
 postgres-# EXPLAIN SELECT * FROM s1.t1
@@ -400,7 +399,7 @@ to children are not in effect.
 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.
+distinguishable.
  postgres=# /*+ MergeJoin(*VALUES*_1 *VALUES*) */
       EXPLAIN SELECT * FROM (VALUES (1, 1), (2, 2)) v (a, b)
@@ -445,20 +444,20 @@ IN (SELECT ... {LIMIT | OFFSET ...} ...)
 
 
 Using IndexOnlyScan hint
-Index scan may unexpectedly performed on another index when the index specifed in IndexOnlyScan hint cannot perform index only scan.
+Index scan may unexpectedly performed on another index when the index specified in IndexOnlyScan hint cannot perform index only scan.
 
 Behavior of NoIndexScan
-NoIndexScan hint involes NoIndexOnlyScan.
+NoIndexScan hint involves 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.
+parallel. Meanwhile, a parallel hint with zero workers inhibits a scan
+from being executed in parallel.
Setting pg_hint_plan parameters by Set hints
-pg_hint_plan paramters change the behavior of itself so some parameters doesn't work as expected.
+
pg_hint_plan parameters change the behavior of itself so some parameters doesn't work as expected.
 
 Hints to change enable_hint, enable_hint_tables are ignored even though they are reported as "used hints" in debug logs.
 Setting debug_print and message_level works from midst of the processing of the target query.
@@ -472,13 +471,13 @@ from executed in parallel.
Syntax errors 
Any syntactical errors or wrong hint names are reported as an syntax error. These errors are reported in the server log with the message level which specified by pg_hint_plan.message_level if pg_hint_plan.debug_print is on and aboves.
Object misspecifications
-Object misspecifications results silent ingorance of the hints. This kind of error is reported as "not used hints" in the server log by the same condtion to syntax errors.
+Object misspecifications results silent ingnorance of the hints. This kind of error is reported as "not used hints" in the server log by the same condition to syntax errors.
Redundant or conflicting hints
The last hint will be active when redundant hints or hints conflicting with each other. This kind of error is reported as "duplication hints" in the server log by the same condition to syntax errors.
Nested comments
-Hint comment cannot include another block comment within. If pg_hint_plan finds it, differently from other erros, it stops parsing and abandans all hints already parsed. This kind of error is reported in the same manner as other errors. 
+Hint comment cannot include another block comment within. If pg_hint_plan finds it, differently from other errors, it stops parsing and abandons all hints already parsed. This kind of error is reported in the same manner as other errors. 
Functional limitations
Influences of some of planner GUC parameters
@@ -494,18 +493,18 @@ from executed in parallel.Queries in ECPG
-ECPG removes comments in queries written as embedded SQLs so hints cannot be passed form those queries. The only exception is that EXECUTE command passes given string unmodifed. Please consider hint tables in the case.
+ECPG removes comments in queries written as embedded SQLs so hints cannot be passed form those queries. The only exception is that EXECUTE command passes given string unmodified. Please consider hint tables in the case.
Work with pg_stat_statements
-pg_stat_statements generates a query id ignoring comments. As the result the identical queires with different hints are summerized as the same query.
+pg_stat_statements generates a query id ignoring comments. As the result the identical queires with different hints are summarized as the same query.
Requirements
-pg_hint_plan13 1.3 requires PostgreSQL 13.
+pg_hint_plan14 1.6 requires PostgreSQL 14.
 
 PostgreSQL versions tested
-  Version 13
+  Version 14
 OS versions tested
-  CentOS 8.2
+  CentOS 8.5
 
See also
@@ -520,6 +519,6 @@ pg_hint_plan13 1.3 requires PostgreSQL 13.
   pg_hint_plan
 
-Copyright (c) 2012-2021, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
+Copyright (c) 2012-2022, NIPPON TELEGRAPH AND TELEPHONE CORPORATION

 
 
Parameter name discription Default
Parameter name description Default
 
 
 pg_hint_plan.enable_hint True enbles pg_hint_plan. on
True enables pg_hint_plan. on
 pg_hint_plan.enable_hint_table
   True enbles hinting by table. true or false. off
 pg_hint_plan.parse_messages
   Specifies the log level of hint parse error. Valid values are error, warning, notice, info, log, debug. INFO
 pg_hint_plan.debug_print Controls debug print and verbosity. Valid vaiues are off, on, detailed and verbose. off
Controls debug print and verbosity. Valid values are off, on, detailed and verbose. off
 pg_hint_plan.message_level
   Specifies message level of debug print. Valid values are error, warning, notice, info, log, debug. INFO

Parameter name	discription	Default
Parameter name	description	Default
pg_hint_plan.enable_hint	True enbles pg_hint_plan.	on
True enables pg_hint_plan.	on
pg_hint_plan.enable_hint_table	True enbles hinting by table. true or false.	off
pg_hint_plan.parse_messages	Specifies the log level of hint parse error. Valid values are error, warning, notice, info, log, debug.	INFO
pg_hint_plan.debug_print	Controls debug print and verbosity. Valid vaiues are off, on, detailed and verbose.	off
Controls debug print and verbosity. Valid values are off, on, detailed and verbose.	off
pg_hint_plan.message_level	Specifies message level of debug print. Valid values are error, warning, notice, info, log, debug.	INFO