Commit: 4c4f5cd40c357bb829e82005383cdadfc0245945 - pg_hint_plan (git)

firtst release

Commit MetaInfo

• Revision: 4c4f5cd40c357bb829e82005383cdadfc0245945 (tree)
• Time: 2014-09-05 13:40:07
• Author: Kyotaro Horiguchi <horiguchi.kyotaro@lab....>
• Commiter: Kyotaro Horiguchi

Log Message

Change verision to 1.1.1. Change target version to 9.4.

Change Summary

• modified: Makefile
• delete: SPECS/{pg_hint_plan93.spec => pg_hint_plan94.spec}
• modified: expected/ut-R.out
• modified: make_join_rel.c
• delete: pg_hint_plan--1.0.sql => pg_hint_plan--1.1.1.sql
• modified: pg_hint_plan.c
• modified: pg_hint_plan.control
• modified: sql/ut-R.sql

Incremental Difference

--- a/Makefile
+++ b/Makefile
@@ -12,7 +12,7 @@ REGRESSION_EXPECTED = expected/init.out expected/base_plan.out expected/pg_hint_
 REGRESS_OPTS = --encoding=UTF8
 
 EXTENSION = pg_hint_plan
-DATA = pg_hint_plan--1.0.sql
+DATA = pg_hint_plan--1.1.1.sql
 
 EXTRA_CLEAN = sql/ut-fdw.sql expected/ut-fdw.out
 
@@ -23,4 +23,4 @@ include $(PGXS)
 installcheck: $(REGRESSION_EXPECTED)
 
 # pg_hint_plan.c includes core.c and make_join_rel.c
-pg_hint_plan.o: core.c make_join_rel.c # pg_stat_statements.c
+pg_hint_plan.o: core.c make_join_rel.c pg_stat_statements.c

--- a/SPECS/pg_hint_plan93.spec
+++ b/SPECS/pg_hint_plan94.spec
@@ -1,15 +1,15 @@
 # SPEC file for pg_hint_plan
 # Copyright(C) 2012-2014 NIPPON TELEGRAPH AND TELEPHONE CORPORATION
 
-%define _pgdir   /usr/pgsql-9.3
+%define _pgdir   /usr/pgsql-9.4
 %define _bindir  %{_pgdir}/bin
 %define _libdir  %{_pgdir}/lib
 %define _datadir %{_pgdir}/share
 
 ## Set general information for pg_hint_plan.
-Summary:    Optimizer hint for PostgreSQL 9.3
-Name:       pg_hint_plan93
-Version:    1.1.0
+Summary:    Optimizer hint for PostgreSQL 9.4
+Name:       pg_hint_plan94
+Version:    1.1.1
 Release:    1%{?dist}
 License:    BSD
 Group:      Applications/Databases
@@ -19,8 +19,8 @@ BuildRoot:  %{_tmppath}/%{name}-%{version}-%{release}-%(%{__id_u} -n)
 Vendor:     NIPPON TELEGRAPH AND TELEPHONE CORPORATION
 
 ## We use postgresql-devel package
-BuildRequires:  postgresql93-devel
-Requires:  postgresql93-libs
+BuildRequires:  postgresql94-devel
+Requires:  postgresql94-libs
 
 ## Description for "pg_hint_plan"
 %description
@@ -32,7 +32,7 @@ plan by adding special comment block with optimizer hint before the query you
 want to optimize.  You can control scan method, join method, join order, and
 planner-related GUC parameters during planning.
 
-Note that this package is available for only PostgreSQL 9.3.
+Note that this package is available for only PostgreSQL 9.4.
 
 ## pre work for build pg_hint_plan
 %prep
@@ -48,7 +48,7 @@ rm -rf %{buildroot}
 install -d %{buildroot}%{_libdir}
 install pg_hint_plan.so %{buildroot}%{_libdir}/pg_hint_plan.so
 install -d %{buildroot}%{_datadir}/extension
-install -m 644 pg_hint_plan--1.0.sql %{buildroot}%{_datadir}/extension/pg_hint_plan--1.0.sql
+install -m 644 pg_hint_plan--1.1.1.sql %{buildroot}%{_datadir}/extension/pg_hint_plan--1.1.1.sql
 install -m 644 pg_hint_plan.control %{buildroot}%{_datadir}/extension/pg_hint_plan.control
 
 %clean
@@ -58,11 +58,13 @@ rm -rf %{buildroot}
 %defattr(0755,root,root)
 %{_libdir}/pg_hint_plan.so
 %defattr(0644,root,root)
-%{_datadir}/extension/pg_hint_plan--1.0.sql
+%{_datadir}/extension/pg_hint_plan--1.1.1.sql
 %{_datadir}/extension/pg_hint_plan.control
 
 # History of pg_hint_plan.
 %changelog
+* Thu Sep 14 2014 Kyotaro Horiguchi
+- Support 9.4. Bug fix. New rev 1.1.1.
 * Mon Sep 02 2013 Takashi Suzuki
 - Initial cut for 1.1.0
 * Mon Sep 24 2012 Shigeru Hanada <[e-mail]>

--- a/expected/ut-R.out
+++ b/expected/ut-R.out
@@ -4392,13 +4392,13 @@ error hint:
 (9 rows)
 
 \o results/ut-R.tmpout
-/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #2)*/
+/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #20)*/
 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
 LOG:  pg_hint_plan:
 used hint:
 Leading(*VALUES* t1 t2)
 Rows(*VALUES* t1 #2)
-Rows(*VALUES* t1 t2 #2)
+Rows(*VALUES* t1 t2 #20)
 not used hint:
 duplication hint:
 error hint:
@@ -4407,7 +4407,7 @@ error hint:
 \! sql/maskout.sh results/ut-R.tmpout
                                  QUERY PLAN                                  
 -----------------------------------------------------------------------------
- Nested Loop  (cost=xxx rows=1 width=xxx)
+ Nested Loop  (cost=xxx rows=20 width=xxx)
    ->  Nested Loop  (cost=xxx rows=2 width=xxx)
          ->  Values Scan on "*VALUES*"  (cost=xxx rows=1 width=xxx)
          ->  Index Scan using t1_i1 on t1  (cost=xxx rows=1 width=xxx)

--- a/make_join_rel.c
+++ b/make_join_rel.c
@@ -13,6 +13,9 @@
  *-------------------------------------------------------------------------
  */
 
+/*
+ * adjust_rows: tweak estimated row numbers according to the hint.
+*/
 static double
 adjust_rows(double rows, RowsHint *hint)
 {
@@ -88,7 +91,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
 
 	/*
 	 * If it's a plain inner join, then we won't have found anything in
-	 * join_info_list.	Make up a SpecialJoinInfo so that selectivity
+	 * join_info_list.  Make up a SpecialJoinInfo so that selectivity
 	 * estimation functions will know what's being joined.
 	 */
 	if (sjinfo == NULL)
@@ -114,67 +117,81 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
 							 &restrictlist);
 
 	/* !!! START: HERE IS THE PART WHICH ADDED FOR PG_HINT_PLAN !!! */
-    {
-	RowsHint   *rows_hint = NULL;
-	int			i;
-
-	/* Apply appropriate Rows hint to the join node, if any. */
-	for (i = 0; i < current_hint->num_hints[HINT_TYPE_ROWS]; i++)
 	{
-		rows_hint = current_hint->rows_hints[i];
+		RowsHint   *rows_hint = NULL;
+		int			i;
+		RowsHint   *justforme = NULL;
+		RowsHint   *domultiply = NULL;
 
-		/*
-		 * This Rows hint is invalid for some reason, or it contains no
-		 * aliasname which exists in the query.
-		 */
-		if (!rows_hint->joinrelids ||
-			rows_hint->base.state == HINT_STATE_ERROR)
-			continue;
-
-		if (bms_equal(joinrelids, rows_hint->joinrelids))
+		/* Search for applicable rows hint for this join node */
+		for (i = 0; i < current_hint->num_hints[HINT_TYPE_ROWS]; i++)
 		{
+			rows_hint = current_hint->rows_hints[i];
+
 			/*
-			 * This join RelOptInfo is exactly a Rows hint specifies, so adjust
-			 * rows estimateion with the hint's content.  Here we never have
-			 * another hint which has same relation combination, so we can skip
-			 * rest of hints.
+			 * Skip this rows_hint if it is invalid from the first or it
+			 * doesn't target any join rels.
 			 */
-			if (rows_hint->base.state == HINT_STATE_NOTUSED)
-				joinrel->rows = adjust_rows(joinrel->rows, rows_hint);
+			if (!rows_hint->joinrelids ||
+				rows_hint->base.state == HINT_STATE_ERROR)
+				continue;
+
+			if (bms_equal(joinrelids, rows_hint->joinrelids))
+			{
+				/*
+				 * This joinrel is just the target of this rows_hint, so tweak
+				 * rows estimation according to the hint.
+				 */
+				justforme = rows_hint;
+			}
+			else if (!(bms_is_subset(rows_hint->joinrelids, rel1->relids) ||
+					   bms_is_subset(rows_hint->joinrelids, rel2->relids)) &&
+					 bms_is_subset(rows_hint->joinrelids, joinrelids) &&
+					 rows_hint->value_type == RVT_MULTI)
+			{
+				/*
+				 * If the rows_hint's target relids is not a subset of both of
+				 * component rels and is a subset of this joinrel, ths hint's
+				 * targets spread over both component rels. This menas that
+				 * this hint has been never applied so far and this joinrel is
+				 * the first (and only) chance to fire in current join tree.
+				 * Only the multiplication hint has the cumulative nature so we
+				 * apply only RVT_MULTI in this way.
+				 */
+				domultiply = rows_hint;
+			}
 		}
-		else if (bms_is_subset(rows_hint->joinrelids, rel1->relids) ||
-				 bms_is_subset(rows_hint->joinrelids, rel2->relids))
+
+		if (justforme)
 		{
 			/*
-			 * Otherwise if the relation combination specified in thee Rows
-			 * hint is subset of the set of join elements, re-estimate rows and
-			 * costs again to reflect the adjustment done in down.  This is
-			 * necessary for the first permutation of the combination the
-			 * relations, but it's difficult to determine that this is the
-			 * first, so do this everytime.
+			 * If a hint just for me is found, no other adjust method is
+			 * useles, but this cannot be more than twice becuase this joinrel
+			 * is already adjusted by this hint.
 			 */
-			set_joinrel_size_estimates(root, joinrel, rel1, rel2, sjinfo,
-									   restrictlist);
+			if (justforme->base.state == HINT_STATE_NOTUSED)
+				joinrel->rows = adjust_rows(joinrel->rows, justforme);
 		}
-		else if (bms_is_subset(rows_hint->joinrelids, joinrelids))
+		else
 		{
-			/*
-			 * If the combination specifed in the Rows hints is subset of the
-			 * join relation and spreads over both children, 
-			 *
-			 * We do adjust rows estimation only when the value type was
-			 * multiplication, because other value types are meanless.
-			 */
-			if (rows_hint->value_type == RVT_MULTI)
+			if (domultiply)
 			{
+				/*
+				 * If we have multiple routes up to this joinrel which are not
+				 * applicable this hint, this multiply hint will applied more
+				 * than twice. But there's no means to know of that,
+				 * re-estimate the row number of this joinrel always just
+				 * before applying the hint. This is a bit different from
+				 * normal planner behavior but it doesn't harm so much.
+				 */
 				set_joinrel_size_estimates(root, joinrel, rel1, rel2, sjinfo,
 										   restrictlist);
-				joinrel->rows = adjust_rows(joinrel->rows, rows_hint);
+				
+				joinrel->rows = adjust_rows(joinrel->rows, domultiply);
 			}
+			
 		}
 	}
-
-	}
 	/* !!! END: HERE IS THE PART WHICH ADDED FOR PG_HINT_PLAN !!! */
 
 	/*

--- a/pg_hint_plan--1.0.sql
+++ b/pg_hint_plan--1.1.1.sql
@@ -1,4 +1,4 @@
-/* pg_hint_plan/pg_hint_plan--1.0.sql */
+/* pg_hint_plan/pg_hint_plan--1.1.1.sql */
 
 -- complain if script is sourced in psql, rather than via CREATE EXTENSION
 \echo Use "CREATE EXTENSION pg_hint_plan" to load this file. \quit

--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -53,7 +53,7 @@
 /* partially copied from pg_stat_statements */
 #include "normalize_query.h"
 
-/* PostgreSQL 9.3 */
+/* PostgreSQL */
 #include "access/htup_details.h"
 
 #ifdef PG_MODULE_MAGIC
@@ -3292,7 +3292,7 @@ transform_join_hints(HintState *hstate, PlannerInfo *root, int nbaserel,
 		return false;
 
 	/*
-	 * Decide to use Leading hint。
+	 * Decide whether to use Leading hint
  	 */
 	for (i = 0; i < hstate->num_hints[HINT_TYPE_LEADING]; i++)
 	{

--- a/pg_hint_plan.control
+++ b/pg_hint_plan.control
@@ -1,6 +1,6 @@
 # pg_hint_plan extension
 
 comment = ''
-default_version = '1.0'
+default_version = '1.1.1'
 relocatable = false
 schema = hint_plan

--- a/sql/ut-R.sql
+++ b/sql/ut-R.sql
@@ -1163,7 +1163,7 @@ EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) W
 \! sql/maskout.sh results/ut-R.tmpout
 
 \o results/ut-R.tmpout
-/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #2)*/
+/*+ Leading(*VALUES* t1 t2) Rows(*VALUES* t1 #2)Rows(*VALUES* t1 t2 #20)*/
 EXPLAIN SELECT * FROM s1.t1, s1.t2, (VALUES(1,1,1,'1')) AS t3 (c1, c2, c3, c4) WHERE t1.c1 = t2.c1 AND t1.c1 = t3.c1;
 \o
 \! sql/maskout.sh results/ut-R.tmpout