健哥的数据花园

摘要： 看到了其源代码中的一段注释，似乎认识又提高了一层：/* * INTERFACE ROUTINES * ExecInitNode - initialize a plan node and its subplans * ExecProcNode - get a tuple by executing the plan node * ExecEndNode - shut down a plan node and its subplans * * NOTES * T... 阅读全文

摘要： PostgreSQL中，有一种 tidscan。当我在使用cursor的时候，会用到此种scan：[postgres@lex pgsql]$ cat ./data/test.sqlCREATE OR REPLACE Function FindCourse ( name_in IN varchar ) RETURNS integer LANGUAGE plpgsql AS $$DECLARE cnumber integer; c1 CURSOR FOR SELECT course_number, instructor from course_t... 阅读全文

摘要： postgres=# select FindCourse('aaaa');ERROR: cannot begin/end transactions in PL/pgSQLHINT: Use a BEGIN block with an EXCEPTION clause instead.CONTEXT: PL/pgSQL function findcourse(character varying) line 21 at SQL statement出现这种错误的原因是：看我改后的代码：[postgres@lex pgsql]$ cat ./data/test.sqlCREATE OR 阅读全文

摘要： 接着追踪->pathlist：初始化可能是在这里完成的？/* * add_path * Consider a potential implementation path for the specified parent rel, * and add it to the rel's pathlist if it is worthy of consideration. * A path is worthy if it has a better sort order (better pathkeys) or * cheaper cost (on either ... 阅读全文

摘要： 接前面，继续分析 : cheapest_startup_path = cheapest_total_path = NULL; have_parameterized_paths = false; foreach(p, parent_rel->pathlist) { Path *path = (Path *) lfirst(p); int cmp; /* We only consider unparameterized paths in this step */ if (path->p... 阅读全文

摘要： 接前面。从 cheapest_total_path 来看：query_planner 会先调用 build_simple_rel，这里完成了 cheapest_total_path 的初始化。(query_planner --> add_base_rels_to_query --> query_planner)然后，query_planner 再在后面调用 create_plan，最终设置 plan_rows。那么，是在何处 设置了 cheapest_total_path 的值呢？经过查找，看到了如下的调用关系：make_one_rel--> set_base_rel_pat 阅读全文

摘要： 接前面，从 cheapeast_path 的角度，关注 query_planner 函数，对其进行简化：voidquery_planner(PlannerInfo *root, List *tlist, double tuple_fraction, double limit_tuples, Path **cheapest_path, Path **sorted_path, double *num_groups){ Query *parse = root->parse; List *... 阅读全文

摘要： 接前面。从PostgreSQL的 log中，看到 计划树中有一个 plan_rows的东西。分析它的来源和来龙去脉：grouping_planner --> create_plan --> create_plan_recurse --> create_scan_plan--> create_seqscan_plan --> copy_path_costsize而copy_path_costsize 中：/* * Copy cost and size info from a Path node to the Plan node created from it. * 阅读全文

摘要： 为了少走弯路，有必要了解如下概念：http://www.postgresql.org/docs/9.2/static/querytree.htmlThe rule system is located between the parser and the planner. It takes the output of the parser, one query tree, and the user-defined rewrite rules, which are also query trees with some extra information, and creates zero or m 阅读全文

摘要： 在看PostgreSQL的源代码的时候，总是看到 CTE。所谓CTE，就是 common table express。这里有一个小例子：WITH test(x) AS (SELECT 1 UNION SELECT 2)SELECT * FROM test;这个是官方说明：http://www.postgresql.org/docs/9.2/static/queries-with.htmlWITH provides a way to write auxiliary statements for use in a larger query. These statements, which are. 阅读全文