PostgreSQL在何处处理 sql查询之五十二
开始
/* * Ready to do the primary planning. */ final_rel = make_one_rel(root, joinlist);
展开：
/* * make_one_rel * Finds all possible access paths for executing a query, returning a * single rel that represents the join of all base rels in the query. */ RelOptInfo * make_one_rel(PlannerInfo *root, List *joinlist) { RelOptInfo *rel; Index rti; /* * Construct the all_baserels Relids set. */ root->all_baserels = NULL; for (rti = 1; rti < root->simple_rel_array_size; rti++) { RelOptInfo *brel = root->simple_rel_array[rti]; /* there may be empty slots corresponding to non-baserel RTEs */ if (brel == NULL) continue; Assert(brel->relid == rti); /* sanity check on array */ /* ignore RTEs that are "other rels" */ if (brel->reloptkind != RELOPT_BASEREL) continue; root->all_baserels = bms_add_member(root->all_baserels, brel->relid); } /* * Generate access paths for the base rels. */ set_base_rel_sizes(root); set_base_rel_pathlists(root); /* * Generate access paths for the entire join tree. */ rel = make_rel_from_joinlist(root, joinlist); /* * The result should join all and only the query's base rels. */ Assert(bms_equal(rel->relids, root->all_baserels)); return rel; }
其中，
root->all_baserels = bms_add_member(root->all_baserels, brel->relid);
这个展开后可以看到，因为 root->all_baserels 是NULL，所以什么也没执行。
/* * bms_add_member - add a specified member to set * * Input set is modified or recycled! */ Bitmapset * bms_add_member(Bitmapset *a, int x) { int wordnum, bitnum; if (x < 0) elog(ERROR, "negative bitmapset member not allowed"); if (a == NULL) return bms_make_singleton(x); wordnum = WORDNUM(x); bitnum = BITNUM(x);if (wordnum >= a->nwords) { /* Slow path: make a larger set and union the input set into it */ Bitmapset *result; int nwords; int i; result = bms_make_singleton(x); nwords = a->nwords; for (i = 0; i < nwords; i++) result->words[i] |= a->words[i]; pfree(a); return result; } /* Fast path: x fits in existing set */ a->words[wordnum] |= ((bitmapword) 1 << bitnum); return a; }
接着分析下一个：
set_base_rel_sizes(root);
/* * set_base_rel_sizes * Set the size estimates (rows and widths) for each base-relation entry. * * We do this in a separate pass over the base rels so that rowcount * estimates are available for parameterized path generation. */ static void set_base_rel_sizes(PlannerInfo *root) { Index rti; for (rti = 1; rti < root->simple_rel_array_size; rti++) { RelOptInfo *rel = root->simple_rel_array[rti]; /* there may be empty slots corresponding to non-baserel RTEs */ if (rel == NULL) continue; Assert(rel->relid == rti); /* sanity check on array */ /* ignore RTEs that are "other rels" */ if (rel->reloptkind != RELOPT_BASEREL) continue; set_rel_size(root, rel, rti, root->simple_rte_array[rti]); } }
这是成本评估的非常重要的依据。
再展开 set_rel_size 函数：
/* * set_rel_size * Set size estimates for a base relation */ static void set_rel_size(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte) { if (rel->reloptkind == RELOPT_BASEREL && relation_excluded_by_constraints(root, rel, rte)) { /* * We proved we don't need to scan the rel via constraint exclusion, * so set up a single dummy path for it. Here we only check this for * regular baserels; if it's an otherrel, CE was already checked in * set_append_rel_pathlist(). * * In this case, we go ahead and set up the relation's path right away * instead of leaving it for set_rel_pathlist to do. This is because * we don't have a convention for marking a rel as dummy except by * assigning a dummy path to it. */ set_dummy_rel_pathlist(rel); } else if (rte->inh) { /* It's an "append relation", process accordingly */ set_append_rel_size(root, rel, rti, rte); } else { switch (rel->rtekind) { case RTE_RELATION: if (rte->relkind == RELKIND_FOREIGN_TABLE) { /* Foreign table */ set_foreign_size(root, rel, rte); } else { /* Plain relation */ set_plain_rel_size(root, rel, rte); } break; case RTE_SUBQUERY: /* * Subqueries don't support parameterized paths, so just go * ahead and build their paths immediately. */ set_subquery_pathlist(root, rel, rti, rte); break; case RTE_FUNCTION: set_function_size_estimates(root, rel); break; case RTE_VALUES: set_values_size_estimates(root, rel); break; case RTE_CTE: /* * CTEs don't support parameterized paths, so just go ahead * and build their paths immediately. */ if (rte->self_reference) set_worktable_pathlist(root, rel, rte); else set_cte_pathlist(root, rel, rte); break; default: elog(ERROR, "unexpected rtekind: %d", (int) rel->rtekind); break; } } }
因为我的是简单查询，所以会走到：
/* Plain relation */ set_plain_rel_size(root, rel, rte);
展开 set_plain_rel_size ：
/* * set_plain_rel_size * Set size estimates for a plain relation (no subquery, no inheritance) */ static void set_plain_rel_size(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte) { /* * Test any partial indexes of rel for applicability. We must do this * first since partial unique indexes can affect size estimates. */ check_partial_indexes(root, rel); /* Mark rel with estimated output rows, width, etc */ set_baserel_size_estimates(root, rel); /* * Check to see if we can extract any restriction conditions from join * quals that are OR-of-AND structures. If so, add them to the rel's * restriction list, and redo the above steps. */ if (create_or_index_quals(root, rel)) { check_partial_indexes(root, rel); set_baserel_size_estimates(root, rel); } }
再对 set_baserel_size_estimates 展开一层：
/* * set_baserel_size_estimates * Set the size estimates for the given base relation. * * The rel's targetlist and restrictinfo list must have been constructed * already, and rel->tuples must be set. * * We set the following fields of the rel node: * rows: the estimated number of output tuples (after applying * restriction clauses). * width: the estimated average output tuple width in bytes. * baserestrictcost: estimated cost of evaluating baserestrictinfo clauses. */ void set_baserel_size_estimates(PlannerInfo *root, RelOptInfo *rel) { double nrows; /* Should only be applied to base relations */ Assert(rel->relid > 0); nrows = rel->tuples * clauselist_selectivity(root, rel->baserestrictinfo, 0, JOIN_INNER, NULL); rel->rows = clamp_row_est(nrows); cost_qual_eval(&rel->baserestrictcost, rel->baserestrictinfo, root); set_rel_width(root, rel); }
rel->tuples 值是如何算得？1条记录第表，tuples 是2400， 4条的却是 2140。
得仔细研究。
