contrib/Makefile | 3 +- contrib/xtime/Makefile | 14 + contrib/xtime/xtime.c | 646 +++++++++++++++++++++++++++++ doc/src/sgml/contrib.sgml | 1 + doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/xtime.sgml | 110 +++++ src/backend/commands/explain.c | 69 +++ src/backend/executor/Makefile | 3 +- src/backend/executor/execAmi.c | 18 + src/backend/executor/execProcnode.c | 18 + src/backend/executor/nodeCustomPlan.c | 325 +++++++++++++++ src/backend/nodes/copyfuncs.c | 26 ++ src/backend/nodes/outfuncs.c | 15 + src/include/executor/nodeCustomPlan.h | 55 +++ src/include/nodes/execnodes.h | 28 ++ src/include/nodes/nodes.h | 2 + src/include/nodes/plannodes.h | 37 ++ src/test/regress/GNUmakefile | 14 +- src/test/regress/input/custom_exec.source | 184 ++++++++ src/test/regress/output/custom_exec.source | 541 ++++++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + 22 files changed, 2107 insertions(+), 6 deletions(-) diff --git a/contrib/Makefile b/contrib/Makefile index 8a2a937..33a5c42 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -53,7 +53,8 @@ SUBDIRS = \ tsearch2 \ unaccent \ vacuumlo \ - worker_spi + worker_spi \ + xtime ifeq ($(with_openssl),yes) SUBDIRS += sslinfo diff --git a/contrib/xtime/Makefile b/contrib/xtime/Makefile new file mode 100644 index 0000000..ee64b58 --- /dev/null +++ b/contrib/xtime/Makefile @@ -0,0 +1,14 @@ +# contrib/xtime/Makefile + +MODULES = xtime + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/xtime +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/xtime/xtime.c b/contrib/xtime/xtime.c new file mode 100644 index 0000000..3b92484 --- /dev/null +++ b/contrib/xtime/xtime.c @@ -0,0 +1,646 @@ +/* + * xtime.c + * + * An example module for custom executor APIs. It prints time to execute + * underlying execution node. + * + * Copyright (C) 2013, PostgreSQL Global Development Group + */ +#include "postgres.h" + +#include "access/relscan.h" +#include "executor/nodeCustomPlan.h" +#include "commands/explain.h" +#include "lib/ilist.h" +#include "nodes/execnodes.h" +#include "nodes/makefuncs.h" +#include "nodes/nodeFuncs.h" +#include "optimizer/planner.h" +#include "parser/parsetree.h" +#include "utils/guc.h" +#include "utils/plancache.h" +#include "utils/rel.h" +#include "sys/time.h" + +PG_MODULE_MAGIC; + +void _PG_init(void); + +typedef struct { + int nest_level; + struct timeval elapsed_time; + const char *signature; +} xtime_state; + +/* saved planner hook */ +static planner_hook_type original_planner_hook = NULL; + +/* + * stuff related to xtime.mode. + * + * It has the third mode, not only "on" and "off", for regression test + * usage. The "regtest" mode performs as "on" doing, except for it does + * not print elapsed time being tracked. + */ +#define XTIME_MODE_DISABLED 0 +#define XTIME_MODE_ENABLED 1 +#define XTIME_MODE_REGTEST 2 +static int xtime_mode = XTIME_MODE_DISABLED; +static struct config_enum_entry xtime_mode_options[] = { + { "off", XTIME_MODE_DISABLED, false }, + { "disabled", XTIME_MODE_DISABLED, true }, + { "true", XTIME_MODE_DISABLED, true }, + { "on", XTIME_MODE_ENABLED, false }, + { "enabled", XTIME_MODE_ENABLED, true }, + { "false", XTIME_MODE_ENABLED, true }, + { "regtest", XTIME_MODE_REGTEST, false }, +}; + +/* + * we need to reset planned cache, if xtime.mode was updated because it + * affects the saved plan being constructed based on older mode. + */ +static void +xtime_mode_assign(int newval, void *extra) +{ + if (newval != xtime_mode) + ResetPlanCache(); +} + +/* + * xtime_plan_signature + * + * It returns a signature string of the provided plan. + */ +static const char * +xtime_plan_signature(Plan *plan, List *rtable) +{ + RangeTblEntry *rte; + char namebuf[80 + NAMEDATALEN]; + + switch (nodeTag(plan)) + { + case T_Result: + return "Result"; + case T_ModifyTable: + switch (((ModifyTable *) plan)->operation) + { + case CMD_INSERT: + return "ModifyTable (Insert)"; + case CMD_UPDATE: + return "ModifyTable (Update)"; + case CMD_DELETE: + return "ModifyTable (Delete)"; + default: + return "ModifyTable (unknown)"; + } + break; + case T_Append: + return "Append"; + case T_MergeAppend: + return "Merge Append"; + case T_RecursiveUnion: + return "Recursive Union"; + case T_BitmapAnd: + return "BitmapAnd"; + case T_BitmapOr: + return "BitmapOr"; + case T_NestLoop: + return "Nested Loop"; + case T_MergeJoin: + return "Merge Join"; + case T_HashJoin: + return "Hash Join"; + case T_SeqScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Seq Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_IndexScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Index Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_IndexOnlyScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Index Only Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_BitmapIndexScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Bitmap Index Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_BitmapHeapScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Bitmap Heap Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_TidScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Tid Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_SubqueryScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Subquery Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_FunctionScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Function Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_ValuesScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Values Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_CteScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "CTE Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_WorkTableScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "WorkTable Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_ForeignScan: + rte = rt_fetch(((Scan *)plan)->scanrelid, rtable); + snprintf(namebuf, sizeof(namebuf), + "Foreign Scan on %s", rte->eref->aliasname); + return pstrdup(namebuf); + case T_Material: + return "Materialize"; + case T_Sort: + return "Sort"; + case T_Group: + return "Group"; + case T_Agg: + switch (((Agg *) plan)->aggstrategy) + { + case AGG_PLAIN: + return "Aggregate (Plain)"; + case AGG_SORTED: + return "GroupAggregate (Sorted)"; + case AGG_HASHED: + return "HashAggregate (Hashed)"; + default: + return "Aggregate (unknown)"; + } + break; + case T_WindowAgg: + return "WindowAgg"; + case T_Unique: + return "Unique"; + case T_SetOp: + switch (((SetOp *) plan)->strategy) + { + case SETOP_SORTED: + return "SetOp (Sorted)"; + case SETOP_HASHED: + return "HashSetOp (Hashed)"; + default: + return "SetOp (unknown)"; + } + break; + case T_LockRows: + return "LockRows"; + case T_Limit: + return "Limit"; + case T_Hash: + return "Hash"; + default: + break; + } + return "???"; +} + +/* + * xtime_begin + * + * BeginCustomPlan handler + */ +static void +xtime_begin(CustomPlanState *node, int eflags) +{ + CustomPlan *custom = (CustomPlan *)node->ss.ps.plan; + xtime_state *xstate; + ListCell *cell; + int nest_level = 0; + + if (eflags & EXEC_FLAG_EXPLAIN_ONLY) + return; + + foreach (cell, custom->cust_private) + { + DefElem *defel = lfirst(cell); + + if (strcmp(defel->defname, "nest_level") == 0) + nest_level = intVal(defel->arg); + else + elog(ERROR, "unexpected xtime planner info: %s", + nodeToString(defel)); + } + + xstate = palloc0(sizeof(xtime_state)); + xstate->nest_level = nest_level; + + /* + * This routine shall be called back with valid relation handler + * if CustomPlan replaced an existing SeqScan plan and set a valid + * index on scanrenid. In this case, xtime custom plan performs as + * if SeqScan is doing. So, it also initializes scan descriptor + * according to the manner of sequential scan. + * + * Elsewhere, this node just performs to call its underlying node. + * So, no need to set up any extra stuff for self relation scan. + */ + if (node->ss.ss_currentRelation) + { + Relation relation = node->ss.ss_currentRelation; + Snapshot snapshot = node->ss.ps.state->es_snapshot; + char namebuf[80 + NAMEDATALEN]; + + node->ss.ss_currentScanDesc = heap_beginscan(relation, + snapshot, + 0, + NULL); + snprintf(namebuf, sizeof(namebuf), + "CustomPlan:xtime on %s", + RelationGetRelationName(relation)); + xstate->signature = pstrdup(namebuf); + } + else + { + Plan *subplan = innerPlanState(node)->plan; + List *rtables = node->ss.ps.state->es_range_table; + + xstate->signature = xtime_plan_signature(subplan, rtables); + } + node->cust_state = xstate; +} + +/* + * xtime_rel_getnext + * + * ExecCustomPlan handler + * It has to perform as if ExecSeqScan is doing if this custom plan works + * to scan a relation by itself. So, it calls ExecScan with two callbacks + * that implement same jobs in SeqNext and SeqRecheck. + */ +static TupleTableSlot * +xtime_rel_getnext(CustomPlanState *node) +{ + HeapTuple tuple; + + tuple = heap_getnext(node->ss.ss_currentScanDesc, + node->ss.ps.state->es_direction); + if (tuple) + ExecStoreTuple(tuple, + node->ss.ss_ScanTupleSlot, + node->ss.ss_currentScanDesc->rs_cbuf, + false); + else + ExecClearTuple(node->ss.ss_ScanTupleSlot); + + return node->ss.ss_ScanTupleSlot; +} + +static bool +xtime_rel_recheck(ScanState *node, TupleTableSlot *slot) +{ + return true; +} + +static TupleTableSlot * +xtime_exec(CustomPlanState *node) +{ + xtime_state *xstate = node->cust_state; + TupleTableSlot *slot; + struct timeval tv1, tv2; + + if (xtime_mode != XTIME_MODE_DISABLED) + gettimeofday(&tv1, NULL); + + if (node->ss.ss_currentRelation) + { + slot = ExecScan((ScanState *)node, + (ExecScanAccessMtd) xtime_rel_getnext, + (ExecScanRecheckMtd) xtime_rel_recheck); + } + else + { + slot = ExecProcNode(innerPlanState(node)); + } + + if (xtime_mode != XTIME_MODE_DISABLED) + { + gettimeofday(&tv2, NULL); + xstate->elapsed_time.tv_sec += tv2.tv_sec - tv1.tv_sec; + xstate->elapsed_time.tv_usec += tv2.tv_usec - tv1.tv_usec; + } + return slot; +} + +/* + * xtime_multi_exec + * + * MultiExecCustomPlan handler + */ +static Node * +xtime_multi_exec(CustomPlanState *node) +{ + xtime_state *xstate = node->cust_state; + Node *result; + struct timeval tv1, tv2; + + Assert(node->ss.ss_currentRelation == NULL); + + if (xtime_mode != XTIME_MODE_DISABLED) + gettimeofday(&tv1, NULL); + + result = MultiExecProcNode(innerPlanState(node)); + + if (xtime_mode != XTIME_MODE_DISABLED) + { + gettimeofday(&tv2, NULL); + xstate->elapsed_time.tv_sec += tv2.tv_sec - tv1.tv_sec; + xstate->elapsed_time.tv_usec += tv2.tv_usec - tv1.tv_usec; + } + return result; +} + +/* + * xtime_end + * + * EndCustomPlan handler + */ +static void +xtime_end(CustomPlanState *node) +{ + xtime_state *xstate = node->cust_state; + StringInfoData str; + + if (!xstate) + return; + + /* + * Release resources being acquired by itself, however, rest of ones + * are released by the framework, so extension does not need to care + * about them, like per-scan memory context and so on. + * In this case, all we have to clean up is scan descriptor if custom + * plan performs to scan a relation by itself. + */ + if (node->ss.ss_currentRelation) + heap_endscan(node->ss.ss_currentScanDesc); + + if (xtime_mode == XTIME_MODE_DISABLED) + return; + + initStringInfo(&str); + appendStringInfoSpaces(&str, xstate->nest_level); + appendStringInfo(&str, "execution time of %s: ", xstate->signature); + if (xtime_mode == XTIME_MODE_ENABLED) + { + double elapsed = ((double)(xstate->elapsed_time.tv_sec * 1000000 + + xstate->elapsed_time.tv_usec)) / 1000.0; + appendStringInfo(&str, "% .3f ms", elapsed); + } + else + appendStringInfo(&str, "**.*** ms"); + + elog(INFO, "%s", str.data); + pfree(str.data); +} + +/* + * xtime_rescan + * + * ReScanCustomPlan handler + */ +static void +xtime_rescan(CustomPlanState *node) +{ + if (node->ss.ss_currentRelation) + { + heap_rescan(node->ss.ss_currentScanDesc, NULL); + + ExecScanReScan((ScanState *) node); + } + else if (innerPlanState(node)->chgParam == NULL) + ExecReScan(innerPlanState(node)); +} + +/* + * xtime_mark_pos + * + * ExecMarkPosCustomPlan handler + */ +static void +xtime_mark_pos(CustomPlanState *node) +{ + if (node->ss.ss_currentRelation) + { + heap_markpos(node->ss.ss_currentScanDesc); + } + else + { + ExecMarkPos(innerPlanState(node)); + } +} + +/* + * xtime_restr_pos + * + * ExecRestrPosCustomPlan + */ +static void +xtime_restr_pos(CustomPlanState *node) +{ + if (node->ss.ss_currentRelation) + { + ExecClearTuple(node->ss.ss_ScanTupleSlot); + + heap_restrpos(node->ss.ss_currentScanDesc); + } + else + { + ExecRestrPos(innerPlanState(node)); + } +} + +static Plan * +xtime_create_plan(Plan *plan, int nest_level) +{ + CustomPlan *custom = makeNode(CustomPlan); + DefElem *defel; + + /* + * No idea how to handle cost parameter after all the planning jobs. + * So, simply we copies the cost parameters from the original or + * underlying plan node. + */ + custom->scan.plan.startup_cost = plan->startup_cost; + custom->scan.plan.total_cost = plan->total_cost; + custom->scan.plan.plan_rows = plan->plan_rows; + custom->scan.plan.plan_width = plan->plan_width; + + /* paramIDs being affected are not changed */ + custom->scan.plan.extParam = bms_copy(plan->extParam); + custom->scan.plan.allParam = bms_copy(plan->allParam); + + /* + * To use this module as an example of custom plan node, we demonstrate + * two different usage. The first case replaces an existing SeqScan node + * by custom plan node with self relation scanning. So, we put a valid + * scanrelid to suggest framework to open the target relation with + * suitable lock level. It also make sense to reduce possible projection + * if targetlist is compatible with relation form. The second case + * inserts a custom plan node on head of the target plan. + */ + if (IsA(plan, SeqScan)) + { + custom->scan.scanrelid = ((SeqScan *)plan)->scanrelid; + custom->scan.plan.targetlist = ((SeqScan *)plan)->plan.targetlist; + custom->scan.plan.qual = ((SeqScan *)plan)->plan.qual; + } + else + { + List *my_tlist = NIL; + ListCell *cell; + + /* + * Construct a targetlist that just reference underlying plan node, + * because upper node assumes compatible TupleDesc. + */ + foreach (cell, plan->targetlist) + { + TargetEntry *tle = lfirst(cell); + TargetEntry *my_tle; + Var *my_var; + char *resname; + + my_var = makeVar(INNER_VAR, + tle->resno, + exprType((Node *)tle->expr), + exprTypmod((Node *)tle->expr), + exprCollation((Node *)tle->expr), + 0); + resname = (tle->resname ? pstrdup(tle->resname) : NULL); + my_tle = makeTargetEntry((Expr *)my_var, + tle->resno, + resname, + tle->resjunk); + my_tlist = lappend(my_tlist, my_tle); + } + custom->scan.plan.targetlist = my_tlist; + custom->scan.plan.qual = NIL; + innerPlan(custom) = plan; + } + custom->cust_name = pstrdup("xtime"); + defel = makeDefElem("nest_level", (Node *)makeInteger(nest_level)); + custom->cust_private = list_make1((Node *)defel); + + return (Plan *)custom; +} + +static Plan * +xtime_subplan_walker(Plan *plan, int nest_level) +{ + ListCell *cell; + + if (IsA(plan, Append)) + { + foreach (cell, ((Append *) plan)->appendplans) + lfirst(cell) = xtime_subplan_walker((Plan *)lfirst(cell), + nest_level + 1); + } + else if (IsA(plan, ModifyTable)) + { + foreach (cell, ((ModifyTable *)plan)->plans) + lfirst(cell) = xtime_subplan_walker((Plan *)lfirst(cell), + nest_level + 1); + } + + if (plan->lefttree) + plan->lefttree = xtime_subplan_walker(plan->lefttree, + nest_level + 1); + if (plan->righttree) + plan->righttree = xtime_subplan_walker(plan->righttree, + nest_level + 1); + /* + * Note that Hash node is tightly coupled to HashJoin, so it makes + * problem if CustomExex would be injected between them. + */ + if (IsA(plan, Hash)) + return plan; + + return xtime_create_plan(plan, nest_level); +} + +/* + * xtime_planner + * + * It tries to rewrite the plan tree being constructed at the planner. + */ +static PlannedStmt * +xtime_planner(Query *parse, + int cursorOptions, + ParamListInfo boundParams) +{ + PlannedStmt *result; + + if (original_planner_hook) + result = original_planner_hook(parse, cursorOptions, boundParams); + else + result = standard_planner(parse, cursorOptions, boundParams); + + /* walk on underlying plan tree to inject custom-exec node */ + if (xtime_mode != XTIME_MODE_DISABLED) + result->planTree = xtime_subplan_walker(result->planTree, 0); + + return result; +} + +void +_PG_init(void) +{ + CustomPlanRoutine routine; + + /* + * Add custom GUC variable + */ + DefineCustomEnumVariable("xtime.mode", + "performing mode of xtime extension", + NULL, + &xtime_mode, + XTIME_MODE_ENABLED, + xtime_mode_options, + PGC_USERSET, + GUC_NOT_IN_SAMPLE, + NULL, xtime_mode_assign, NULL); + + /* + * Registration of custom executor provider + */ + strcpy(routine.CustomPlanName, "xtime"); + routine.IsSupportBackwardScan = true; + routine.BeginCustomPlan = xtime_begin; + routine.ExecCustomPlan = xtime_exec; + routine.MultiExecCustomPlan = xtime_multi_exec; + routine.EndCustomPlan = xtime_end; + routine.ReScanCustomPlan = xtime_rescan; + routine.ExecMarkPosCustomPlan = xtime_mark_pos; + routine.ExecRestrPosCustomPlan = xtime_restr_pos; + routine.ExplainCustomPlan = NULL; /* no additional information */ + RegisterCustomPlan(&routine); + + /* + * Registration of planner_hook + */ + if (planner_hook) + original_planner_hook = planner_hook; + planner_hook = xtime_planner; +} diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml index dd8e09e..7ad68ca 100644 --- a/doc/src/sgml/contrib.sgml +++ b/doc/src/sgml/contrib.sgml @@ -144,6 +144,7 @@ CREATE EXTENSION module_name FROM unpackaged; &unaccent; &uuid-ossp; &xml2; + &xtime; diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 914090d..8bd8243 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -148,6 +148,7 @@ + diff --git a/doc/src/sgml/xtime.sgml b/doc/src/sgml/xtime.sgml new file mode 100644 index 0000000..3074ec2 --- /dev/null +++ b/doc/src/sgml/xtime.sgml @@ -0,0 +1,110 @@ + + + + xtime + + + xtime + + + + xtime is designed to provide a simple code + example to utilize custom plan node feature to override a part of + executor logic, but it also expose which node consumes how much + time on its execution. + + + Once xtime is enabled, it injects its + CustomPlan node on top of each executor node. + will show you how does it works. + The example below shows plan tree of a simple query that + contains sort, join, index and sequential scan. + +postgres=# EXPLAIN (costs off) + SELECT * FROM t1 JOIN t2 ON t1.a = t2.x + WHERE x BETWEEN 1000 AND 1200 ORDER BY y; + QUERY PLAN +----------------------------------------------------- + Sort + Sort Key: t2.y + -> Nested Loop + -> Seq Scan on t2 + Filter: ((x >= 1000) AND (x <= 1200)) + -> Index Scan using t1_pkey on t1 + Index Cond: (a = t2.x) +(7 rows) + + Once xtime loaded, it rewrites the plan + tree being constructed using planner_hook, + as follows: + +postgres=# LOAD '$libdir/xtime'; +LOAD +postgres=# EXPLAIN (costs off) + SELECT * FROM t1 JOIN t2 ON t1.a = t2.x + WHERE x BETWEEN 1000 AND 1200 ORDER BY y; + QUERY PLAN +----------------------------------------------------------------- + CustomPlan:xtime + -> Sort + Sort Key: y + -> CustomPlan:xtime + -> Nested Loop + -> CustomPlan:xtime on t2 + Filter: ((x >= 1000) AND (x <= 1200)) + -> CustomPlan:xtime + -> Index Scan using t1_pkey on t1 + Index Cond: (a = x) +(10 rows) + + Each CustomPlan of xtime records the + time when underlying executor node was started and ended for + each execution, then it prints total time consumption at end + of the query execution. + +postgres=# \timing +Timing is on. +postgres=# SELECT * FROM t1 JOIN t2 ON t1.a = t2.x WHERE x BETWEEN 1000 AND 1200 ORDER BY y; +INFO: execution time of Sort: 28.508 ms +INFO: execution time of Nested Loop: 28.100 ms +INFO: execution time of CustomPlan:xtime on t2: 26.658 ms +INFO: execution time of Index Scan on t1: 1.183 ms + : + <snip> + : +Time: 30.794 ms + + + + + Configuration Parameters + + + + + xtime.mode (int) + + + xtime.mode configuration parameter + + + + Either of on, off or regtest shall + be set. on means its functionality is enabled, and works + as described above. off means its functionality is disabled, + thus it performs as if xtime is not loaded. + regtest performs almost as on doing, but it + does not print time consumption to run regression test correctly. + + + + + + + + Author + + KaiGai Kohei kaigai@ak.jp.nec.com + + + diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 91bea51..2d523f6 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -18,6 +18,7 @@ #include "commands/createas.h" #include "commands/defrem.h" #include "commands/prepare.h" +#include "executor/nodeCustomPlan.h" #include "executor/hashjoin.h" #include "foreign/fdwapi.h" #include "optimizer/clauses.h" @@ -84,6 +85,7 @@ static void show_hash_info(HashState *hashstate, ExplainState *es); static void show_instrumentation_count(const char *qlabel, int which, PlanState *planstate, ExplainState *es); static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es); +static void show_custom_exec_info(CustomPlanState *cestate, ExplainState *es); static const char *explain_get_index_name(Oid indexId); static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir, ExplainState *es); @@ -809,6 +811,7 @@ ExplainNode(PlanState *planstate, List *ancestors, const char *sname; /* node type name for non-text output */ const char *strategy = NULL; const char *operation = NULL; + char namebuf[NAMEDATALEN + 32]; int save_indent = es->indent; bool haschildren; @@ -961,6 +964,12 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_Hash: pname = sname = "Hash"; break; + case T_CustomPlan: + sname = "CustomPlan"; + snprintf(namebuf, sizeof(namebuf), "CustomPlan:%s", + ((CustomPlan *) plan)->cust_name); + pname = namebuf; + break; default: pname = sname = "???"; break; @@ -1121,6 +1130,10 @@ ExplainNode(PlanState *planstate, List *ancestors, ExplainPropertyText("Command", setopcmd, es); } break; + case T_CustomPlan: + if (((Scan *)plan)->scanrelid > 0) + ExplainScanTarget((Scan *) plan, es); + break; default: break; } @@ -1357,6 +1370,13 @@ ExplainNode(PlanState *planstate, List *ancestors, case T_Hash: show_hash_info((HashState *) planstate, es); break; + case T_CustomPlan: + show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); + if (plan->qual) + show_instrumentation_count("Rows Removed by Filter", 1, + planstate, es); + show_custom_exec_info((CustomPlanState *) planstate, es); + break; default: break; } @@ -1477,6 +1497,7 @@ ExplainNode(PlanState *planstate, List *ancestors, IsA(plan, BitmapAnd) || IsA(plan, BitmapOr) || IsA(plan, SubqueryScan) || + (IsA(plan, CustomPlan) && ((CustomPlan *)plan)->cust_subplans) || planstate->subPlan; if (haschildren) { @@ -1531,6 +1552,12 @@ ExplainNode(PlanState *planstate, List *ancestors, ExplainNode(((SubqueryScanState *) planstate)->subplan, ancestors, "Subquery", NULL, es); break; + case T_CustomPlan: + if (((CustomPlan *) plan)->cust_subplans) + ExplainMemberNodes(((CustomPlan *) plan)->cust_subplans, + ((CustomPlanState *) planstate)->cust_subplans, + ancestors, es); + break; default: break; } @@ -1858,6 +1885,18 @@ show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es) } /* + * Show extra information for a CustomPlan node + */ +static void +show_custom_exec_info(CustomPlanState *cestate, ExplainState *es) +{ + CustomPlanRoutine *cust_routine = cestate->cust_routine; + + if (cust_routine->ExplainCustomPlan != NULL) + cust_routine->ExplainCustomPlan(cestate, es); +} + +/* * Fetch the name of an index in an EXPLAIN * * We allow plugins to get control here so that plans involving hypothetical @@ -2025,6 +2064,36 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) objectname = rte->ctename; objecttag = "CTE Name"; break; + case T_CustomPlan: + if (rte->rtekind == RTE_RELATION) + { + objectname = get_rel_name(rte->relid); + if (es->verbose) + namespace = + get_namespace_name(get_rel_namespace(rte->relid)); + objecttag = "Relation Name"; + } + else if (rte->rtekind == RTE_FUNCTION) + { + Node *funcexpr = ((FunctionScan *) plan)->funcexpr; + + if (funcexpr && IsA(funcexpr, FuncExpr)) + { + Oid funcid = ((FuncExpr *) funcexpr)->funcid; + + objectname = get_func_name(funcid); + if (es->verbose) + namespace = + get_namespace_name(get_func_namespace(funcid)); + } + objecttag = "Function Name"; + } + else if (rte->rtekind == RTE_CTE) + { + objectname = rte->ctename; + objecttag = "CTE Name"; + } + break; default: break; } diff --git a/src/backend/executor/Makefile b/src/backend/executor/Makefile index 6081b56..b808bd7 100644 --- a/src/backend/executor/Makefile +++ b/src/backend/executor/Makefile @@ -24,6 +24,7 @@ OBJS = execAmi.o execCurrent.o execGrouping.o execJunk.o execMain.o \ nodeSeqscan.o nodeSetOp.o nodeSort.o nodeUnique.o \ nodeValuesscan.o nodeCtescan.o nodeWorktablescan.o \ nodeGroup.o nodeSubplan.o nodeSubqueryscan.o nodeTidscan.o \ - nodeForeignscan.o nodeWindowAgg.o tstoreReceiver.o spi.o + nodeForeignscan.o nodeWindowAgg.o nodeCustomPlan.o \ + tstoreReceiver.o spi.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c index a078104..f545e01 100644 --- a/src/backend/executor/execAmi.c +++ b/src/backend/executor/execAmi.c @@ -21,6 +21,7 @@ #include "executor/nodeBitmapIndexscan.h" #include "executor/nodeBitmapOr.h" #include "executor/nodeCtescan.h" +#include "executor/nodeCustomPlan.h" #include "executor/nodeForeignscan.h" #include "executor/nodeFunctionscan.h" #include "executor/nodeGroup.h" @@ -249,6 +250,10 @@ ExecReScan(PlanState *node) ExecReScanLimit((LimitState *) node); break; + case T_CustomPlanState: + ExecReScanCustomPlan((CustomPlanState *) node); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); break; @@ -303,6 +308,10 @@ ExecMarkPos(PlanState *node) ExecResultMarkPos((ResultState *) node); break; + case T_CustomPlanState: + ExecMarkPosCustomPlan((CustomPlanState *) node); + break; + default: /* don't make hard error unless caller asks to restore... */ elog(DEBUG2, "unrecognized node type: %d", (int) nodeTag(node)); @@ -360,6 +369,10 @@ ExecRestrPos(PlanState *node) ExecResultRestrPos((ResultState *) node); break; + case T_CustomPlanState: + ExecRestrPosCustomPlan((CustomPlanState *) node); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); break; @@ -475,6 +488,11 @@ ExecSupportsBackwardScan(Plan *node) /* these don't evaluate tlist */ return ExecSupportsBackwardScan(outerPlan(node)); + case T_CustomPlan: + if (CustomPlanSupportBackwardScan((CustomPlan *)node)) + return TargetListSupportsBackwardScan(node->targetlist); + return false; + default: return false; } diff --git a/src/backend/executor/execProcnode.c b/src/backend/executor/execProcnode.c index 76dd62f..f1ab93e 100644 --- a/src/backend/executor/execProcnode.c +++ b/src/backend/executor/execProcnode.c @@ -85,6 +85,7 @@ #include "executor/nodeBitmapIndexscan.h" #include "executor/nodeBitmapOr.h" #include "executor/nodeCtescan.h" +#include "executor/nodeCustomPlan.h" #include "executor/nodeForeignscan.h" #include "executor/nodeFunctionscan.h" #include "executor/nodeGroup.h" @@ -315,6 +316,11 @@ ExecInitNode(Plan *node, EState *estate, int eflags) estate, eflags); break; + case T_CustomPlan: + result = (PlanState *) ExecInitCustomPlan((CustomPlan *) node, + estate, eflags); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); result = NULL; /* keep compiler quiet */ @@ -500,6 +506,10 @@ ExecProcNode(PlanState *node) result = ExecLimit((LimitState *) node); break; + case T_CustomPlanState: + result = ExecCustomPlan((CustomPlanState *) node); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); result = NULL; @@ -558,6 +568,10 @@ MultiExecProcNode(PlanState *node) result = MultiExecBitmapOr((BitmapOrState *) node); break; + case T_CustomPlanState: + result = MultiExecCustomPlan((CustomPlanState *) node); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); result = NULL; @@ -736,6 +750,10 @@ ExecEndNode(PlanState *node) ExecEndLimit((LimitState *) node); break; + case T_CustomPlanState: + ExecEndCustomPlan((CustomPlanState *) node); + break; + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); break; diff --git a/src/backend/executor/nodeCustomPlan.c b/src/backend/executor/nodeCustomPlan.c new file mode 100644 index 0000000..a61622e --- /dev/null +++ b/src/backend/executor/nodeCustomPlan.c @@ -0,0 +1,325 @@ +/* ------------------------------------------------------------------------ + * + * nodeCustomPlan.c + * Routines to handle execution of custom plan node and management + * of its provider. + * + * Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * ------------------------------------------------------------------------ + */ +#include "postgres.h" +#include "executor/nodeCustomPlan.h" +#include "utils/hsearch.h" +#include "utils/memutils.h" +#include "utils/rel.h" + +/* table of registered custom execution providers */ +static HTAB *custom_exec_hash = NULL; + +/* + * RegisterCustomPlan + * + * It registers a set of callbacks with a unique name, as custom plan. + * Then, extensions can use the registered custom plan if it inject custom + * plan node into the plan tree. + * A typical usage of this framework requires extensions two jobs; the first + * one is preliminary registration of custom plan at _PG_init() that shall be + * called when its module is loaded. The second one is manipulation of plan + * tree to add CustomPlan with registered name. + * A set of callbacks shall be associated on the CustomPlanState node later, + * and executor calls the callbacks during its jobs, so it allows extension + * to override a part of executor portion. + * Of course, it is a responsibility of extension to fetch values from the + * underlying plan nodes, and to return appropriate values to the upper node. + */ +void +RegisterCustomPlan(const CustomPlanRoutine *routine) +{ + CustomPlanRoutine *entry; + bool found; + + if (!routine->CustomPlanName) + elog(ERROR, "name of custom plan was not provided."); + + if (!custom_exec_hash) + { + HASHCTL ctl; + + memset(&ctl, 0, sizeof(ctl)); + ctl.hcxt = CacheMemoryContext; + ctl.keysize = NAMEDATALEN; + ctl.entrysize = sizeof(CustomPlanRoutine); + + custom_exec_hash = hash_create("custom plan provider hash", + 128, + &ctl, + HASH_ELEM | HASH_CONTEXT); + } + + entry = hash_search(custom_exec_hash, + routine->CustomPlanName, + HASH_ENTER, &found); + if (found) + elog(ERROR, "custom plan '%s' was already registered", + routine->CustomPlanName); + + Assert(strcmp(routine->CustomPlanName, entry->CustomPlanName) == 0); + memcpy(entry, routine, sizeof(CustomPlanRoutine)); +} + +/* + * get_custom_plan_rouine + * + * It looks up a registered custom plan by the given name. + */ +static CustomPlanRoutine * +get_custom_plan_rouine(const char *cust_name) +{ + CustomPlanRoutine *entry; + + /* lookup custom execution provider */ + if (!custom_exec_hash) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("no custom execution provider was registered"))); + + entry = (CustomPlanRoutine *) hash_search(custom_exec_hash, + cust_name, HASH_FIND, NULL); + if (!entry) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("custom execution provider \"%s\" was not registered", + cust_name))); + + return entry; +} + +/* + * CustomPlanSupportBackwardScan + * + * It checks whether the given custom plan supports backward scan, or not. + * Note that it does not check its capability of nodes in targetlist, even + * though it checks left-/right-tree and subplans. + */ +bool +CustomPlanSupportBackwardScan(CustomPlan *node) +{ + CustomPlanRoutine *entry = get_custom_plan_rouine(node->cust_name); + ListCell *cell; + + if (!entry->IsSupportBackwardScan) + return false; + if (innerPlan(node) && !ExecSupportsBackwardScan(innerPlan(node))) + return false; + if (outerPlan(node) && !ExecSupportsBackwardScan(outerPlan(node))) + return false; + foreach (cell, node->cust_subplans) + { + if (!ExecSupportsBackwardScan((Plan *) lfirst(cell))) + return false; + } + return true; +} + +/* + * ExecInitCustomPlan + * + * It constructs a CustomPlanState node according to the supplied CustomPlan, + * and recursively initializes underlying left-/right-tree and subplans, if + * any. + * It also opens the relation with suitable lock level, if CustomPlan has + * a valid 'scanrelid'. It is a recommendable way to implement a custom plan + * to scan a particular relation instead of built-in Scan nodes; to avoid + * unnecessary projection if its target-list is compatible with definition + * of the target relation. + */ +CustomPlanState * +ExecInitCustomPlan(CustomPlan *node, EState *estate, int eflags) +{ + Plan *plan = &node->scan.plan; + CustomPlanRoutine *entry = get_custom_plan_rouine(node->cust_name); + CustomPlanState *custom; + ListCell *cell; + int index; + + /* + * create state structure + */ + custom = makeNode(CustomPlanState); + custom->ss.ps.plan = (Plan *) node; + custom->ss.ps.state = estate; + if (node->cust_subplans != NIL) + { + custom->cust_numplans = list_length(node->cust_subplans); + custom->cust_subplans = palloc0(sizeof(PlanState *) * + custom->cust_numplans); + } + + /* + * Miscellaneous initialization + * + * create expression context for node + */ + ExecAssignExprContext(estate, &custom->ss.ps); + + custom->ss.ps.ps_TupFromTlist = false; + + /* + * initialize child expressions + */ + custom->ss.ps.targetlist = + (List *) ExecInitExpr((Expr *) plan->targetlist, + (PlanState *) custom); + custom->ss.ps.qual = + (List *) ExecInitExpr((Expr *) plan->qual, + (PlanState *) custom); + + /* tuple table initialization */ + ExecInitResultTupleSlot(estate, &custom->ss.ps); + + /* initialization if custom-exec scan on relation */ + if (node->scan.scanrelid > 0) + { + Relation rel; + + ExecInitScanTupleSlot(estate, &custom->ss); + rel = ExecOpenScanRelation(estate, node->scan.scanrelid, eflags); + + custom->ss.ss_currentRelation = rel; + ExecAssignScanType(&custom->ss, RelationGetDescr(rel)); + } + + /* initialize underlying subplans, if exist */ + outerPlanState(custom) = ExecInitNode(outerPlan(node), estate, eflags); + innerPlanState(custom) = ExecInitNode(innerPlan(node), estate, eflags); + index = 0; + foreach (cell, node->cust_subplans) + { + custom->cust_subplans[index++] + = ExecInitNode((Plan *)lfirst(cell), estate, eflags); + } + + /* + * Initialize result tuple type and projection info. + */ + ExecAssignResultTypeFromTL(&custom->ss.ps); + if (node->scan.scanrelid > 0) + ExecAssignScanProjectionInfo(&custom->ss); + else + ExecAssignProjectionInfo(&custom->ss.ps, NULL); + + /* + * Tell the custom-exec provider to initiate this plan + */ + custom->cust_routine = entry; + custom->cust_state = NULL; + custom->cust_routine->BeginCustomPlan(custom, eflags); + + return custom; +} + +/* + * ExecCustomPlan + * + * It calls back extension to get a tuple being stored in TupleTableSlot. + * NULL means no more tuples can be fetched. + * Also note that extension is responsible to execute underlying plans + * with suitable timing. + */ +TupleTableSlot * +ExecCustomPlan(CustomPlanState *node) +{ + Assert(node->cust_routine->ExecCustomPlan != NULL); + + return node->cust_routine->ExecCustomPlan(node); +} + +/* + * MultiExecCustomPlan + * + * It is a variation of ExecCustomPlan if CustomPlan is connected to some + * node types that expect underlying plan returns multiple tuples according + * to its expectation. + * Please note that HashJoin and Hash are tightly connected, and its protocol + * to return scanned result is a bit ad-hoc. Extension needs to pay attention + * if it tries to replace Hash plan. + */ +Node * +MultiExecCustomPlan(CustomPlanState *node) +{ + Assert(node->cust_routine->MultiExecCustomPlan != NULL); + + return node->cust_routine->MultiExecCustomPlan(node); +} + +/* + * ExecEndCustomPlan + * + * It ends this custom plan. Extension is also called back to release + * resources used for execution. + */ +void +ExecEndCustomPlan(CustomPlanState *node) +{ + int index; + + /* Let the custom-exec shut down */ + node->cust_routine->EndCustomPlan(node); + + /* Free the exprcontext */ + ExecFreeExprContext(&node->ss.ps); + + ExecClearTuple(node->ss.ps.ps_ResultTupleSlot); + if (node->ss.ss_ScanTupleSlot) + ExecClearTuple(node->ss.ss_ScanTupleSlot); + if (node->ss.ss_currentRelation) + ExecCloseScanRelation(node->ss.ss_currentRelation); + + /* End the underlying exec-nodes also */ + ExecEndNode(outerPlanState(node)); + ExecEndNode(innerPlanState(node)); + for (index=0; index < node->cust_numplans; index++) + ExecEndNode(node->cust_subplans[index]); +} + +/* + * ExecReScanCustomPlan + * + * It calls back extension to reset current position of this scan. + */ +void +ExecReScanCustomPlan(CustomPlanState *node) +{ + node->cust_routine->ReScanCustomPlan(node); + + if (node->ss.ss_currentRelation) + ExecScanReScan(&node->ss); +} + +/* + * ExecMarkPosCustomPlan + */ +void +ExecMarkPosCustomPlan(CustomPlanState *node) +{ + if (node->cust_routine->ExecMarkPosCustomPlan) + node->cust_routine->ExecMarkPosCustomPlan(node); + else + elog(DEBUG2, "CustomPlan:%s does not support ExecMarkPos", + node->cust_routine->CustomPlanName); +} + +/* + * ExecRestrPosCustomPlan + */ +void +ExecRestrPosCustomPlan(CustomPlanState *node) +{ + if (node->cust_routine->ExecRestrPosCustomPlan) + node->cust_routine->ExecRestrPosCustomPlan(node); + else + elog(ERROR, "CustomPlan:%s does not support ExecMarkPos", + node->cust_routine->CustomPlanName); +} diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 788907e..cf4e817 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -938,6 +938,29 @@ _copyLimit(const Limit *from) } /* + * _copyCustomPlan + */ +static CustomPlan * +_copyCustomPlan(const CustomPlan *from) +{ + CustomPlan *newnode = makeNode(CustomPlan); + + /* + * copy node superclass fields + */ + CopyScanFields((const Scan *) from, (Scan *) newnode); + + /* + * copy remainder of node + */ + COPY_STRING_FIELD(cust_name); + COPY_NODE_FIELD(cust_private); + COPY_NODE_FIELD(cust_subplans); + + return newnode; +} + +/* * _copyNestLoopParam */ static NestLoopParam * @@ -3970,6 +3993,9 @@ copyObject(const void *from) case T_Limit: retval = _copyLimit(from); break; + case T_CustomPlan: + retval = _copyCustomPlan(from); + break; case T_NestLoopParam: retval = _copyNestLoopParam(from); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index cff4734..c93938e 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -823,6 +823,18 @@ _outLimit(StringInfo str, const Limit *node) } static void +_outCustomPlan(StringInfo str, const CustomPlan *node) +{ + WRITE_NODE_TYPE("CUSTOMPLAN"); + + _outScanInfo(str, (const Scan *) node); + + WRITE_STRING_FIELD(cust_name); + WRITE_NODE_FIELD(cust_private); + WRITE_NODE_FIELD(cust_subplans); +} + +static void _outNestLoopParam(StringInfo str, const NestLoopParam *node) { WRITE_NODE_TYPE("NESTLOOPPARAM"); @@ -2855,6 +2867,9 @@ _outNode(StringInfo str, const void *obj) case T_Limit: _outLimit(str, obj); break; + case T_CustomPlan: + _outCustomPlan(str, obj); + break; case T_NestLoopParam: _outNestLoopParam(str, obj); break; diff --git a/src/include/executor/nodeCustomPlan.h b/src/include/executor/nodeCustomPlan.h new file mode 100644 index 0000000..79fc2ae --- /dev/null +++ b/src/include/executor/nodeCustomPlan.h @@ -0,0 +1,55 @@ +/* ------------------------------------------------------------------------ + * + * nodeCustomPlan.h + * + * prototypes for custom plan nodes + * + * Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * ------------------------------------------------------------------------ + */ +#ifndef NODECUSTOMPLAN_H +#define NODECUSTOMPLAN_H + +#include "commands/explain.h" +#include "nodes/execnodes.h" + +typedef void (*BeginCustomPlan_function)(CustomPlanState *cestate, + int eflags); +typedef TupleTableSlot *(*ExecCustomPlan_function)(CustomPlanState *node); +typedef Node *(*MultiExecCustomPlan_function)(CustomPlanState *node); +typedef void (*ReScanCustomPlan_function)(CustomPlanState *node); +typedef void (*EndCustomPlan_function)(CustomPlanState *node); +typedef void (*ExplainCustomPlan_function)(CustomPlanState *node, + ExplainState *es); +typedef void (*ExecMarkPosCustomPlan_function)(CustomPlanState *node); +typedef void (*ExecRestrPosCustomPlan_function)(CustomPlanState *node); + +typedef struct CustomPlanRoutine +{ + char CustomPlanName[NAMEDATALEN]; + bool IsSupportBackwardScan; + BeginCustomPlan_function BeginCustomPlan; + ExecCustomPlan_function ExecCustomPlan; + MultiExecCustomPlan_function MultiExecCustomPlan; + EndCustomPlan_function EndCustomPlan; + ExplainCustomPlan_function ExplainCustomPlan; + ReScanCustomPlan_function ReScanCustomPlan; + ExecMarkPosCustomPlan_function ExecMarkPosCustomPlan; + ExecRestrPosCustomPlan_function ExecRestrPosCustomPlan; +} CustomPlanRoutine; + +extern void RegisterCustomPlan(const CustomPlanRoutine *routine); +extern bool CustomPlanSupportBackwardScan(CustomPlan *node); + +extern CustomPlanState *ExecInitCustomPlan(CustomPlan *node, + EState *estate, int eflags); +extern TupleTableSlot *ExecCustomPlan(CustomPlanState *node); +extern Node *MultiExecCustomPlan(CustomPlanState *node); +extern void ExecEndCustomPlan(CustomPlanState *node); +extern void ExecReScanCustomPlan(CustomPlanState *node); +extern void ExecMarkPosCustomPlan(CustomPlanState *node); +extern void ExecRestrPosCustomPlan(CustomPlanState *node); + +#endif /* NODECUSTOMPLAN_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 3b430e0..4f557db 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1887,4 +1887,32 @@ typedef struct LimitState TupleTableSlot *subSlot; /* tuple last obtained from subplan */ } LimitState; +/* + * ---------------- + * CustomPlanState information + * + */ +typedef struct +{ + ScanState ss; + + /* + * callback routines of this custom plan provider. Note that, + * we use struct pointer to avoid including nodeCustomPlan.h here. + */ + struct CustomPlanRoutine *cust_routine; + + /* + * provider of custom-executor can keep private state here. + */ + void *cust_state; + + /* + * NULL, or array of PlanStates for inputs, if this custom- + * executor performs like Append. + */ + PlanState **cust_subplans; + int cust_numplans; +} CustomPlanState; + #endif /* EXECNODES_H */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 78368c6..2eb142c 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -76,6 +76,7 @@ typedef enum NodeTag T_SetOp, T_LockRows, T_Limit, + T_CustomPlan, /* these aren't subclasses of Plan: */ T_NestLoopParam, T_PlanRowMark, @@ -121,6 +122,7 @@ typedef enum NodeTag T_SetOpState, T_LockRowsState, T_LimitState, + T_CustomPlanState, /* * TAGS FOR PRIMITIVE NODES (primnodes.h) diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 44ea0b7..2484e54 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -750,6 +750,43 @@ typedef struct Limit Node *limitCount; /* COUNT parameter, or NULL if none */ } Limit; +/* ---------------- + * custom plan node + * + * Note: we assume that extension appends a custom plan or replaces part + * of the given ones with a custom plan on the planner_hook. Custom plan + * provider is identified with its name being registred, then suitable set + * of callbacks shall be choosen prior to its execution. + * ---------------- + */ +typedef struct CustomPlan +{ + /* + * Common field of Plan nodes. Also note that scanrelid can have + * a valid index into the range table if it scans a particular + * table. In this case, PostgreSQL opens the relation with suitable + * lock level prior to invocation of its callback. + * Otherwise, set 0 on scanrelid instead. + */ + Scan scan; + + /* + * Name of the custom plan provider; must be set to identify + * which provider shall run this node. + */ + char *cust_name; + + /* + * Private information to be passed to executor callback + */ + List *cust_private; + + /* + * List of subplans if custom node has multiple number of subplans + * like as Append node has multiple children. + */ + List *cust_subplans; +} CustomPlan; /* * RowMarkType - diff --git a/src/test/regress/GNUmakefile b/src/test/regress/GNUmakefile index d5935b6..d73a675 100644 --- a/src/test/regress/GNUmakefile +++ b/src/test/regress/GNUmakefile @@ -100,7 +100,7 @@ installdirs-tests: installdirs # Get some extra C modules from contrib/spi and contrib/dummy_seclabel... -all: refint$(DLSUFFIX) autoinc$(DLSUFFIX) dummy_seclabel$(DLSUFFIX) +all: refint$(DLSUFFIX) autoinc$(DLSUFFIX) dummy_seclabel$(DLSUFFIX) xtime$(DLSUFFIX) refint$(DLSUFFIX): $(top_builddir)/contrib/spi/refint$(DLSUFFIX) cp $< $@ @@ -111,19 +111,27 @@ autoinc$(DLSUFFIX): $(top_builddir)/contrib/spi/autoinc$(DLSUFFIX) dummy_seclabel$(DLSUFFIX): $(top_builddir)/contrib/dummy_seclabel/dummy_seclabel$(DLSUFFIX) cp $< $@ +xtime$(DLSUFFIX): $(top_builddir)/contrib/xtime/xtime$(DLSUFFIX) + cp $< $@ + $(top_builddir)/contrib/spi/refint$(DLSUFFIX): | submake-contrib-spi ; $(top_builddir)/contrib/spi/autoinc$(DLSUFFIX): | submake-contrib-spi ; $(top_builddir)/contrib/dummy_seclabel/dummy_seclabel$(DLSUFFIX): | submake-contrib-dummy_seclabel ; +$(top_builddir)/contrib/xtime/xtime$(DLSUFFIX): | submake-contrib-xtime ; + submake-contrib-spi: $(MAKE) -C $(top_builddir)/contrib/spi submake-contrib-dummy_seclabel: $(MAKE) -C $(top_builddir)/contrib/dummy_seclabel -.PHONY: submake-contrib-spi submake-contrib-dummy_seclabel +submake-contrib-xtime: + $(MAKE) -C $(top_builddir)/contrib/xtime + +.PHONY: submake-contrib-spi submake-contrib-dummy_seclabel submake-contrib-xtime # Tablespace setup @@ -170,7 +178,7 @@ bigcheck: all tablespace-setup clean distclean maintainer-clean: clean-lib # things built by `all' target - rm -f $(OBJS) refint$(DLSUFFIX) autoinc$(DLSUFFIX) dummy_seclabel$(DLSUFFIX) + rm -f $(OBJS) refint$(DLSUFFIX) autoinc$(DLSUFFIX) dummy_seclabel$(DLSUFFIX) xtime$(DLSUFFIX) rm -f pg_regress_main.o pg_regress.o pg_regress$(X) # things created by various check targets rm -f $(output_files) $(input_files) diff --git a/src/test/regress/input/custom_exec.source b/src/test/regress/input/custom_exec.source new file mode 100644 index 0000000..054fb1e --- /dev/null +++ b/src/test/regress/input/custom_exec.source @@ -0,0 +1,184 @@ +-- +-- Test for custom executor nodes +-- + +-- Clean up in case a prior regression run failed +SET client_min_messages TO 'warning'; + +DROP SCHEMA IF EXISTS custom_exec_test CASCADE; + +RESET client_min_messages; + +-- Setting up tables for tests +CREATE SCHEMA custom_exec_test; +SET search_path TO custom_exec_test, pg_temp, public; + +CREATE TABLE t1 ( + a int primary key, + b text +); +INSERT INTO t1 (SELECT v, md5(v::text) FROM generate_series(1,10000) AS v); +VACUUM ANALYZE t1; + +CREATE TABLE t2 ( + x int references t1(a), + y text, + z float +); +INSERT INTO t2 (SELECT v, md5(v::text), (v::text || '.' || v::text)::float FROM generate_series(1,10000,2) AS v); +CREATE INDEX t2_x_idx ON t2(x); +VACUUM ANALYZE t2; + +CREATE TABLE s1 ( + a int, + b text +); +INSERT INTO s1 (SELECT v, md5((v+10)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+20)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+30)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+40)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+50)::text) FROM generate_series(1,2000) AS v); +CREATE INDEX s1_a_idx ON s1(a); +VACUUM ANALYZE s1; + +CREATE TABLE u1 ( + x int, + y text, + z float +); +CREATE INDEX u1_x_idx ON u1(x); +INSERT INTO u1 (SELECT v, md5(v::text) FROM generate_series(1,5000) AS v); +VACUUM ANALYZE u1; + +CREATE TABLE u2 () INHERITS (u1); +INSERT INTO u2 (SELECT v, md5((v+5000)::text) FROM generate_series(1,5000) AS v); +CREATE UNIQUE INDEX u2_x_idx ON u2(x); +VACUUM ANALYZE u2; + +CREATE TABLE u3 () INHERITS (u1); +INSERT INTO u3 (SELECT v, md5((v+10000)::text) FROM generate_series(1,5000) AS v); +VACUUM ANALYZE u3; + +-- Load example custom execution provider +LOAD '@libdir@/xtime@DLSUFFIX@'; +SET xtime.regression_test = on; + +-- test for Hash Join +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; +SELECT * INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; + +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; +SELECT * INTO TEMP with_cust FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for SeqScan replace +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a > 1000; +SELECT * INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE a > 1000; + +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a > 1000; +SELECT * INTO TEMP with_cust FROM t1 JOIN t2 ON a = x WHERE a > 1000; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for target list with projection +SET xtime.mode = off; +EXPLAIN(costs off, verbose) SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; +SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; + +SET xtime.mode = regtest; +EXPLAIN(costs off, verbose) SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP q1 FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; +SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP with_cust FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for Bitmap Scan +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a between 100 and 200; +SELECT * INTO TEMP no_cust FROM s1 WHERE a between 100 and 200; + +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a between 100 and 200; +SELECT * INTO TEMP with_cust FROM s1 WHERE a between 100 and 200; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for Sort/Limit +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; +SELECT * INTO TEMP no_cust FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; + +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; +SELECT * INTO TEMP with_cust FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for complicated query +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); +SELECT * INTO TEMP no_cust FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); + +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); +SELECT * INTO TEMP with_cust FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for inherited tables +SET xtime.mode = off; +EXPLAIN(costs off, verbose) SELECT x+z AS c1, y||y AS c2 FROM u1 WHERE x BETWEEN 1111 AND 4444; +SELECT x+z AS c1, y||y AS c2 INTO no_cust FROM u1 WHERE x BETWEEN 1111 AND 4444; + +SET xtime.mode = regtest; +EXPLAIN(costs off, verbose) SELECT x+z AS c1, y||y AS c2 FROM u1 WHERE x BETWEEN 1111 AND 4444; +SELECT x+z AS c1, y||y AS c2 INTO with_cust FROM u1 WHERE x BETWEEN 1111 AND 4444; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + + +-- test for inherited tables with index scan +SET xtime.mode = off; +EXPLAIN(costs off, verbose) SELECT x-z AS c1, y||y AS c2 FROM u1 WHERE x = 3333; +SELECT x-z AS c1, y||y AS c2 INTO no_cust FROM u1 WHERE x = 3333; + +SET xtime.mode = regtest; +EXPLAIN(costs off, verbose) SELECT x-z AS c1, y||y AS c2 FROM u1 WHERE x = 3333; +SELECT x-z AS c1, y||y AS c2 INTO with_cust FROM u1 WHERE x = 3333; +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +DROP TABLE IF EXISTS no_cust, with_cust; + +-- test for invalidation of prepared statement +SET xtime.mode = off; +PREPARE p1(int,int) AS SELECT * FROM u1 WHERE x BETWEEN $1 AND $2; +EXPLAIN EXECUTE p1(100,200); + +SET xtime.mode = on; +PREPARE p2(int) AS SELECT * FROM u1 WHERE x = $1; +EXPLAIN EXECUTE p1(100,200); +EXPLAIN EXECUTE p2(2222); + +SET xtime.mode = off; +EXPLAIN EXECUTE p1(100,200); +EXPLAIN EXECUTE p2(2222); + +-- Clean up resources +DROP SCHEMA IF EXISTS custom_exec_test CASCADE; diff --git a/src/test/regress/output/custom_exec.source b/src/test/regress/output/custom_exec.source new file mode 100644 index 0000000..146e468 --- /dev/null +++ b/src/test/regress/output/custom_exec.source @@ -0,0 +1,541 @@ +-- +-- Test for custom executor nodes +-- +-- Clean up in case a prior regression run failed +SET client_min_messages TO 'warning'; +DROP SCHEMA IF EXISTS custom_exec_test CASCADE; +RESET client_min_messages; +-- Setting up tables for tests +CREATE SCHEMA custom_exec_test; +SET search_path TO custom_exec_test, pg_temp, public; +CREATE TABLE t1 ( + a int primary key, + b text +); +INSERT INTO t1 (SELECT v, md5(v::text) FROM generate_series(1,10000) AS v); +VACUUM ANALYZE t1; +CREATE TABLE t2 ( + x int references t1(a), + y text, + z float +); +INSERT INTO t2 (SELECT v, md5(v::text), (v::text || '.' || v::text)::float FROM generate_series(1,10000,2) AS v); +CREATE INDEX t2_x_idx ON t2(x); +VACUUM ANALYZE t2; +CREATE TABLE s1 ( + a int, + b text +); +INSERT INTO s1 (SELECT v, md5((v+10)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+20)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+30)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+40)::text) FROM generate_series(1,2000) AS v); +INSERT INTO s1 (SELECT v, md5((v+50)::text) FROM generate_series(1,2000) AS v); +CREATE INDEX s1_a_idx ON s1(a); +VACUUM ANALYZE s1; +CREATE TABLE u1 ( + x int, + y text, + z float +); +CREATE INDEX u1_x_idx ON u1(x); +INSERT INTO u1 (SELECT v, md5(v::text) FROM generate_series(1,5000) AS v); +VACUUM ANALYZE u1; +CREATE TABLE u2 () INHERITS (u1); +INSERT INTO u2 (SELECT v, md5((v+5000)::text) FROM generate_series(1,5000) AS v); +CREATE UNIQUE INDEX u2_x_idx ON u2(x); +VACUUM ANALYZE u2; +CREATE TABLE u3 () INHERITS (u1); +INSERT INTO u3 (SELECT v, md5((v+10000)::text) FROM generate_series(1,5000) AS v); +VACUUM ANALYZE u3; +-- Load example custom execution provider +LOAD '@libdir@/xtime@DLSUFFIX@'; +SET xtime.regression_test = on; +-- test for Hash Join +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; + QUERY PLAN +------------------------------------------------------- + Hash Join + Hash Cond: (t2.x = t1.a) + -> Seq Scan on t2 + -> Hash + -> Index Scan using t1_pkey on t1 + Index Cond: ((a >= 100) AND (a <= 200)) +(6 rows) + +SELECT * INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; + QUERY PLAN +------------------------------------------------------------------- + CustomPlan:xtime + -> Hash Join + Hash Cond: (x = t1.a) + -> CustomPlan:xtime on t2 + -> Hash + -> CustomPlan:xtime + -> Index Scan using t1_pkey on t1 + Index Cond: ((a >= 100) AND (a <= 200)) +(8 rows) + +SELECT * INTO TEMP with_cust FROM t1 JOIN t2 ON a = x WHERE a BETWEEN 100 AND 200; +INFO: execution time of Hash Join: **.*** ms +INFO: execution time of CustomPlan:xtime on t2: **.*** ms +INFO: execution time of Index Scan on t1: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + a | b | x | y | z +---+---+---+---+--- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for SeqScan replace +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a > 1000; + QUERY PLAN +---------------------------- + Hash Join + Hash Cond: (t1.a = t2.x) + -> Seq Scan on t1 + Filter: (a > 1000) + -> Hash + -> Seq Scan on t2 +(6 rows) + +SELECT * INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE a > 1000; +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x WHERE a > 1000; + QUERY PLAN +------------------------------------------ + CustomPlan:xtime + -> Hash Join + Hash Cond: (t1.a = t2.x) + -> CustomPlan:xtime on t1 + Filter: (a > 1000) + -> Hash + -> CustomPlan:xtime on t2 +(7 rows) + +SELECT * INTO TEMP with_cust FROM t1 JOIN t2 ON a = x WHERE a > 1000; +INFO: execution time of Hash Join: **.*** ms +INFO: execution time of CustomPlan:xtime on t1: **.*** ms +INFO: execution time of CustomPlan:xtime on t2: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + a | b | x | y | z +---+---+---+---+--- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for target list with projection +SET xtime.mode = off; +EXPLAIN(costs off, verbose) SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; + QUERY PLAN +----------------------------------------------------------------------- + Nested Loop + Output: ((t1.a * 10000) + t2.x), ((t1.b || '_'::text) || 'y'::text) + -> Seq Scan on custom_exec_test.t2 + Output: t2.x, t2.y, t2.z + Filter: ((t2.x % 2) = 0) + -> Index Scan using t1_pkey on custom_exec_test.t1 + Output: t1.a, t1.b + Index Cond: (t1.a = t2.x) +(8 rows) + +SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP no_cust FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; +SET xtime.mode = regtest; +EXPLAIN(costs off, verbose) SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP q1 FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; + QUERY PLAN +-------------------------------------------------------------------------- + CustomPlan:xtime + Output: (((t1.a * 10000) + x)), (((t1.b || '_'::text) || 'y'::text)) + -> Nested Loop + Output: ((t1.a * 10000) + x), ((t1.b || '_'::text) || 'y'::text) + -> CustomPlan:xtime on custom_exec_test.t2 + Output: x, y, z + Filter: ((x % 2) = 0) + -> CustomPlan:xtime + Output: t1.a, t1.b + -> Index Scan using t1_pkey on custom_exec_test.t1 + Output: t1.a, t1.b + Index Cond: (t1.a = x) +(12 rows) + +SELECT a * 10000 + x AS c1, b || '_' || 'y' AS c2 + INTO TEMP with_cust FROM t1 JOIN t2 ON a = x WHERE x % 2 = 0; +INFO: execution time of Nested Loop: **.*** ms +INFO: execution time of CustomPlan:xtime on t2: **.*** ms +INFO: execution time of Index Scan on t1: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + c1 | c2 +----+---- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for Bitmap Scan +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a between 100 and 200; + QUERY PLAN +------------------------------------------------- + Bitmap Heap Scan on s1 + Recheck Cond: ((a >= 100) AND (a <= 200)) + -> Bitmap Index Scan on s1_a_idx + Index Cond: ((a >= 100) AND (a <= 200)) +(4 rows) + +SELECT * INTO TEMP no_cust FROM s1 WHERE a between 100 and 200; +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a between 100 and 200; + QUERY PLAN +------------------------------------------------------------- + CustomPlan:xtime + -> Bitmap Heap Scan on s1 + Recheck Cond: ((a >= 100) AND (a <= 200)) + -> CustomPlan:xtime + -> Bitmap Index Scan on s1_a_idx + Index Cond: ((a >= 100) AND (a <= 200)) +(6 rows) + +SELECT * INTO TEMP with_cust FROM s1 WHERE a between 100 and 200; +INFO: execution time of Bitmap Heap Scan on s1: **.*** ms +INFO: execution time of Bitmap Index Scan on s1: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + a | b +---+--- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for Sort/Limit +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; + QUERY PLAN +--------------------------------------------- + Limit + -> Sort + Sort Key: b + -> Index Scan using s1_a_idx on s1 + Index Cond: (a > 9000) +(5 rows) + +SELECT * INTO TEMP no_cust FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; + QUERY PLAN +--------------------------------------------------------------- + CustomPlan:xtime + -> Limit + -> CustomPlan:xtime + -> Sort + Sort Key: b + -> CustomPlan:xtime + -> Index Scan using s1_a_idx on s1 + Index Cond: (a > 9000) +(8 rows) + +SELECT * INTO TEMP with_cust FROM s1 WHERE a > 9000 ORDER BY b LIMIT 20; +INFO: execution time of Limit: **.*** ms +INFO: execution time of Sort: **.*** ms +INFO: execution time of Index Scan on s1: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + a | b +---+--- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for complicated query +SET xtime.mode = off; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); + QUERY PLAN +-------------------------------------------------------------------- + Nested Loop + -> Merge Join + Merge Cond: (t2.x = s1.a) + -> Index Scan using t2_x_idx on t2 + -> Sort + Sort Key: s1.a + -> HashAggregate + -> Limit + -> Sort + Sort Key: s1.b + -> Seq Scan on s1 + Filter: (b ~~ '%abc%'::text) + -> Index Scan using t1_pkey on t1 + Index Cond: (a = t2.x) +(14 rows) + +SELECT * INTO TEMP no_cust FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); +SET xtime.mode = regtest; +EXPLAIN(costs off) SELECT * FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + CustomPlan:xtime + -> Nested Loop + -> CustomPlan:xtime + -> Merge Join + Merge Cond: (t2.x = a) + -> CustomPlan:xtime + -> Index Scan using t2_x_idx on t2 + -> CustomPlan:xtime + -> Sort + Sort Key: a + -> CustomPlan:xtime + -> HashAggregate + -> CustomPlan:xtime + -> Limit + -> CustomPlan:xtime + -> Sort + Sort Key: b + -> CustomPlan:xtime on s1 + Filter: (b ~~ '%abc%'::text) + -> CustomPlan:xtime + -> Index Scan using t1_pkey on t1 + Index Cond: (a = t2.x) +(22 rows) + +SELECT * INTO TEMP with_cust FROM t1 JOIN t2 ON a = x + WHERE a IN (SELECT a FROM s1 WHERE b like '%abc%' ORDER BY b LIMIT 100); +INFO: execution time of Nested Loop: **.*** ms +INFO: execution time of Merge Join: **.*** ms +INFO: execution time of Sort: **.*** ms +INFO: execution time of HashAggregate (Hashed): **.*** ms +INFO: execution time of Limit: **.*** ms +INFO: execution time of Sort: **.*** ms +INFO: execution time of CustomPlan:xtime on s1: **.*** ms +INFO: execution time of Index Scan on t2: **.*** ms +INFO: execution time of Index Scan on t1: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + a | b | x | y | z +---+---+---+---+--- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for inherited tables +SET xtime.mode = off; +EXPLAIN(costs off, verbose) SELECT x+z AS c1, y||y AS c2 FROM u1 WHERE x BETWEEN 1111 AND 4444; + QUERY PLAN +------------------------------------------------------------- + Result + Output: ((u1.x)::double precision + u1.z), (u1.y || u1.y) + -> Append + -> Seq Scan on custom_exec_test.u1 + Output: u1.x, u1.z, u1.y + Filter: ((u1.x >= 1111) AND (u1.x <= 4444)) + -> Seq Scan on custom_exec_test.u2 + Output: u2.x, u2.z, u2.y + Filter: ((u2.x >= 1111) AND (u2.x <= 4444)) + -> Seq Scan on custom_exec_test.u3 + Output: u3.x, u3.z, u3.y + Filter: ((u3.x >= 1111) AND (u3.x <= 4444)) +(12 rows) + +SELECT x+z AS c1, y||y AS c2 INTO no_cust FROM u1 WHERE x BETWEEN 1111 AND 4444; +SET xtime.mode = regtest; +EXPLAIN(costs off, verbose) SELECT x+z AS c1, y||y AS c2 FROM u1 WHERE x BETWEEN 1111 AND 4444; + QUERY PLAN +--------------------------------------------------------------------------- + CustomPlan:xtime + Output: (((u1_1.x)::double precision + u1_1.z)), ((u1_1.y || u1_1.y)) + -> Result + Output: ((u1_1.x)::double precision + u1_1.z), (u1_1.y || u1_1.y) + -> CustomPlan:xtime + Output: u1_1.x, u1_1.z, u1_1.y + -> Append + -> CustomPlan:xtime on custom_exec_test.u1 u1_1 + Output: u1_1.x, u1_1.z, u1_1.y + Filter: ((u1_1.x >= 1111) AND (u1_1.x <= 4444)) + -> CustomPlan:xtime on custom_exec_test.u2 + Output: u2.x, u2.z, u2.y + Filter: ((u2.x >= 1111) AND (u2.x <= 4444)) + -> CustomPlan:xtime on custom_exec_test.u3 + Output: u3.x, u3.z, u3.y + Filter: ((u3.x >= 1111) AND (u3.x <= 4444)) +(16 rows) + +SELECT x+z AS c1, y||y AS c2 INTO with_cust FROM u1 WHERE x BETWEEN 1111 AND 4444; +INFO: execution time of Result: **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of CustomPlan:xtime on u1: **.*** ms +INFO: execution time of CustomPlan:xtime on u2: **.*** ms +INFO: execution time of CustomPlan:xtime on u3: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + c1 | c2 +----+---- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for inherited tables with index scan +SET xtime.mode = off; +EXPLAIN(costs off, verbose) SELECT x-z AS c1, y||y AS c2 FROM u1 WHERE x = 3333; + QUERY PLAN +-------------------------------------------------------------- + Result + Output: ((u1.x)::double precision - u1.z), (u1.y || u1.y) + -> Append + -> Index Scan using u1_x_idx on custom_exec_test.u1 + Output: u1.x, u1.z, u1.y + Index Cond: (u1.x = 3333) + -> Index Scan using u2_x_idx on custom_exec_test.u2 + Output: u2.x, u2.z, u2.y + Index Cond: (u2.x = 3333) + -> Seq Scan on custom_exec_test.u3 + Output: u3.x, u3.z, u3.y + Filter: (u3.x = 3333) +(12 rows) + +SELECT x-z AS c1, y||y AS c2 INTO no_cust FROM u1 WHERE x = 3333; +SET xtime.mode = regtest; +EXPLAIN(costs off, verbose) SELECT x-z AS c1, y||y AS c2 FROM u1 WHERE x = 3333; + QUERY PLAN +-------------------------------------------------------------------------------- + CustomPlan:xtime + Output: (((u1.x)::double precision - u1.z)), ((u1.y || u1.y)) + -> Result + Output: ((u1.x)::double precision - u1.z), (u1.y || u1.y) + -> CustomPlan:xtime + Output: u1.x, u1.z, u1.y + -> Append + -> CustomPlan:xtime + Output: u1.x, u1.z, u1.y + -> Index Scan using u1_x_idx on custom_exec_test.u1 + Output: u1.x, u1.z, u1.y + Index Cond: (u1.x = 3333) + -> CustomPlan:xtime + Output: u2.x, u2.z, u2.y + -> Index Scan using u2_x_idx on custom_exec_test.u2 + Output: u2.x, u2.z, u2.y + Index Cond: (u2.x = 3333) + -> CustomPlan:xtime on custom_exec_test.u3 u1 + Output: x, z, y + Filter: (x = 3333) +(20 rows) + +SELECT x-z AS c1, y||y AS c2 INTO with_cust FROM u1 WHERE x = 3333; +INFO: execution time of Result: **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Index Scan on u1: **.*** ms +INFO: execution time of Index Scan on u1: **.*** ms +INFO: execution time of CustomPlan:xtime on u3: **.*** ms +SELECT * FROM no_cust EXCEPT SELECT * FROM with_cust; -- should be empty +INFO: execution time of HashSetOp (Hashed): **.*** ms +INFO: execution time of Append: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 1: **.*** ms +INFO: execution time of Subquery Scan on *SELECT* 2: **.*** ms + c1 | c2 +----+---- +(0 rows) + +DROP TABLE IF EXISTS no_cust, with_cust; +-- test for invalidation of prepared statement +SET xtime.mode = off; +PREPARE p1(int,int) AS SELECT * FROM u1 WHERE x BETWEEN $1 AND $2; +EXPLAIN EXECUTE p1(100,200); + QUERY PLAN +----------------------------------------------------------------------------- + Append (cost=0.28..137.56 rows=300 width=45) + -> Index Scan using u1_x_idx on u1 (cost=0.28..10.28 rows=100 width=45) + Index Cond: ((x >= 100) AND (x <= 200)) + -> Index Scan using u2_x_idx on u2 (cost=0.28..10.28 rows=100 width=45) + Index Cond: ((x >= 100) AND (x <= 200)) + -> Seq Scan on u3 (cost=0.00..117.00 rows=100 width=45) + Filter: ((x >= 100) AND (x <= 200)) +(7 rows) + +SET xtime.mode = on; +PREPARE p2(int) AS SELECT * FROM u1 WHERE x = $1; +EXPLAIN EXECUTE p1(100,200); + QUERY PLAN +----------------------------------------------------------------------------------------- + CustomPlan:xtime (cost=0.28..137.56 rows=300 width=45) + -> Append (cost=0.28..137.56 rows=300 width=45) + -> CustomPlan:xtime (cost=0.28..10.28 rows=100 width=45) + -> Index Scan using u1_x_idx on u1 (cost=0.28..10.28 rows=100 width=45) + Index Cond: ((x >= 100) AND (x <= 200)) + -> CustomPlan:xtime (cost=0.28..10.28 rows=100 width=45) + -> Index Scan using u2_x_idx on u2 (cost=0.28..10.28 rows=100 width=45) + Index Cond: ((x >= 100) AND (x <= 200)) + -> CustomPlan:xtime on u3 u1 (cost=0.00..117.00 rows=100 width=45) + Filter: ((x >= 100) AND (x <= 200)) +(10 rows) + +EXPLAIN EXECUTE p2(2222); + QUERY PLAN +-------------------------------------------------------------------------------------- + CustomPlan:xtime (cost=0.28..121.10 rows=3 width=45) + -> Append (cost=0.28..121.10 rows=3 width=45) + -> CustomPlan:xtime (cost=0.28..8.30 rows=1 width=45) + -> Index Scan using u1_x_idx on u1 (cost=0.28..8.30 rows=1 width=45) + Index Cond: (x = 2222) + -> CustomPlan:xtime (cost=0.28..8.30 rows=1 width=45) + -> Index Scan using u2_x_idx on u2 (cost=0.28..8.30 rows=1 width=45) + Index Cond: (x = 2222) + -> CustomPlan:xtime on u3 u1 (cost=0.00..104.50 rows=1 width=45) + Filter: (x = 2222) +(10 rows) + +SET xtime.mode = off; +EXPLAIN EXECUTE p1(100,200); + QUERY PLAN +----------------------------------------------------------------------------- + Append (cost=0.28..137.56 rows=300 width=45) + -> Index Scan using u1_x_idx on u1 (cost=0.28..10.28 rows=100 width=45) + Index Cond: ((x >= 100) AND (x <= 200)) + -> Index Scan using u2_x_idx on u2 (cost=0.28..10.28 rows=100 width=45) + Index Cond: ((x >= 100) AND (x <= 200)) + -> Seq Scan on u3 (cost=0.00..117.00 rows=100 width=45) + Filter: ((x >= 100) AND (x <= 200)) +(7 rows) + +EXPLAIN EXECUTE p2(2222); + QUERY PLAN +-------------------------------------------------------------------------- + Append (cost=0.28..121.10 rows=3 width=45) + -> Index Scan using u1_x_idx on u1 (cost=0.28..8.30 rows=1 width=45) + Index Cond: (x = 2222) + -> Index Scan using u2_x_idx on u2 (cost=0.28..8.30 rows=1 width=45) + Index Cond: (x = 2222) + -> Seq Scan on u3 (cost=0.00..104.50 rows=1 width=45) + Filter: (x = 2222) +(7 rows) + +-- Clean up resources +DROP SCHEMA IF EXISTS custom_exec_test CASCADE; +NOTICE: drop cascades to 6 other objects +DETAIL: drop cascades to table t1 +drop cascades to table t2 +drop cascades to table s1 +drop cascades to table u1 +drop cascades to table u2 +drop cascades to table u3 diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index fd08e8d..dddc444 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -98,7 +98,7 @@ test: event_trigger # ---------- # Another group of parallel tests # ---------- -test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json indirect_toast +test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap functional_deps advisory_lock json indirect_toast custom_exec # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 1ed059b..864928e 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -120,6 +120,7 @@ test: functional_deps test: advisory_lock test: json test: indirect_toast +test: custom_exec test: plancache test: limit test: plpgsql