From 454f72a07c2bfa37816be9b223de17e01bdb23c8 Mon Sep 17 00:00:00 2001 From: Kyotaro Horiguchi Date: Tue, 5 Oct 2021 15:27:32 +0900 Subject: [PATCH] Support PostgreSQL 14 In PostgreSQL 14, planner was restructured in regard to query string passing. This change allows us to remove fair amount of lines and complexity that had been added as a struggle for retrieving proper query string. Two callbacks, post_parse_analyze_hook and ProcessUtility_hook are no longer used. get_query_string is removed altogether. Also current_hint_retrieved juggling, which was complex and fragile, disappears. Now pg_hint_plan_planner alone can retrieve hint string by looking into the given string. In PostgreSQL 14, a new optimization item "memoize" is added. It is not handled in this commit and leave it for a later commit. --- Makefile | 12 +- SPECS/{pg_hint_plan13.spec => pg_hint_plan14.spec} | 30 +- core.c | 304 ++++++++- expected/init.out | 5 +- expected/pg_hint_plan.out | 82 +-- expected/ut-A.out | 70 ++- expected/ut-S.out | 59 +- make_join_rel.c | 4 +- normalize_query.h | 42 +- pg_hint_plan--1.3.7.sql => pg_hint_plan--1.4.sql | 2 +- pg_hint_plan.c | 515 +++------------ pg_hint_plan.control | 2 +- pg_stat_statements.c | 691 +-------------------- sql/pg_hint_plan.sql | 4 +- 14 files changed, 551 insertions(+), 1271 deletions(-) rename SPECS/{pg_hint_plan13.spec => pg_hint_plan14.spec} (74%) rename pg_hint_plan--1.3.7.sql => pg_hint_plan--1.4.sql (93%) diff --git a/Makefile b/Makefile index 027773d..1a8ae90 100644 --- a/Makefile +++ b/Makefile @@ -5,7 +5,7 @@ # MODULES = pg_hint_plan -HINTPLANVER = 1.3.7 +HINTPLANVER = 1.4 REGRESS = init base_plan pg_hint_plan ut-init ut-A ut-S ut-J ut-L ut-G ut-R ut-fdw ut-W ut-T ut-fini @@ -22,8 +22,8 @@ PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) -STARBALL13 = pg_hint_plan13-$(HINTPLANVER).tar.gz -STARBALLS = $(STARBALL13) +STARBALL14 = pg_hint_plan14-$(HINTPLANVER).tar.gz +STARBALLS = $(STARBALL14) TARSOURCES = Makefile *.c *.h COPYRIGHT* \ pg_hint_plan--*.sql \ @@ -37,7 +37,7 @@ endif installcheck: $(REGRESSION_EXPECTED) -rpms: rpm13 +rpms: rpm14 # 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 @@ -52,5 +52,5 @@ $(STARBALLS): $(TARSOURCES) tar -chzf $@ $(addprefix $(subst .tar.gz,,$@)/, $^) rm $(subst .tar.gz,,$@) -rpm13: $(STARBALL13) - MAKE_ROOT=`pwd` rpmbuild -bb SPECS/pg_hint_plan13.spec +rpm14: $(STARBALL14) + MAKE_ROOT=`pwd` rpmbuild -bb SPECS/pg_hint_plan14.spec diff --git a/SPECS/pg_hint_plan13.spec b/SPECS/pg_hint_plan14.spec similarity index 74% rename from SPECS/pg_hint_plan13.spec rename to SPECS/pg_hint_plan14.spec index ee01413..91c54d0 100644 --- a/SPECS/pg_hint_plan13.spec +++ b/SPECS/pg_hint_plan14.spec @@ -1,7 +1,7 @@ # SPEC file for pg_store_plans # Copyright(C) 2020 NIPPON TELEGRAPH AND TELEPHONE CORPORATION -%define _pgdir /usr/pgsql-13 +%define _pgdir /usr/pgsql-14 %define _bindir %{_pgdir}/bin %define _libdir %{_pgdir}/lib %define _datadir %{_pgdir}/share @@ -15,8 +15,8 @@ ## Set general information for pg_store_plans. Summary: Optimizer hint on PostgreSQL 12 -Name: pg_hint_plan13 -Version: 1.3.7 +Name: pg_hint_plan14 +Version: 1.4 Release: 1%{?dist} License: BSD Group: Applications/Databases @@ -26,8 +26,8 @@ BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-%(%{__id_u} -n) Vendor: NIPPON TELEGRAPH AND TELEPHONE CORPORATION ## We use postgresql-devel package -BuildRequires: postgresql13-devel -Requires: postgresql13-server +BuildRequires: postgresql14-devel +Requires: postgresql14-server ## Description for "pg_hint_plan" %description @@ -35,19 +35,19 @@ Requires: postgresql13-server pg_hint_plan provides capability to tweak execution plans to be executed on PostgreSQL. -Note that this package is available for only PostgreSQL 13. +Note that this package is available for only PostgreSQL 14. %package llvmjit -Requires: postgresql13-server, postgresql13-llvmjit -Requires: pg_hint_plan13 = 1.3.7 -Summary: Just-in-time compilation support for pg_hint_plan13 +Requires: postgresql14-server, postgresql14-llvmjit +Requires: pg_hint_plan14 = 1.4 +Summary: Just-in-time compilation support for pg_hint_plan14 %description llvmjit -Just-in-time compilation support for pg_hint_plan13 +Just-in-time compilation support for pg_hint_plan14 ## pre work for build pg_hint_plan %prep -PATH=/usr/pgsql-13/bin:$PATH +PATH=/usr/pgsql-14/bin:$PATH if [ "${MAKE_ROOT}" != "" ]; then pushd ${MAKE_ROOT} make clean %{name}-%{version}.tar.gz @@ -58,7 +58,7 @@ if [ ! -d %{_rpmdir} ]; then mkdir -p %{_rpmdir}; fi ## Set variables for build environment %build -PATH=/usr/pgsql-13/bin:$PATH +PATH=/usr/pgsql-14/bin:$PATH make USE_PGXS=1 %{?_smp_mflags} ## Set variables for install @@ -73,7 +73,7 @@ rm -rf %{buildroot} %defattr(0755,root,root) %{_libdir}/pg_hint_plan.so %defattr(0644,root,root) -%{_datadir}/extension/pg_hint_plan--1.3.7.sql +%{_datadir}/extension/pg_hint_plan--1.4.sql %{_datadir}/extension/pg_hint_plan.control %files llvmjit @@ -85,5 +85,5 @@ rm -rf %{buildroot} # History of pg_hint_plan. %changelog -* Thu Oct 29 2020 Kyotaro Horiguchi -- First release of pg_hint_plan13. +* Tue Oct 05 2021 Kyotaro Horiguchi +- Support PostgreSQL 14. diff --git a/core.c b/core.c index fc7899e..e1eb785 100644 --- a/core.c +++ b/core.c @@ -20,8 +20,14 @@ * function. * * static functions: + * set_rel_pathlist() * set_plain_rel_pathlist() + * set_tablesample_rel_pathlist + * set_foreign_pathlist() * set_append_rel_pathlist() + * set_function_pathlist() + * set_values_pathlist() + * set_tablefunc_pathlist() * create_plain_partial_paths() * * src/backend/optimizer/path/joinrels.c @@ -47,9 +53,9 @@ *------------------------------------------------------------------------- */ -static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, - RelOptInfo *rel2, RelOptInfo *joinrel, - SpecialJoinInfo *sjinfo, List *restrictlist); +#include "access/tsmapi.h" +#include "catalog/pg_operator.h" +#include "foreign/fdwapi.h" /* * set_plain_rel_pathlist @@ -83,6 +89,283 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) /* + * set_tablesample_rel_pathlist + * Build access paths for a sampled relation + */ +static void +set_tablesample_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) +{ + Relids required_outer; + Path *path; + + /* + * We don't support pushing join clauses into the quals of a samplescan, + * but it could still have required parameterization due to LATERAL refs + * in its tlist or TABLESAMPLE arguments. + */ + required_outer = rel->lateral_relids; + + /* Consider sampled scan */ + path = create_samplescan_path(root, rel, required_outer); + + /* + * If the sampling method does not support repeatable scans, we must avoid + * plans that would scan the rel multiple times. Ideally, we'd simply + * avoid putting the rel on the inside of a nestloop join; but adding such + * a consideration to the planner seems like a great deal of complication + * to support an uncommon usage of second-rate sampling methods. Instead, + * if there is a risk that the query might perform an unsafe join, just + * wrap the SampleScan in a Materialize node. We can check for joins by + * counting the membership of all_baserels (note that this correctly + * counts inheritance trees as single rels). If we're inside a subquery, + * we can't easily check whether a join might occur in the outer query, so + * just assume one is possible. + * + * GetTsmRoutine is relatively expensive compared to the other tests here, + * so check repeatable_across_scans last, even though that's a bit odd. + */ + if ((root->query_level > 1 || + bms_membership(root->all_baserels) != BMS_SINGLETON) && + !(GetTsmRoutine(rte->tablesample->tsmhandler)->repeatable_across_scans)) + { + path = (Path *) create_material_path(rel, path); + } + + add_path(rel, path); + + /* For the moment, at least, there are no other paths to consider */ +} + + +/* + * set_foreign_pathlist + * Build access paths for a foreign table RTE + */ +static void +set_foreign_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) +{ + /* Call the FDW's GetForeignPaths function to generate path(s) */ + rel->fdwroutine->GetForeignPaths(root, rel, rte->relid); +} + + +/* + * set_function_pathlist + * Build the (single) access path for a function RTE + */ +static void +set_function_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) +{ + Relids required_outer; + List *pathkeys = NIL; + + /* + * We don't support pushing join clauses into the quals of a function + * scan, but it could still have required parameterization due to LATERAL + * refs in the function expression. + */ + required_outer = rel->lateral_relids; + + /* + * The result is considered unordered unless ORDINALITY was used, in which + * case it is ordered by the ordinal column (the last one). See if we + * care, by checking for uses of that Var in equivalence classes. + */ + if (rte->funcordinality) + { + AttrNumber ordattno = rel->max_attr; + Var *var = NULL; + ListCell *lc; + + /* + * Is there a Var for it in rel's targetlist? If not, the query did + * not reference the ordinality column, or at least not in any way + * that would be interesting for sorting. + */ + foreach(lc, rel->reltarget->exprs) + { + Var *node = (Var *) lfirst(lc); + + /* checking varno/varlevelsup is just paranoia */ + if (IsA(node, Var) && + node->varattno == ordattno && + node->varno == rel->relid && + node->varlevelsup == 0) + { + var = node; + break; + } + } + + /* + * Try to build pathkeys for this Var with int8 sorting. We tell + * build_expression_pathkey not to build any new equivalence class; if + * the Var isn't already mentioned in some EC, it means that nothing + * cares about the ordering. + */ + if (var) + pathkeys = build_expression_pathkey(root, + (Expr *) var, + NULL, /* below outer joins */ + Int8LessOperator, + rel->relids, + false); + } + + /* Generate appropriate path */ + add_path(rel, create_functionscan_path(root, rel, + pathkeys, required_outer)); +} + + +/* + * set_values_pathlist + * Build the (single) access path for a VALUES RTE + */ +static void +set_values_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) +{ + Relids required_outer; + + /* + * We don't support pushing join clauses into the quals of a values scan, + * but it could still have required parameterization due to LATERAL refs + * in the values expressions. + */ + required_outer = rel->lateral_relids; + + /* Generate appropriate path */ + add_path(rel, create_valuesscan_path(root, rel, required_outer)); +} + +/* + * set_tablefunc_pathlist + * Build the (single) access path for a table func RTE + */ +static void +set_tablefunc_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) +{ + Relids required_outer; + + /* + * We don't support pushing join clauses into the quals of a tablefunc + * scan, but it could still have required parameterization due to LATERAL + * refs in the function expression. + */ + required_outer = rel->lateral_relids; + + /* Generate appropriate path */ + add_path(rel, create_tablefuncscan_path(root, rel, + required_outer)); +} + + +/* + * set_rel_pathlist + * Build access paths for a base relation + */ +static void +set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, + Index rti, RangeTblEntry *rte) +{ + if (IS_DUMMY_REL(rel)) + { + /* We already proved the relation empty, so nothing more to do */ + } + else if (rte->inh) + { + /* It's an "append relation", process accordingly */ + set_append_rel_pathlist(root, rel, rti, rte); + } + else + { + switch (rel->rtekind) + { + case RTE_RELATION: + if (rte->relkind == RELKIND_FOREIGN_TABLE) + { + /* Foreign table */ + set_foreign_pathlist(root, rel, rte); + } + else if (rte->tablesample != NULL) + { + /* Sampled relation */ + set_tablesample_rel_pathlist(root, rel, rte); + } + else + { + /* Plain relation */ + set_plain_rel_pathlist(root, rel, rte); + } + break; + case RTE_SUBQUERY: + /* Subquery --- fully handled during set_rel_size */ + break; + case RTE_FUNCTION: + /* RangeFunction */ + set_function_pathlist(root, rel, rte); + break; + case RTE_TABLEFUNC: + /* Table Function */ + set_tablefunc_pathlist(root, rel, rte); + break; + case RTE_VALUES: + /* Values list */ + set_values_pathlist(root, rel, rte); + break; + case RTE_CTE: + /* CTE reference --- fully handled during set_rel_size */ + break; + case RTE_NAMEDTUPLESTORE: + /* tuplestore reference --- fully handled during set_rel_size */ + break; + case RTE_RESULT: + /* simple Result --- fully handled during set_rel_size */ + break; + default: + elog(ERROR, "unexpected rtekind: %d", (int) rel->rtekind); + break; + } + } + + /* + * Allow a plugin to editorialize on the set of Paths for this base + * relation. It could add new paths (such as CustomPaths) by calling + * add_path(), or add_partial_path() if parallel aware. It could also + * delete or modify paths added by the core code. + */ + if (set_rel_pathlist_hook) + (*set_rel_pathlist_hook) (root, rel, rti, rte); + + /* + * If this is a baserel, we should normally consider gathering any partial + * paths we may have created for it. We have to do this after calling the + * set_rel_pathlist_hook, else it cannot add partial paths to be included + * here. + * + * However, if this is an inheritance child, skip it. Otherwise, we could + * end up with a very large number of gather nodes, each trying to grab + * its own pool of workers. Instead, we'll consider gathering partial + * paths for the parent appendrel. + * + * Also, if this is the topmost scan/join rel (that is, the only baserel), + * we postpone gathering until the final scan/join targetlist is available + * (see grouping_planner). + */ + if (rel->reloptkind == RELOPT_BASEREL && + bms_membership(root->all_baserels) != BMS_SINGLETON) + generate_useful_gather_paths(root, rel, false); + + /* Now find the cheapest of the paths for this rel */ + set_cheapest(rel); + +#ifdef OPTIMIZER_DEBUG + debug_print_rel(root, rel); +#endif +} + + +/* * set_append_rel_pathlist * Build access paths for an "append relation" */ @@ -134,12 +417,6 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, if (IS_DUMMY_REL(childrel)) continue; - /* Bubble up childrel's partitioned children. */ - if (rel->part_scheme) - rel->partitioned_child_rels = - list_concat(rel->partitioned_child_rels, - childrel->partitioned_child_rels); - /* * Child is live, so add it to the live_childrels list for use below. */ @@ -219,10 +496,11 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels) join_search_one_level(root, lev); /* - * Run generate_partitionwise_join_paths() and generate_gather_paths() - * for each just-processed joinrel. We could not do this earlier - * because both regular and partial paths can get added to a - * particular joinrel at multiple times within join_search_one_level. + * Run generate_partitionwise_join_paths() and + * generate_useful_gather_paths() for each just-processed joinrel. We + * could not do this earlier because both regular and partial paths + * can get added to a particular joinrel at multiple times within + * join_search_one_level. * * After that, we're done creating paths for the joinrel, so run * set_cheapest(). diff --git a/expected/init.out b/expected/init.out index 6a0a17e..d443f33 100644 --- a/expected/init.out +++ b/expected/init.out @@ -172,7 +172,6 @@ SELECT * FROM settings; constraint_exclusion | partition | Query Tuning / Other Planner Options cursor_tuple_fraction | 0.1 | Query Tuning / Other Planner Options default_statistics_target | 100 | Query Tuning / Other Planner Options - force_parallel_mode | off | Query Tuning / Other Planner Options from_collapse_limit | 8 | Query Tuning / Other Planner Options jit | on | Query Tuning / Other Planner Options join_collapse_limit | 8 | Query Tuning / Other Planner Options @@ -190,6 +189,7 @@ SELECT * FROM settings; parallel_tuple_cost | 0.1 | Query Tuning / Planner Cost Constants random_page_cost | 4 | Query Tuning / Planner Cost Constants seq_page_cost | 1 | Query Tuning / Planner Cost Constants + enable_async_append | on | Query Tuning / Planner Method Configuration enable_bitmapscan | on | Query Tuning / Planner Method Configuration enable_gathermerge | on | Query Tuning / Planner Method Configuration enable_hashagg | on | Query Tuning / Planner Method Configuration @@ -198,6 +198,7 @@ SELECT * FROM settings; enable_indexonlyscan | on | Query Tuning / Planner Method Configuration enable_indexscan | on | Query Tuning / Planner Method Configuration enable_material | on | Query Tuning / Planner Method Configuration + enable_memoize | on | Query Tuning / Planner Method Configuration enable_mergejoin | on | Query Tuning / Planner Method Configuration enable_nestloop | on | Query Tuning / Planner Method Configuration enable_parallel_append | on | Query Tuning / Planner Method Configuration @@ -208,6 +209,6 @@ SELECT * FROM settings; enable_seqscan | on | Query Tuning / Planner Method Configuration enable_sort | on | Query Tuning / Planner Method Configuration enable_tidscan | on | Query Tuning / Planner Method Configuration -(47 rows) +(48 rows) ANALYZE; diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index 9889949..5148f6a 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -11,14 +11,15 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; (4 rows) EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val; - QUERY PLAN --------------------------------- - Hash Join - Hash Cond: (t2.val = t1.val) + QUERY PLAN +------------------------------------------- + Nested Loop -> Seq Scan on t2 - -> Hash - -> Seq Scan on t1 -(5 rows) + -> Memoize + Cache Key: t2.val + -> Index Scan using t1_val on t1 + Index Cond: (val = t2.val) +(6 rows) LOAD 'pg_hint_plan'; SET pg_hint_plan.debug_print TO on; @@ -32,14 +33,15 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; (4 rows) EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val; - QUERY PLAN --------------------------------- - Hash Join - Hash Cond: (t2.val = t1.val) + QUERY PLAN +------------------------------------------- + Nested Loop -> Seq Scan on t2 - -> Hash - -> Seq Scan on t1 -(5 rows) + -> Memoize + Cache Key: t2.val + -> Index Scan using t1_val on t1 + Index Cond: (val = t2.val) +(6 rows) /*+ Test (t1 t2) */ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id; @@ -5276,39 +5278,39 @@ error hint: Index Cond: (id = $1) (51 rows) --- ambigous error +-- ambiguous error EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id; - QUERY PLAN -------------------------------------------------- - Merge Join - Merge Cond: (t1.id = t2.id) - -> Merge Join - Merge Cond: (t1.id = t1_1.id) + QUERY PLAN +-------------------------------------------- + Nested Loop + -> Nested Loop + -> Seq Scan on t1 t1_1 -> Index Scan using t1_pkey on t1 - -> Index Scan using t1_pkey on t1 t1_1 + Index Cond: (id = t1_1.id) -> Index Scan using t2_pkey on t2 + Index Cond: (id = t1.id) (7 rows) -/*+NestLoop(t1 t2)*/ +/*+MergeJoin(t1 t2)*/ EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id; -INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t2)" +INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t1 t2)" DETAIL: Relation name "t1" is ambiguous. LOG: pg_hint_plan: used hint: not used hint: duplication hint: error hint: -NestLoop(t1 t2) +MergeJoin(t1 t2) - QUERY PLAN -------------------------------------------------- - Merge Join - Merge Cond: (t1.id = t2.id) - -> Merge Join - Merge Cond: (t1.id = t1_1.id) + QUERY PLAN +-------------------------------------------- + Nested Loop + -> Nested Loop + -> Seq Scan on t1 t1_1 -> Index Scan using t1_pkey on t1 - -> Index Scan using t1_pkey on t1 t1_1 + Index Cond: (id = t1_1.id) -> Index Scan using t2_pkey on t2 + Index Cond: (id = t1.id) (7 rows) /*+Leading(t1 t2 t1)*/ @@ -5322,15 +5324,15 @@ duplication hint: error hint: Leading(t1 t2 t1) - QUERY PLAN -------------------------------------------------- - Merge Join - Merge Cond: (t1.id = t2.id) - -> Merge Join - Merge Cond: (t1.id = t1_1.id) + QUERY PLAN +-------------------------------------------- + Nested Loop + -> Nested Loop + -> Seq Scan on t1 t1_1 -> Index Scan using t1_pkey on t1 - -> Index Scan using t1_pkey on t1 t1_1 + Index Cond: (id = t1_1.id) -> Index Scan using t2_pkey on t2 + Index Cond: (id = t1.id) (7 rows) -- identifier length test @@ -8057,7 +8059,7 @@ not used hint: duplication hint: error hint: -CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1" +CONTEXT: SQL statement "SELECT 1, /*+ SeqScan(t1) */ * from t1" PL/pgSQL function testfunc() line 3 at PERFORM testfunc ---------- diff --git a/expected/ut-A.out b/expected/ut-A.out index 9161115..e2a7985 100644 --- a/expected/ut-A.out +++ b/expected/ut-A.out @@ -3239,7 +3239,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3257,6 +3256,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3265,6 +3265,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3275,7 +3276,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) SET pg_hint_plan.parse_messages TO error; /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/ @@ -3296,7 +3297,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3314,6 +3314,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3322,6 +3323,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3332,7 +3334,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; @@ -3372,7 +3374,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3390,6 +3391,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3398,6 +3400,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3408,7 +3411,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) SET pg_hint_plan.parse_messages TO error; /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/ @@ -3429,7 +3432,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3447,6 +3449,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3455,6 +3458,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3465,7 +3469,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) EXPLAIN (COSTS false) EXECUTE p1; QUERY PLAN @@ -3494,7 +3498,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3512,6 +3515,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3520,6 +3524,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3530,7 +3535,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) SET pg_hint_plan.parse_messages TO error; EXPLAIN (COSTS false) EXECUTE p2; @@ -3582,7 +3587,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3600,6 +3604,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3608,6 +3613,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3618,7 +3624,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) -- No. A-12-1-4 -- No. A-12-2-4 @@ -3636,7 +3642,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3654,6 +3659,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3662,6 +3668,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3672,7 +3679,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) SET pg_hint_plan.parse_messages TO error; EXPLAIN (COSTS false) EXECUTE p2; @@ -3702,7 +3709,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3720,6 +3726,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3728,6 +3735,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | on enable_material | on + enable_memoize | on enable_mergejoin | on enable_nestloop | on enable_parallel_append | on @@ -3738,7 +3746,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) DEALLOCATE p1; SET pg_hint_plan.parse_messages TO LOG; @@ -3783,7 +3791,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3801,6 +3808,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3809,6 +3817,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | off enable_material | on + enable_memoize | on enable_mergejoin | off enable_nestloop | on enable_parallel_append | on @@ -3819,7 +3828,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; @@ -3856,7 +3865,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3874,6 +3882,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3882,6 +3891,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | off enable_material | on + enable_memoize | on enable_mergejoin | off enable_nestloop | on enable_parallel_append | on @@ -3892,7 +3902,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; QUERY PLAN @@ -3931,7 +3941,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -3949,6 +3958,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -3957,6 +3967,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | off enable_material | on + enable_memoize | on enable_mergejoin | off enable_nestloop | on enable_parallel_append | on @@ -3967,7 +3978,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) BEGIN; /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/ @@ -4007,7 +4018,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -4025,6 +4035,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -4033,6 +4044,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | off enable_material | on + enable_memoize | on enable_mergejoin | off enable_nestloop | on enable_parallel_append | on @@ -4043,7 +4055,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; QUERY PLAN @@ -4083,7 +4095,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -4101,6 +4112,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -4109,6 +4121,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | off enable_material | on + enable_memoize | on enable_mergejoin | off enable_nestloop | on enable_parallel_append | on @@ -4119,7 +4132,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; @@ -4160,7 +4173,6 @@ SELECT name, setting FROM settings; constraint_exclusion | partition cursor_tuple_fraction | 0.1 default_statistics_target | 100 - force_parallel_mode | off from_collapse_limit | 8 jit | on join_collapse_limit | 8 @@ -4178,6 +4190,7 @@ SELECT name, setting FROM settings; parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 + enable_async_append | on enable_bitmapscan | on enable_gathermerge | on enable_hashagg | on @@ -4186,6 +4199,7 @@ SELECT name, setting FROM settings; enable_indexonlyscan | on enable_indexscan | off enable_material | on + enable_memoize | on enable_mergejoin | off enable_nestloop | on enable_parallel_append | on @@ -4196,7 +4210,7 @@ SELECT name, setting FROM settings; enable_seqscan | on enable_sort | on enable_tidscan | on -(47 rows) +(48 rows) EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1; QUERY PLAN diff --git a/expected/ut-S.out b/expected/ut-S.out index 0f36d22..4e36451 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -5050,16 +5050,17 @@ error hint: -- No. S-3-8-4 EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; - QUERY PLAN ------------------------------ + QUERY PLAN +----------------------------------- Update on p1 - Update on p1 - Update on p1c1 p1_1 - -> Seq Scan on p1 - Filter: (c1 = 1) - -> Seq Scan on p1c1 p1_1 - Filter: (c1 = 1) -(7 rows) + Update on p1 p1_1 + Update on p1c1 p1_2 + -> Append + -> Seq Scan on p1 p1_1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1_2 + Filter: (c1 = 1) +(8 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; @@ -5070,23 +5071,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------- + QUERY PLAN +-------------------------------------------------- Update on p1 - Update on p1 - Update on p1c1 p1_1 - -> Index Scan using p1_i on p1 - Index Cond: (c1 = 1) - -> Index Scan using p1c1_i on p1c1 p1_1 - Index Cond: (c1 = 1) -(7 rows) + Update on p1 p1_1 + Update on p1c1 p1_2 + -> Append + -> Index Scan using p1_i on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1_2 + Index Cond: (c1 = 1) +(8 rows) /*+IndexScan(p1 p1_pkey)*/ EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1; LOG: available indexes for IndexScan(p1): p1_pkey LOG: available indexes for IndexScan(p1c1): p1c1_pkey -LOG: available indexes for IndexScan(p1): p1_pkey -LOG: available indexes for IndexScan(p1c1): p1c1_pkey LOG: pg_hint_plan: used hint: IndexScan(p1 p1_pkey) @@ -5094,16 +5094,17 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------ + QUERY PLAN +----------------------------------------------------- Update on p1 - Update on p1 - Update on p1c1 p1_1 - -> Index Scan using p1_pkey on p1 - Index Cond: (c1 = 1) - -> Index Scan using p1c1_pkey on p1c1 p1_1 - Index Cond: (c1 = 1) -(7 rows) + Update on p1 p1_1 + Update on p1c1 p1_2 + -> Append + -> Index Scan using p1_pkey on p1 p1_1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1_2 + Index Cond: (c1 = 1) +(8 rows) ---- ---- No. S-3-9 inheritance table number diff --git a/make_join_rel.c b/make_join_rel.c index 287e7f1..638e500 100644 --- a/make_join_rel.c +++ b/make_join_rel.c @@ -126,7 +126,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) joinrel = build_join_rel(root, joinrelids, rel1, rel2, sjinfo, &restrictlist); - /* !!! START: HERE IS THE PART WHICH ADDED FOR PG_HINT_PLAN !!! */ + /* !!! START: HERE IS THE PART WHICH IS ADDED FOR PG_HINT_PLAN !!! */ { RowsHint *rows_hint = NULL; int i; @@ -202,7 +202,7 @@ make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2) } } - /* !!! END: HERE IS THE PART WHICH ADDED FOR PG_HINT_PLAN !!! */ + /* !!! END: HERE IS THE PART WHICH IS ADDED FOR PG_HINT_PLAN !!! */ /* * If we've already proven this join is empty, we needn't consider any diff --git a/normalize_query.h b/normalize_query.h index df8e4d6..9357ce2 100644 --- a/normalize_query.h +++ b/normalize_query.h @@ -11,45 +11,7 @@ #ifndef NORMALIZE_QUERY_H #define NORMALIZE_QUERY_H -/* - * Struct for tracking locations/lengths of constants during normalization - */ -typedef struct pgssLocationLen -{ - int location; /* start offset in query text */ - int length; /* length in bytes, or -1 to ignore */ -} pgssLocationLen; - -/* - * Working state for computing a query jumble and producing a normalized - * query string - */ -typedef struct pgssJumbleState -{ - /* Jumble of current query tree */ - unsigned char *jumble; - - /* Number of bytes used in jumble[] */ - Size jumble_len; - - /* Array of locations of constants that should be removed */ - pgssLocationLen *clocations; - - /* Allocated length of clocations array */ - int clocations_buf_size; - - /* Current number of valid entries in clocations array */ - int clocations_count; - - /* highest Param id we've seen, in order to start normalization correctly */ - int highest_extern_param_id; -} pgssJumbleState; - static char * -generate_normalized_query(pgssJumbleState *jstate, const char *query, - int query_loc, int *query_len_p, int encoding); -static void JumbleQuery(pgssJumbleState *jstate, Query *query); - -#define JUMBLE_SIZE 1024 - +generate_normalized_query(JumbleState *jstate, const char *query, + int query_loc, int *query_len_p); #endif /* NORMALIZE_QUERY_H */ diff --git a/pg_hint_plan--1.3.7.sql b/pg_hint_plan--1.4.sql similarity index 93% rename from pg_hint_plan--1.3.7.sql rename to pg_hint_plan--1.4.sql index bda3462..b06ce24 100644 --- a/pg_hint_plan--1.3.7.sql +++ b/pg_hint_plan--1.4.sql @@ -1,4 +1,4 @@ -/* pg_hint_plan/pg_hint_plan--1.3.7.sql */ +/* pg_hint_plan/pg_hint_plan--1.4.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 diff --git a/pg_hint_plan.c b/pg_hint_plan.c index 048e85b..6130800 100644 --- a/pg_hint_plan.c +++ b/pg_hint_plan.c @@ -230,14 +230,6 @@ static unsigned int msgqno = 0; static char qnostr[32]; static const char *current_hint_str = NULL; -/* - * However we usually take a hint stirng in post_parse_analyze_hook, we still - * need to do so in planner_hook when client starts query execution from the - * bind message on a prepared query. This variable prevent duplicate and - * sometimes harmful hint string retrieval. - */ -static bool current_hint_retrieved = false; - /* common data for all hints. */ struct Hint { @@ -400,12 +392,6 @@ void _PG_fini(void); static void push_hint(HintState *hstate); static void pop_hint(void); -static void pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query); -static void pg_hint_plan_ProcessUtility(PlannedStmt *pstmt, - const char *queryString, - ProcessUtilityContext context, - ParamListInfo params, QueryEnvironment *queryEnv, - DestReceiver *dest, QueryCompletion *qc); static PlannedStmt *pg_hint_plan_planner(Query *parse, const char *query_string, int cursorOptions, ParamListInfo boundParams); @@ -489,8 +475,6 @@ static void make_rels_by_clauseless_joins(PlannerInfo *root, static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel); static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte); -static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, - Index rti, RangeTblEntry *rte); RelOptInfo *pg_hint_plan_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2); @@ -562,11 +546,9 @@ static const struct config_enum_entry parse_debug_level_options[] = { }; /* Saved hook values in case of unload */ -static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL; static planner_hook_type prev_planner = NULL; static join_search_hook_type prev_join_search = NULL; static set_rel_pathlist_hook_type prev_set_rel_pathlist = NULL; -static ProcessUtility_hook_type prev_ProcessUtility_hook = NULL; /* Hold reference to currently active hint */ static HintState *current_hint_state = NULL; @@ -688,16 +670,12 @@ _PG_init(void) NULL); /* Install hooks. */ - prev_post_parse_analyze_hook = post_parse_analyze_hook; - post_parse_analyze_hook = pg_hint_plan_post_parse_analyze; prev_planner = planner_hook; planner_hook = pg_hint_plan_planner; prev_join_search = join_search_hook; join_search_hook = pg_hint_plan_join_search; prev_set_rel_pathlist = set_rel_pathlist_hook; set_rel_pathlist_hook = pg_hint_plan_set_rel_pathlist; - prev_ProcessUtility_hook = ProcessUtility_hook; - ProcessUtility_hook = pg_hint_plan_ProcessUtility; /* setup PL/pgSQL plugin hook */ var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin"); @@ -716,11 +694,9 @@ _PG_fini(void) PLpgSQL_plugin **var_ptr; /* Uninstall hooks. */ - post_parse_analyze_hook = prev_post_parse_analyze_hook; planner_hook = prev_planner; join_search_hook = prev_join_search; set_rel_pathlist_hook = prev_set_rel_pathlist; - ProcessUtility_hook = prev_ProcessUtility_hook; /* uninstall PL/pgSQL plugin hook */ var_ptr = (PLpgSQL_plugin **) find_rendezvous_variable("PLpgSQL_plugin"); @@ -1809,125 +1785,6 @@ get_hints_from_table(const char *client_query, const char *client_application) } /* - * Get client-supplied query string. Addtion to that the jumbled query is - * supplied if the caller requested. From the restriction of JumbleQuery, some - * kind of query needs special amendments. Reutrns NULL if this query doesn't - * change the current hint. This function returns NULL also when something - * wrong has happend and let the caller continue using the current hints. - */ -static const char * -get_query_string(ParseState *pstate, Query *query, Query **jumblequery) -{ - const char *p = debug_query_string; - - /* - * If debug_query_string is set, it is the top level statement. But in some - * cases we reach here with debug_query_string set NULL for example in the - * case of DESCRIBE message handling or EXECUTE command. We may still see a - * candidate top-level query in pstate in the case. - */ - if (pstate && pstate->p_sourcetext) - p = pstate->p_sourcetext; - - /* We don't see a query string, return NULL */ - if (!p) - return NULL; - - if (jumblequery != NULL) - *jumblequery = query; - - if (query->commandType == CMD_UTILITY) - { - Query *target_query = (Query *)query->utilityStmt; - - /* - * Some CMD_UTILITY statements have a subquery that we can hint on. - * Since EXPLAIN can be placed before other kind of utility statements - * and EXECUTE can be contained other kind of utility statements, these - * conditions are not mutually exclusive and should be considered in - * this order. - */ - if (IsA(target_query, ExplainStmt)) - { - ExplainStmt *stmt = (ExplainStmt *)target_query; - - Assert(IsA(stmt->query, Query)); - target_query = (Query *)stmt->query; - - /* strip out the top-level query for further processing */ - if (target_query->commandType == CMD_UTILITY && - target_query->utilityStmt != NULL) - target_query = (Query *)target_query->utilityStmt; - } - - if (IsA(target_query, DeclareCursorStmt)) - { - DeclareCursorStmt *stmt = (DeclareCursorStmt *)target_query; - Query *query = (Query *)stmt->query; - - /* the target must be CMD_SELECT in this case */ - Assert(IsA(query, Query) && query->commandType == CMD_SELECT); - target_query = query; - } - - if (IsA(target_query, CreateTableAsStmt)) - { - CreateTableAsStmt *stmt = (CreateTableAsStmt *) target_query; - - Assert(IsA(stmt->query, Query)); - target_query = (Query *) stmt->query; - - /* strip out the top-level query for further processing */ - if (target_query->commandType == CMD_UTILITY && - target_query->utilityStmt != NULL) - target_query = (Query *)target_query->utilityStmt; - } - - if (IsA(target_query, ExecuteStmt)) - { - /* - * Use the prepared query for EXECUTE. The Query for jumble - * also replaced with the corresponding one. - */ - ExecuteStmt *stmt = (ExecuteStmt *)target_query; - PreparedStatement *entry; - - entry = FetchPreparedStatement(stmt->name, true); - - if (entry->plansource->is_valid) - { - p = entry->plansource->query_string; - target_query = (Query *) linitial (entry->plansource->query_list); - } - else - { - /* igonre the hint for EXECUTE if invalidated */ - p = NULL; - target_query = NULL; - } - } - - /* JumbleQuery accespts only a non-utility Query */ - if (target_query && - (!IsA(target_query, Query) || - target_query->utilityStmt != NULL)) - target_query = NULL; - - if (jumblequery) - *jumblequery = target_query; - } - /* - * Return NULL if pstate is not of top-level query. We don't need this - * when jumble info is not requested or cannot do this when pstate is NULL. - */ - else if (!jumblequery && pstate && pstate->p_sourcetext != p && - strcmp(pstate->p_sourcetext, p) != 0) - p = NULL; - - return p; -} - -/* * Get hints from the head block comment in client-supplied query string. */ static const char * @@ -2842,32 +2699,25 @@ pop_hint(void) * Retrieve and store hint string from given query or from the hint table. */ static void -get_current_hint_string(ParseState *pstate, Query *query) +get_current_hint_string(Query *query, const char *query_str) { - const char *query_str; MemoryContext oldcontext; - /* do nothing under hint table search */ + /* do nothing while scanning hint table */ if (hint_inhibit_level > 0) return; - /* We alredy have one, don't parse it again. */ - if (current_hint_retrieved) - return; - - /* Don't parse the current query hereafter */ - current_hint_retrieved = true; - - if (!pg_hint_plan_enable_hint) + /* Make sure trashing old hint string */ + if (current_hint_str) { - if (current_hint_str) - { - pfree((void *)current_hint_str); - current_hint_str = NULL; - } - return; + pfree((void *)current_hint_str); + current_hint_str = NULL; } + /* Return if nothing to do. */ + if (!pg_hint_plan_enable_hint || !query_str) + return; + /* increment the query number */ qnostr[0] = 0; if (debug_level > 1) @@ -2877,121 +2727,71 @@ get_current_hint_string(ParseState *pstate, Query *query) /* search the hint table for a hint if requested */ if (pg_hint_plan_enable_hint_table) { + JumbleState *jstate; int query_len; - pgssJumbleState jstate; - Query *jumblequery; - char *normalized_query = NULL; - - query_str = get_query_string(pstate, query, &jumblequery); - - /* If this query is not for hint, just return */ - if (!query_str) - return; + char *normalized_query; - /* clear the previous hint string */ - if (current_hint_str) - { - pfree((void *)current_hint_str); - current_hint_str = NULL; - } - - if (jumblequery) - { - /* - * XXX: normalization code is copied from pg_stat_statements.c. - * Make sure to keep up-to-date with it. - */ - jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE); - jstate.jumble_len = 0; - jstate.clocations_buf_size = 32; - jstate.clocations = (pgssLocationLen *) - palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen)); - jstate.clocations_count = 0; + jstate = JumbleQuery(query, query_str); - JumbleQuery(&jstate, jumblequery); - - /* - * Normalize the query string by replacing constants with '?' - */ - /* - * Search hint string which is stored keyed by query string - * and application name. The query string is normalized to allow - * fuzzy matching. - * - * Adding 1 byte to query_len ensures that the returned string has - * a terminating NULL. - */ - query_len = strlen(query_str) + 1; - normalized_query = - generate_normalized_query(&jstate, query_str, 0, &query_len, - GetDatabaseEncoding()); + /* + * Normalize the query string by replacing constants with '?' + */ + /* + * Search hint string which is stored keyed by query string + * and application name. The query string is normalized to allow + * fuzzy matching. + * + * Adding 1 byte to query_len ensures that the returned string has + * a terminating NULL. + */ + query_len = strlen(query_str) + 1; + normalized_query = + generate_normalized_query(jstate, query_str, 0, &query_len); - /* - * find a hint for the normalized query. the result should be in - * TopMemoryContext - */ - oldcontext = MemoryContextSwitchTo(TopMemoryContext); - current_hint_str = - get_hints_from_table(normalized_query, application_name); - MemoryContextSwitchTo(oldcontext); + /* + * find a hint for the normalized query. the result should be in + * TopMemoryContext + */ + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + current_hint_str = + get_hints_from_table(normalized_query, application_name); + MemoryContextSwitchTo(oldcontext); - if (debug_level > 1) - { - if (current_hint_str) - ereport(pg_hint_plan_debug_message_level, - (errmsg("pg_hint_plan[qno=0x%x]: " - "post_parse_analyze_hook: " - "hints from table: \"%s\": " - "normalized_query=\"%s\", " - "application name =\"%s\"", - qno, current_hint_str, - normalized_query, application_name), - errhidestmt(msgqno != qno), - errhidecontext(msgqno != qno))); - else - ereport(pg_hint_plan_debug_message_level, - (errmsg("pg_hint_plan[qno=0x%x]: " - "no match found in table: " - "application name = \"%s\", " - "normalized_query=\"%s\"", - qno, application_name, - normalized_query), - errhidestmt(msgqno != qno), - errhidecontext(msgqno != qno))); - - msgqno = qno; - } + if (debug_level > 1) + { + if (current_hint_str) + ereport(pg_hint_plan_debug_message_level, + (errmsg("pg_hint_plan[qno=0x%x]: " + "hints from table: \"%s\": " + "normalized_query=\"%s\", " + "application name =\"%s\"", + qno, current_hint_str, + normalized_query, application_name), + errhidestmt(msgqno != qno), + errhidecontext(msgqno != qno))); + else + ereport(pg_hint_plan_debug_message_level, + (errmsg("pg_hint_plan[qno=0x%x]: " + "no match found in table: " + "application name = \"%s\", " + "normalized_query=\"%s\"", + qno, application_name, + normalized_query), + errhidestmt(msgqno != qno), + errhidecontext(msgqno != qno))); + + msgqno = qno; } - /* retrun if we have hint here */ + /* retrun if we have hint string here */ if (current_hint_str) return; } - else - query_str = get_query_string(pstate, query, NULL); - - if (query_str) - { - /* - * get hints from the comment. However we may have the same query - * string with the previous call, but the extra comparison seems no - * use.. - */ - if (current_hint_str) - pfree((void *)current_hint_str); - oldcontext = MemoryContextSwitchTo(TopMemoryContext); - current_hint_str = get_hints_from_comment(query_str); - MemoryContextSwitchTo(oldcontext); - } - else - { - /* - * Failed to get query. We would be in fetching invalidated - * plancache. Try the next chance. - */ - current_hint_retrieved = false; - } + /* get hints from the comment */ + oldcontext = MemoryContextSwitchTo(TopMemoryContext); + current_hint_str = get_hints_from_comment(query_str); + MemoryContextSwitchTo(oldcontext); if (debug_level > 1) { @@ -3015,44 +2815,6 @@ get_current_hint_string(ParseState *pstate, Query *query) } /* - * Retrieve hint string from the current query. - */ -static void -pg_hint_plan_post_parse_analyze(ParseState *pstate, Query *query) -{ - if (prev_post_parse_analyze_hook) - prev_post_parse_analyze_hook(pstate, query); - - /* always retrieve hint from the top-level query string */ - if (plpgsql_recurse_level == 0) - current_hint_retrieved = false; - - get_current_hint_string(pstate, query); -} - -/* - * We need to reset current_hint_retrieved flag always when a command execution - * is finished. This is true even for a pure utility command that doesn't - * involve planning phase. - */ -static void -pg_hint_plan_ProcessUtility(PlannedStmt *pstmt, const char *queryString, - ProcessUtilityContext context, - ParamListInfo params, QueryEnvironment *queryEnv, - DestReceiver *dest, QueryCompletion *qc) -{ - if (prev_ProcessUtility_hook) - prev_ProcessUtility_hook(pstmt, queryString, context, params, queryEnv, - dest, qc); - else - standard_ProcessUtility(pstmt, queryString, context, params, queryEnv, - dest, qc); - - if (plpgsql_recurse_level == 0) - current_hint_retrieved = false; -} - -/* * Read and set up hint information */ static PlannedStmt * @@ -3082,27 +2844,14 @@ pg_hint_plan_planner(Query *parse, const char *query_string, int cursorOptions, goto standard_planner_proc; } - /* - * Support for nested plpgsql functions. This is quite ugly but this is the - * only point I could find where I can get the query string. - */ - if (plpgsql_recurse_level > 0 && - error_context_stack && error_context_stack->arg) + /* always retrieve hint from the top-level query string */ + if (plpgsql_recurse_level == 0 && current_hint_str) { - MemoryContext oldcontext; - - oldcontext = MemoryContextSwitchTo(TopMemoryContext); - current_hint_str = - get_hints_from_comment((char *)error_context_stack->arg); - MemoryContextSwitchTo(oldcontext); + pfree((void *)current_hint_str); + current_hint_str = NULL; } - /* - * Query execution in extended protocol can be started without the analyze - * phase. In the case retrieve hint string here. - */ - if (!current_hint_str) - get_current_hint_string(NULL, parse); + get_current_hint_string(parse, query_string); /* No hint, go the normal way */ if (!current_hint_str) @@ -3111,9 +2860,18 @@ pg_hint_plan_planner(Query *parse, const char *query_string, int cursorOptions, /* parse the hint into hint state struct */ hstate = create_hintstate(parse, pstrdup(current_hint_str)); - /* run standard planner if the statement has not valid hint */ + /* run standard planner if we're given with no valid hints */ if (!hstate) + { + /* forget invalid hint string */ + if (current_hint_str) + { + pfree((void *)current_hint_str); + current_hint_str = NULL; + } + goto standard_planner_proc; + } /* * Push new hint struct to the hint stack to disable previous hint context. @@ -3201,7 +2959,6 @@ pg_hint_plan_planner(Query *parse, const char *query_string, int cursorOptions, { pfree((void *)current_hint_str); current_hint_str = NULL; - current_hint_retrieved = false; } /* Print hint in debug mode. */ @@ -3821,50 +3578,18 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel, return 0; } - /* - * Forget about the parent of another subquery, but don't forget if the - * inhTargetkind of the root is not INHKIND_NONE, which signals the root - * contains only appendrel members. See inheritance_planner for details. - * - * (PG12.0) 428b260f87 added one more planning cycle for updates on - * partitioned tables and hints set up in the cycle are overriden by the - * second cycle. Since I didn't find no apparent distinction between the - * PlannerRoot of the cycle and that of ordinary CMD_SELECT, pg_hint_plan - * accepts both cycles and the later one wins. In the second cycle root - * doesn't have inheritance information at all so use the parent_relid set - * in the first cycle. - */ - if (root->inhTargetKind == INHKIND_NONE) + if (bms_num_members(rel->top_parent_relids) == 1) { - if (root != current_hint_state->current_root) - current_hint_state->parent_relid = 0; - - /* Find the parent for this relation other than the registered parent */ - foreach (l, root->append_rel_list) - { - AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l); - - if (appinfo->child_relid == rel->relid) - { - if (current_hint_state->parent_relid != appinfo->parent_relid) - { - new_parent_relid = appinfo->parent_relid; - current_hint_state->current_root = root; - } - break; - } - } - - if (!l) - { - /* - * This relation doesn't have a parent. Cancel - * current_hint_state. - */ - current_hint_state->parent_relid = 0; - current_hint_state->parent_scan_hint = NULL; - current_hint_state->parent_parallel_hint = NULL; - } + new_parent_relid = bms_next_member(rel->top_parent_relids, -1); + current_hint_state->current_root = root; + Assert(new_parent_relid > 0); + } + else + { + /* This relation doesn't have a parent. Cancel current_hint_state. */ + current_hint_state->parent_relid = 0; + current_hint_state->parent_scan_hint = NULL; + current_hint_state->parent_parallel_hint = NULL; } if (new_parent_relid > 0) @@ -4858,58 +4583,6 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel, } /* - * set_rel_pathlist - * Build access paths for a base relation - * - * This function was copied and edited from set_rel_pathlist() in - * src/backend/optimizer/path/allpaths.c in order not to copy other static - * functions not required here. - */ -static void -set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, - Index rti, RangeTblEntry *rte) -{ - if (IS_DUMMY_REL(rel)) - { - /* We already proved the relation empty, so nothing more to do */ - } - else if (rte->inh) - { - /* It's an "append relation", process accordingly */ - set_append_rel_pathlist(root, rel, rti, rte); - } - else - { - if (rel->rtekind == RTE_RELATION) - { - if (rte->relkind == RELKIND_RELATION) - { - if(rte->tablesample != NULL) - elog(ERROR, "sampled relation is not supported"); - - /* Plain relation */ - set_plain_rel_pathlist(root, rel, rte); - } - else - elog(ERROR, "unexpected relkind: %c", rte->relkind); - } - else - elog(ERROR, "unexpected rtekind: %d", (int) rel->rtekind); - } - - /* - * Allow a plugin to editorialize on the set of Paths for this base - * relation. It could add new paths (such as CustomPaths) by calling - * add_path(), or delete or modify paths added by the core code. - */ - if (set_rel_pathlist_hook) - (*set_rel_pathlist_hook) (root, rel, rti, rte); - - /* Now find the cheapest of the paths for this rel */ - set_cheapest(rel); -} - -/* * stmt_beg callback is called when each query in PL/pgSQL function is about * to be executed. At that timing, we save query string in the global variable * plpgsql_query_string to use it in planner hook. It's safe to use one global @@ -4945,6 +4618,14 @@ void plpgsql_query_erase_callback(ResourceReleasePhase phase, plpgsql_recurse_level = 0; } + +/* include core static functions */ +static void populate_joinrel_with_paths(PlannerInfo *root, RelOptInfo *rel1, + RelOptInfo *rel2, RelOptInfo *joinrel, + SpecialJoinInfo *sjinfo, List *restrictlist); +static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, + Index rti, RangeTblEntry *rte); + #define standard_join_search pg_hint_plan_standard_join_search #define join_search_one_level pg_hint_plan_join_search_one_level #define make_join_rel make_join_rel_wrapper diff --git a/pg_hint_plan.control b/pg_hint_plan.control index ee3dbea..3c26f9c 100644 --- a/pg_hint_plan.control +++ b/pg_hint_plan.control @@ -1,6 +1,6 @@ # pg_hint_plan extension comment = '' -default_version = '1.3.7' +default_version = '1.4' relocatable = false schema = hint_plan diff --git a/pg_stat_statements.c b/pg_stat_statements.c index f4c5b6c..a1a92c3 100644 --- a/pg_stat_statements.c +++ b/pg_stat_statements.c @@ -10,673 +10,15 @@ */ #include "postgres.h" -#include - -#include "access/hash.h" #include "parser/scanner.h" -static void AppendJumble(pgssJumbleState *jstate, - const unsigned char *item, Size size); -static void JumbleQuery(pgssJumbleState *jstate, Query *query); -static void JumbleRangeTable(pgssJumbleState *jstate, List *rtable); -static void JumbleExpr(pgssJumbleState *jstate, Node *node); -static void RecordConstLocation(pgssJumbleState *jstate, int location); -static char *generate_normalized_query(pgssJumbleState *jstate, const char *query, - int query_loc, int *query_len_p, int encoding); -static void fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, +static char *generate_normalized_query(JumbleState *jstate, const char *query, + int query_loc, int *query_len_p); +static void fill_in_constant_lengths(JumbleState *jstate, const char *query, int query_loc); static int comp_location(const void *a, const void *b); -/* - * AppendJumble: Append a value that is substantive in a given query to - * the current jumble. - */ -static void -AppendJumble(pgssJumbleState *jstate, const unsigned char *item, Size size) -{ - unsigned char *jumble = jstate->jumble; - Size jumble_len = jstate->jumble_len; - - /* - * Whenever the jumble buffer is full, we hash the current contents and - * reset the buffer to contain just that hash value, thus relying on the - * hash to summarize everything so far. - */ - while (size > 0) - { - Size part_size; - - if (jumble_len >= JUMBLE_SIZE) - { - uint64 start_hash; - - start_hash = DatumGetUInt64(hash_any_extended(jumble, - JUMBLE_SIZE, 0)); - memcpy(jumble, &start_hash, sizeof(start_hash)); - jumble_len = sizeof(start_hash); - } - part_size = Min(size, JUMBLE_SIZE - jumble_len); - memcpy(jumble + jumble_len, item, part_size); - jumble_len += part_size; - item += part_size; - size -= part_size; - } - jstate->jumble_len = jumble_len; -} - -/* - * Wrappers around AppendJumble to encapsulate details of serialization - * of individual local variable elements. - */ -#define APP_JUMB(item) \ - AppendJumble(jstate, (const unsigned char *) &(item), sizeof(item)) -#define APP_JUMB_STRING(str) \ - AppendJumble(jstate, (const unsigned char *) (str), strlen(str) + 1) - -/* - * JumbleQuery: Selectively serialize the query tree, appending significant - * data to the "query jumble" while ignoring nonsignificant data. - * - * Rule of thumb for what to include is that we should ignore anything not - * semantically significant (such as alias names) as well as anything that can - * be deduced from child nodes (else we'd just be double-hashing that piece - * of information). - */ -static void -JumbleQuery(pgssJumbleState *jstate, Query *query) -{ - Assert(IsA(query, Query)); - Assert(query->utilityStmt == NULL); - - APP_JUMB(query->commandType); - /* resultRelation is usually predictable from commandType */ - JumbleExpr(jstate, (Node *) query->cteList); - JumbleRangeTable(jstate, query->rtable); - JumbleExpr(jstate, (Node *) query->jointree); - JumbleExpr(jstate, (Node *) query->targetList); - JumbleExpr(jstate, (Node *) query->onConflict); - JumbleExpr(jstate, (Node *) query->returningList); - JumbleExpr(jstate, (Node *) query->groupClause); - JumbleExpr(jstate, (Node *) query->groupingSets); - JumbleExpr(jstate, query->havingQual); - JumbleExpr(jstate, (Node *) query->windowClause); - JumbleExpr(jstate, (Node *) query->distinctClause); - JumbleExpr(jstate, (Node *) query->sortClause); - JumbleExpr(jstate, query->limitOffset); - JumbleExpr(jstate, query->limitCount); - /* we ignore rowMarks */ - JumbleExpr(jstate, query->setOperations); -} - -/* - * Jumble a range table - */ -static void -JumbleRangeTable(pgssJumbleState *jstate, List *rtable) -{ - ListCell *lc; - - foreach(lc, rtable) - { - RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc); - - APP_JUMB(rte->rtekind); - switch (rte->rtekind) - { - case RTE_RELATION: - APP_JUMB(rte->relid); - JumbleExpr(jstate, (Node *) rte->tablesample); - break; - case RTE_SUBQUERY: - JumbleQuery(jstate, rte->subquery); - break; - case RTE_JOIN: - APP_JUMB(rte->jointype); - break; - case RTE_FUNCTION: - JumbleExpr(jstate, (Node *) rte->functions); - break; - case RTE_TABLEFUNC: - JumbleExpr(jstate, (Node *) rte->tablefunc); - break; - case RTE_VALUES: - JumbleExpr(jstate, (Node *) rte->values_lists); - break; - case RTE_CTE: - - /* - * Depending on the CTE name here isn't ideal, but it's the - * only info we have to identify the referenced WITH item. - */ - APP_JUMB_STRING(rte->ctename); - APP_JUMB(rte->ctelevelsup); - break; - case RTE_NAMEDTUPLESTORE: - APP_JUMB_STRING(rte->enrname); - break; - case RTE_RESULT: - break; - default: - elog(ERROR, "unrecognized RTE kind: %d", (int) rte->rtekind); - break; - } - } -} - -/* - * Jumble an expression tree - * - * In general this function should handle all the same node types that - * expression_tree_walker() does, and therefore it's coded to be as parallel - * to that function as possible. However, since we are only invoked on - * queries immediately post-parse-analysis, we need not handle node types - * that only appear in planning. - * - * Note: the reason we don't simply use expression_tree_walker() is that the - * point of that function is to support tree walkers that don't care about - * most tree node types, but here we care about all types. We should complain - * about any unrecognized node type. - */ -static void -JumbleExpr(pgssJumbleState *jstate, Node *node) -{ - ListCell *temp; - - if (node == NULL) - return; - - /* Guard against stack overflow due to overly complex expressions */ - check_stack_depth(); - - /* - * We always emit the node's NodeTag, then any additional fields that are - * considered significant, and then we recurse to any child nodes. - */ - APP_JUMB(node->type); - - switch (nodeTag(node)) - { - case T_Var: - { - Var *var = (Var *) node; - - APP_JUMB(var->varno); - APP_JUMB(var->varattno); - APP_JUMB(var->varlevelsup); - } - break; - case T_Const: - { - Const *c = (Const *) node; - - /* We jumble only the constant's type, not its value */ - APP_JUMB(c->consttype); - /* Also, record its parse location for query normalization */ - RecordConstLocation(jstate, c->location); - } - break; - case T_Param: - { - Param *p = (Param *) node; - - APP_JUMB(p->paramkind); - APP_JUMB(p->paramid); - APP_JUMB(p->paramtype); - /* Also, track the highest external Param id */ - if (p->paramkind == PARAM_EXTERN && - p->paramid > jstate->highest_extern_param_id) - jstate->highest_extern_param_id = p->paramid; - } - break; - case T_Aggref: - { - Aggref *expr = (Aggref *) node; - - APP_JUMB(expr->aggfnoid); - JumbleExpr(jstate, (Node *) expr->aggdirectargs); - JumbleExpr(jstate, (Node *) expr->args); - JumbleExpr(jstate, (Node *) expr->aggorder); - JumbleExpr(jstate, (Node *) expr->aggdistinct); - JumbleExpr(jstate, (Node *) expr->aggfilter); - } - break; - case T_GroupingFunc: - { - GroupingFunc *grpnode = (GroupingFunc *) node; - - JumbleExpr(jstate, (Node *) grpnode->refs); - } - break; - case T_WindowFunc: - { - WindowFunc *expr = (WindowFunc *) node; - - APP_JUMB(expr->winfnoid); - APP_JUMB(expr->winref); - JumbleExpr(jstate, (Node *) expr->args); - JumbleExpr(jstate, (Node *) expr->aggfilter); - } - break; - case T_SubscriptingRef: - { - SubscriptingRef *sbsref = (SubscriptingRef *) node; - - JumbleExpr(jstate, (Node *) sbsref->refupperindexpr); - JumbleExpr(jstate, (Node *) sbsref->reflowerindexpr); - JumbleExpr(jstate, (Node *) sbsref->refexpr); - JumbleExpr(jstate, (Node *) sbsref->refassgnexpr); - } - break; - case T_FuncExpr: - { - FuncExpr *expr = (FuncExpr *) node; - - APP_JUMB(expr->funcid); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_NamedArgExpr: - { - NamedArgExpr *nae = (NamedArgExpr *) node; - - APP_JUMB(nae->argnumber); - JumbleExpr(jstate, (Node *) nae->arg); - } - break; - case T_OpExpr: - case T_DistinctExpr: /* struct-equivalent to OpExpr */ - case T_NullIfExpr: /* struct-equivalent to OpExpr */ - { - OpExpr *expr = (OpExpr *) node; - - APP_JUMB(expr->opno); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_ScalarArrayOpExpr: - { - ScalarArrayOpExpr *expr = (ScalarArrayOpExpr *) node; - - APP_JUMB(expr->opno); - APP_JUMB(expr->useOr); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_BoolExpr: - { - BoolExpr *expr = (BoolExpr *) node; - - APP_JUMB(expr->boolop); - JumbleExpr(jstate, (Node *) expr->args); - } - break; - case T_SubLink: - { - SubLink *sublink = (SubLink *) node; - - APP_JUMB(sublink->subLinkType); - APP_JUMB(sublink->subLinkId); - JumbleExpr(jstate, (Node *) sublink->testexpr); - JumbleQuery(jstate, castNode(Query, sublink->subselect)); - } - break; - case T_FieldSelect: - { - FieldSelect *fs = (FieldSelect *) node; - - APP_JUMB(fs->fieldnum); - JumbleExpr(jstate, (Node *) fs->arg); - } - break; - case T_FieldStore: - { - FieldStore *fstore = (FieldStore *) node; - - JumbleExpr(jstate, (Node *) fstore->arg); - JumbleExpr(jstate, (Node *) fstore->newvals); - } - break; - case T_RelabelType: - { - RelabelType *rt = (RelabelType *) node; - - APP_JUMB(rt->resulttype); - JumbleExpr(jstate, (Node *) rt->arg); - } - break; - case T_CoerceViaIO: - { - CoerceViaIO *cio = (CoerceViaIO *) node; - - APP_JUMB(cio->resulttype); - JumbleExpr(jstate, (Node *) cio->arg); - } - break; - case T_ArrayCoerceExpr: - { - ArrayCoerceExpr *acexpr = (ArrayCoerceExpr *) node; - - APP_JUMB(acexpr->resulttype); - JumbleExpr(jstate, (Node *) acexpr->arg); - JumbleExpr(jstate, (Node *) acexpr->elemexpr); - } - break; - case T_ConvertRowtypeExpr: - { - ConvertRowtypeExpr *crexpr = (ConvertRowtypeExpr *) node; - - APP_JUMB(crexpr->resulttype); - JumbleExpr(jstate, (Node *) crexpr->arg); - } - break; - case T_CollateExpr: - { - CollateExpr *ce = (CollateExpr *) node; - - APP_JUMB(ce->collOid); - JumbleExpr(jstate, (Node *) ce->arg); - } - break; - case T_CaseExpr: - { - CaseExpr *caseexpr = (CaseExpr *) node; - - JumbleExpr(jstate, (Node *) caseexpr->arg); - foreach(temp, caseexpr->args) - { - CaseWhen *when = lfirst_node(CaseWhen, temp); - - JumbleExpr(jstate, (Node *) when->expr); - JumbleExpr(jstate, (Node *) when->result); - } - JumbleExpr(jstate, (Node *) caseexpr->defresult); - } - break; - case T_CaseTestExpr: - { - CaseTestExpr *ct = (CaseTestExpr *) node; - - APP_JUMB(ct->typeId); - } - break; - case T_ArrayExpr: - JumbleExpr(jstate, (Node *) ((ArrayExpr *) node)->elements); - break; - case T_RowExpr: - JumbleExpr(jstate, (Node *) ((RowExpr *) node)->args); - break; - case T_RowCompareExpr: - { - RowCompareExpr *rcexpr = (RowCompareExpr *) node; - - APP_JUMB(rcexpr->rctype); - JumbleExpr(jstate, (Node *) rcexpr->largs); - JumbleExpr(jstate, (Node *) rcexpr->rargs); - } - break; - case T_CoalesceExpr: - JumbleExpr(jstate, (Node *) ((CoalesceExpr *) node)->args); - break; - case T_MinMaxExpr: - { - MinMaxExpr *mmexpr = (MinMaxExpr *) node; - - APP_JUMB(mmexpr->op); - JumbleExpr(jstate, (Node *) mmexpr->args); - } - break; - case T_SQLValueFunction: - { - SQLValueFunction *svf = (SQLValueFunction *) node; - - APP_JUMB(svf->op); - /* type is fully determined by op */ - APP_JUMB(svf->typmod); - } - break; - case T_XmlExpr: - { - XmlExpr *xexpr = (XmlExpr *) node; - - APP_JUMB(xexpr->op); - JumbleExpr(jstate, (Node *) xexpr->named_args); - JumbleExpr(jstate, (Node *) xexpr->args); - } - break; - case T_NullTest: - { - NullTest *nt = (NullTest *) node; - - APP_JUMB(nt->nulltesttype); - JumbleExpr(jstate, (Node *) nt->arg); - } - break; - case T_BooleanTest: - { - BooleanTest *bt = (BooleanTest *) node; - APP_JUMB(bt->booltesttype); - JumbleExpr(jstate, (Node *) bt->arg); - } - break; - case T_CoerceToDomain: - { - CoerceToDomain *cd = (CoerceToDomain *) node; - - APP_JUMB(cd->resulttype); - JumbleExpr(jstate, (Node *) cd->arg); - } - break; - case T_CoerceToDomainValue: - { - CoerceToDomainValue *cdv = (CoerceToDomainValue *) node; - - APP_JUMB(cdv->typeId); - } - break; - case T_SetToDefault: - { - SetToDefault *sd = (SetToDefault *) node; - - APP_JUMB(sd->typeId); - } - break; - case T_CurrentOfExpr: - { - CurrentOfExpr *ce = (CurrentOfExpr *) node; - - APP_JUMB(ce->cvarno); - if (ce->cursor_name) - APP_JUMB_STRING(ce->cursor_name); - APP_JUMB(ce->cursor_param); - } - break; - case T_NextValueExpr: - { - NextValueExpr *nve = (NextValueExpr *) node; - - APP_JUMB(nve->seqid); - APP_JUMB(nve->typeId); - } - break; - case T_InferenceElem: - { - InferenceElem *ie = (InferenceElem *) node; - - APP_JUMB(ie->infercollid); - APP_JUMB(ie->inferopclass); - JumbleExpr(jstate, ie->expr); - } - break; - case T_TargetEntry: - { - TargetEntry *tle = (TargetEntry *) node; - - APP_JUMB(tle->resno); - APP_JUMB(tle->ressortgroupref); - JumbleExpr(jstate, (Node *) tle->expr); - } - break; - case T_RangeTblRef: - { - RangeTblRef *rtr = (RangeTblRef *) node; - - APP_JUMB(rtr->rtindex); - } - break; - case T_JoinExpr: - { - JoinExpr *join = (JoinExpr *) node; - - APP_JUMB(join->jointype); - APP_JUMB(join->isNatural); - APP_JUMB(join->rtindex); - JumbleExpr(jstate, join->larg); - JumbleExpr(jstate, join->rarg); - JumbleExpr(jstate, join->quals); - } - break; - case T_FromExpr: - { - FromExpr *from = (FromExpr *) node; - - JumbleExpr(jstate, (Node *) from->fromlist); - JumbleExpr(jstate, from->quals); - } - break; - case T_OnConflictExpr: - { - OnConflictExpr *conf = (OnConflictExpr *) node; - - APP_JUMB(conf->action); - JumbleExpr(jstate, (Node *) conf->arbiterElems); - JumbleExpr(jstate, conf->arbiterWhere); - JumbleExpr(jstate, (Node *) conf->onConflictSet); - JumbleExpr(jstate, conf->onConflictWhere); - APP_JUMB(conf->constraint); - APP_JUMB(conf->exclRelIndex); - JumbleExpr(jstate, (Node *) conf->exclRelTlist); - } - break; - case T_List: - foreach(temp, (List *) node) - { - JumbleExpr(jstate, (Node *) lfirst(temp)); - } - break; - case T_IntList: - foreach(temp, (List *) node) - { - APP_JUMB(lfirst_int(temp)); - } - break; - case T_SortGroupClause: - { - SortGroupClause *sgc = (SortGroupClause *) node; - - APP_JUMB(sgc->tleSortGroupRef); - APP_JUMB(sgc->eqop); - APP_JUMB(sgc->sortop); - APP_JUMB(sgc->nulls_first); - } - break; - case T_GroupingSet: - { - GroupingSet *gsnode = (GroupingSet *) node; - - JumbleExpr(jstate, (Node *) gsnode->content); - } - break; - case T_WindowClause: - { - WindowClause *wc = (WindowClause *) node; - - APP_JUMB(wc->winref); - APP_JUMB(wc->frameOptions); - JumbleExpr(jstate, (Node *) wc->partitionClause); - JumbleExpr(jstate, (Node *) wc->orderClause); - JumbleExpr(jstate, wc->startOffset); - JumbleExpr(jstate, wc->endOffset); - } - break; - case T_CommonTableExpr: - { - CommonTableExpr *cte = (CommonTableExpr *) node; - - /* we store the string name because RTE_CTE RTEs need it */ - APP_JUMB_STRING(cte->ctename); - APP_JUMB(cte->ctematerialized); - JumbleQuery(jstate, castNode(Query, cte->ctequery)); - } - break; - case T_SetOperationStmt: - { - SetOperationStmt *setop = (SetOperationStmt *) node; - - APP_JUMB(setop->op); - APP_JUMB(setop->all); - JumbleExpr(jstate, setop->larg); - JumbleExpr(jstate, setop->rarg); - } - break; - case T_RangeTblFunction: - { - RangeTblFunction *rtfunc = (RangeTblFunction *) node; - - JumbleExpr(jstate, rtfunc->funcexpr); - } - break; - case T_TableFunc: - { - TableFunc *tablefunc = (TableFunc *) node; - - JumbleExpr(jstate, tablefunc->docexpr); - JumbleExpr(jstate, tablefunc->rowexpr); - JumbleExpr(jstate, (Node *) tablefunc->colexprs); - } - break; - case T_TableSampleClause: - { - TableSampleClause *tsc = (TableSampleClause *) node; - - APP_JUMB(tsc->tsmhandler); - JumbleExpr(jstate, (Node *) tsc->args); - JumbleExpr(jstate, (Node *) tsc->repeatable); - } - break; - default: - /* Only a warning, since we can stumble along anyway */ - elog(WARNING, "unrecognized node type: %d", - (int) nodeTag(node)); - break; - } -} - -/* - * Record location of constant within query string of query tree - * that is currently being walked. - */ -static void -RecordConstLocation(pgssJumbleState *jstate, int location) -{ - /* -1 indicates unknown or undefined location */ - if (location >= 0) - { - /* enlarge array if needed */ - if (jstate->clocations_count >= jstate->clocations_buf_size) - { - jstate->clocations_buf_size *= 2; - jstate->clocations = (pgssLocationLen *) - repalloc(jstate->clocations, - jstate->clocations_buf_size * - sizeof(pgssLocationLen)); - } - jstate->clocations[jstate->clocations_count].location = location; - /* initialize lengths to -1 to simplify fill_in_constant_lengths */ - jstate->clocations[jstate->clocations_count].length = -1; - jstate->clocations_count++; - } -} /* * Generate a normalized version of the query string that will be used to @@ -698,8 +40,8 @@ RecordConstLocation(pgssJumbleState *jstate, int location) * Returns a palloc'd string. */ static char * -generate_normalized_query(pgssJumbleState *jstate, const char *query, - int query_loc, int *query_len_p, int encoding) +generate_normalized_query(JumbleState *jstate, const char *query, + int query_loc, int *query_len_p) { char *norm_query; int query_len = *query_len_p; @@ -751,12 +93,11 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, memcpy(norm_query + n_quer_loc, query + quer_loc, len_to_wrt); n_quer_loc += len_to_wrt; - /* - * PG_HINT_PLAN: DON'T TAKE IN a6f22e8356 so that the designed behavior - * is kept stable. - */ - /* And insert a '?' in place of the constant token */ - norm_query[n_quer_loc++] = '?'; + /* And insert a param symbol in place of the constant token */ + + /* !!! START: HERE IS THE PART WHICH IS MODIFIED FOR PG_HINT_PLAN !!! */ + n_quer_loc += sprintf(norm_query + n_quer_loc, "?"); + /* !!! END: HERE IS THE PART WHICH IS MODIFIED FOR PG_HINT_PLAN !!! */ quer_loc = off + tok_len; last_off = off; @@ -808,10 +149,10 @@ generate_normalized_query(pgssJumbleState *jstate, const char *query, * reason for a constant to start with a '-'. */ static void -fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, +fill_in_constant_lengths(JumbleState *jstate, const char *query, int query_loc) { - pgssLocationLen *locs; + LocationLen *locs; core_yyscan_t yyscanner; core_yy_extra_type yyextra; core_YYSTYPE yylval; @@ -825,7 +166,7 @@ fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, */ if (jstate->clocations_count > 1) qsort(jstate->clocations, jstate->clocations_count, - sizeof(pgssLocationLen), comp_location); + sizeof(LocationLen), comp_location); locs = jstate->clocations; /* initialize the flex scanner --- should match raw_parser() */ @@ -905,13 +246,13 @@ fill_in_constant_lengths(pgssJumbleState *jstate, const char *query, } /* - * comp_location: comparator for qsorting pgssLocationLen structs by location + * comp_location: comparator for qsorting LocationLen structs by location */ static int comp_location(const void *a, const void *b) { - int l = ((const pgssLocationLen *) a)->location; - int r = ((const pgssLocationLen *) b)->location; + int l = ((const LocationLen *) a)->location; + int r = ((const LocationLen *) b)->location; if (l < r) return -1; diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql index 7309e1e..a5309cb 100644 --- a/sql/pg_hint_plan.sql +++ b/sql/pg_hint_plan.sql @@ -592,9 +592,9 @@ SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.i SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id ); --- ambigous error +-- ambiguous error EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id; -/*+NestLoop(t1 t2)*/ +/*+MergeJoin(t1 t2)*/ EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id; /*+Leading(t1 t2 t1)*/ EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id; -- 2.11.0