PostgreSQL 原始碼解讀（60）- 查詢語句#45（make_one_rel函式#10-...
這一小節繼續介紹查詢物理最佳化中的create_index_paths->choose_bitmap_and,該函式執行Bitmap AND操作後建立點陣圖索引掃描訪問路徑(BitmapAndPath)節點。
關於Bitmap Scan的相關知識,請參照PostgreSQL DBA(6) - SeqScan vs IndexScan vs BitmapHeapScan這篇文章.
下面是BitmapAnd訪問路徑的樣例:
testdb=# explain verbose select t1.*
testdb-# from t_dwxx t1
testdb-# where (dwbh > '10000' and dwbh < '15000') AND (dwdz between 'DWDZ10000' and 'DWDZ15000');
QUERY PLAN
----------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.t_dwxx t1 (cost=32.33..88.38 rows=33 width=20)
Output: dwmc, dwbh, dwdz
Recheck Cond: (((t1.dwbh)::text > '10000'::text) AND ((t1.dwbh)::text < '15000'::text) AND ((t1.dwdz)::text >= 'DWDZ10000'
::text) AND ((t1.dwdz)::text <= 'DWDZ15000'::text))
-> BitmapAnd (cost=32.33..32.33 rows=33 width=0) -->BitmapAnd
-> Bitmap Index Scan on t_dwxx_pkey (cost=0.00..13.86 rows=557 width=0)
Index Cond: (((t1.dwbh)::text > '10000'::text) AND ((t1.dwbh)::text < '15000'::text))
-> Bitmap Index Scan on idx_dwxx_dwdz (cost=0.00..18.21 rows=592 width=0)
Index Cond: (((t1.dwdz)::text >= 'DWDZ10000'::text) AND ((t1.dwdz)::text <= 'DWDZ15000'::text))
(8 rows)
一、資料結構
Cost相關
注意:實際使用的引數值透過系統配置檔案定義,而不是這裡的常量定義!
typedef double Cost; /* execution cost (in page-access units) */
/* defaults for costsize.c's Cost parameters */
/* NB: cost-estimation code should use the variables, not these constants! */
/* 注意:實際值透過系統配置檔案定義,而不是這裡的常量定義! */
/* If you change these, update backend/utils/misc/postgresql.sample.conf */
#define DEFAULT_SEQ_PAGE_COST 1.0 //順序掃描page的成本
#define DEFAULT_RANDOM_PAGE_COST 4.0 //隨機掃描page的成本
#define DEFAULT_CPU_TUPLE_COST 0.01 //處理一個元組的CPU成本
#define DEFAULT_CPU_INDEX_TUPLE_COST 0.005 //處理一個索引元組的CPU成本
#define DEFAULT_CPU_OPERATOR_COST 0.0025 //執行一次操作或函式的CPU成本
#define DEFAULT_PARALLEL_TUPLE_COST 0.1 //並行執行,從一個worker傳輸一個元組到另一個worker的成本
#define DEFAULT_PARALLEL_SETUP_COST 1000.0 //構建並行執行環境的成本
#define DEFAULT_EFFECTIVE_CACHE_SIZE 524288 /*先前已有介紹, measured in pages */
double seq_page_cost = DEFAULT_SEQ_PAGE_COST;
double random_page_cost = DEFAULT_RANDOM_PAGE_COST;
double cpu_tuple_cost = DEFAULT_CPU_TUPLE_COST;
double cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;
double cpu_operator_cost = DEFAULT_CPU_OPERATOR_COST;
double parallel_tuple_cost = DEFAULT_PARALLEL_TUPLE_COST;
double parallel_setup_cost = DEFAULT_PARALLEL_SETUP_COST;
int effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
Cost disable_cost = 1.0e10;//1後面10個0,透過設定一個巨大的成本,讓最佳化器自動放棄此路徑
int max_parallel_workers_per_gather = 2;//每次gather使用的worker數
PathClauseUsage
/* Per-path data used within choose_bitmap_and() */
typedef struct
{
Path *path; /* 訪問路徑連結串列,IndexPath, BitmapAndPath, or BitmapOrPath */
List *quals; /* 限制條件子句連結串列,the WHERE clauses it uses */
List *preds; /* 部分索引謂詞連結串列,predicates of its partial index(es) */
Bitmapset *clauseids; /* 點陣圖集合,quals+preds represented as a bitmapset */
} PathClauseUsage;
二、原始碼解讀
choose_bitmap_and函式
create_index_paths->choose_bitmap_and函式,該函式給定非空的點陣圖訪問路徑連結串列,執行AND操作後合併到一條路徑中,最終得到點陣圖索引掃描訪問路徑節點.
/*
* choose_bitmap_and
* Given a nonempty list of bitmap paths, AND them into one path.
* 給定非空的點陣圖訪問路徑連結串列,執行AND操作後合併到一條路徑中
*
* This is a nontrivial decision since we can legally use any subset of the
* given path set. We want to choose a good tradeoff between selectivity
* and cost of computing the bitmap.
* 這是一個非常重要的策略，因為這樣可以合法地使用給定路徑集的任何子集。
*
* The result is either a single one of the inputs, or a BitmapAndPath
* combining multiple inputs.
* 輸出結果要麼是輸出的其中之一,要麼是融合多個輸入之後的BitmapAndPath
*/
static Path *
choose_bitmap_and(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
int npaths = list_length(paths);
PathClauseUsage **pathinfoarray;
PathClauseUsage *pathinfo;
List *clauselist;
List *bestpaths = NIL;
Cost bestcost = 0;
int i,
j;
ListCell *l;
Assert(npaths > 0); /* else caller error */
if (npaths == 1)
return (Path *) linitial(paths); /* easy case */
/*
* In theory we should consider every nonempty subset of the given paths.
* In practice that seems like overkill, given the crude nature of the
* estimates, not to mention the possible effects of higher-level AND and
* OR clauses. Moreover, it's completely impractical if there are a large
* number of paths, since the work would grow as O(2^N).
* 理論上，我們應該考慮給定路徑的所有非空子集。在實踐中，
* 考慮到估算的不確定性和成本，以及更高階別的AND和OR約束可能產生的影響,這樣的做法並不合適.
* 此外,它並不切合實際,如果有大量的路徑,這項工作的複雜度會是指數級的O(2 ^ N)。
*
* As a heuristic, we first check for paths using exactly the same sets of
* WHERE clauses + index predicate conditions, and reject all but the
* cheapest-to-scan in any such group. This primarily gets rid of indexes
* that include the interesting columns but also irrelevant columns. (In
* situations where the DBA has gone overboard on creating variant
* indexes, this can make for a very large reduction in the number of
* paths considered further.)
* 作為一種啟發式方法，首先使用完全相同的WHERE子句+索引謂詞條件集檢查路徑，
* 並去掉這類條件組中除成本最低之外的所有路徑。
* 這主要是去掉了包含interesting列和不相關列的索引。
* (在DBA過度建立索引的情況下，這會大大減少進一步考慮的路徑數量。)
*
* We then sort the surviving paths with the cheapest-to-scan first, and
* for each path, consider using that path alone as the basis for a bitmap
* scan. Then we consider bitmap AND scans formed from that path plus
* each subsequent (higher-cost) path, adding on a subsequent path if it
* results in a reduction in the estimated total scan cost. This means we
* consider about O(N^2) rather than O(2^N) path combinations, which is
* quite tolerable, especially given than N is usually reasonably small
* because of the prefiltering step. The cheapest of these is returned.
* 然後，我們首先使用成本最低的掃描路徑對現存的路徑進行排序，
* 對於每個路徑，考慮單獨使用該路徑作為點陣圖掃描的基礎。
* 然後我們考慮點陣圖和從該路徑形成的掃描加上每個後續的(更高成本的)路徑，
* 如果後續路徑導致估算的總掃描成本減少，那麼就新增一個後續路徑。
* 這意味著我們只需要處理O(N ^ 2),而不是O(2 ^ N)個路徑組合,
* 這樣的成本完全可以接受,特別是N通常相當小時。函式返回成本最低的路徑。
*
* We will only consider AND combinations in which no two indexes use the
* same WHERE clause. This is a bit of a kluge: it's needed because
* costsize.c and clausesel.c aren't very smart about redundant clauses.
* They will usually double-count the redundant clauses, producing a
* too-small selectivity that makes a redundant AND step look like it
* reduces the total cost. Perhaps someday that code will be smarter and
* we can remove this limitation. (But note that this also defends
* against flat-out duplicate input paths, which can happen because
* match_join_clauses_to_index will find the same OR join clauses that
* extract_restriction_or_clauses has pulled OR restriction clauses out
* of.)
* 我們將只考慮沒有兩個索引同時使用相同的WHERE子句的AND組合。
* 這是一個有點蹩腳的做法:之所以這樣是因為cost.c和clausesel.c未能足夠聰明的處理多餘的子句。
* 它們通常會重複計算冗餘子句，從而產生很小的選擇性，使冗餘子句看起來像是減少了總成本。
* 也許有一天，程式碼會變得更聰明，我們可以消除這個限制。
* (但是要注意，這也可以防止完全重複的輸入路徑，
* 因為match_join_clauses_to_index會找到相同的OR連線子句,而這些子句
* 已透過extract_restriction_or_clauses函式提升到外面去了.)
*
* For the same reason, we reject AND combinations in which an index
* predicate clause duplicates another clause. Here we find it necessary
* to be even stricter: we'll reject a partial index if any of its
* predicate clauses are implied by the set of WHERE clauses and predicate
* clauses used so far. This covers cases such as a condition "x = 42"
* used with a plain index, followed by a clauseless scan of a partial
* index "WHERE x >= 40 AND x < 50". The partial index has been accepted
* only because "x = 42" was present, and so allowing it would partially
* double-count selectivity. (We could use predicate_implied_by on
* regular qual clauses too, to have a more intelligent, but much more
* expensive, check for redundancy --- but in most cases simple equality
* seems to suffice.)
* 出於同樣的原因，我們不會組合索引謂詞子句與另一個重複的子句。
* 在這裡，有必要更加嚴格 : 如果部分索引的任何謂詞子句
* 隱含在WHERE子句中，則不能使用此索引。
* 這裡包括了形如使用普通索引的“x = 42”和使用部分索引“x >= 40和x < 50”的情況。
* 部分索引被接受，是因為存在“x = 42”，因此允許它部分重複計數選擇性。
* (我們也可以在普通的qual子句上使用predicate_implied_by函式，
* 這樣就可以更智慧但更昂貴地檢查冗餘——但在大多數情況下，簡單的等式似乎就足夠了。)
*/
/*
* Extract clause usage info and detect any paths that use exactly the
* same set of clauses; keep only the cheapest-to-scan of any such groups.
* The surviving paths are put into an array for qsort'ing.
* 提取子句使用資訊並檢測使用完全相同子句集的所有路徑;
* 只保留這類路徑中成本最低的,這些路徑被放入一個陣列中進行qsort'ing
*/
pathinfoarray = (PathClauseUsage **)
palloc(npaths * sizeof(PathClauseUsage *));//陣列
clauselist = NIL;
npaths = 0;
foreach(l, paths)//遍歷paths
{
Path *ipath = (Path *) lfirst(l);
pathinfo = classify_index_clause_usage(ipath, &clauselist);//歸類路徑資訊
for (i = 0; i < npaths; i++)
{
if (bms_equal(pathinfo->clauseids, pathinfoarray[i]->clauseids))
break;//只要發現子句集一樣,就繼續執行
}
if (i < npaths)//發現相同的
{
/* duplicate clauseids, keep the cheaper one */
//相同的約束條件,只保留成本最低的
Cost ncost;
Cost ocost;
Selectivity nselec;
Selectivity oselec;
cost_bitmap_tree_node(pathinfo->path, &ncost, &nselec);//計算成本
cost_bitmap_tree_node(pathinfoarray[i]->path, &ocost, &oselec);
if (ncost < ocost)
pathinfoarray[i] = pathinfo;
}
else//沒有發現條件一樣的,新增到陣列中
{
/* not duplicate clauseids, add to array */
pathinfoarray[npaths++] = pathinfo;
}
}
/* If only one surviving path, we're done */
if (npaths == 1)//結果只有一條,則返回之
return pathinfoarray[0]->path;
/* Sort the surviving paths by index access cost */
qsort(pathinfoarray, npaths, sizeof(PathClauseUsage *),
path_usage_comparator);//以索引訪問成本排序現存路徑
/*
* For each surviving index, consider it as an "AND group leader", and see
* whether adding on any of the later indexes results in an AND path with
* cheaper total cost than before. Then take the cheapest AND group.
* 對於現存的索引,把它視為"AND group leader",
* 並檢視是否新增了以後的索引後，會得到一個總成本比以前更低的AND路徑。
* 選擇成本最低的AND組.
*
*/
for (i = 0; i < npaths; i++)//遍歷這些路徑
{
Cost costsofar;
List *qualsofar;
Bitmapset *clauseidsofar;
ListCell *lastcell;
pathinfo = pathinfoarray[i];//PathClauseUsage結構體
paths = list_make1(pathinfo->path);//路徑連結串列
costsofar = bitmap_scan_cost_est(root, rel, pathinfo->path);//當前的成本
qualsofar = list_concat(list_copy(pathinfo->quals),
list_copy(pathinfo->preds));
clauseidsofar = bms_copy(pathinfo->clauseids);
lastcell = list_head(paths); /* 用於快速刪除,for quick deletions */
for (j = i + 1; j < npaths; j++)//掃描後續的路徑
{
Cost newcost;
pathinfo = pathinfoarray[j];
/* Check for redundancy */
if (bms_overlap(pathinfo->clauseids, clauseidsofar))
continue; /* 多餘的路徑,consider it redundant */
if (pathinfo->preds)//部分索引?
{
bool redundant = false;
/* we check each predicate clause separately */
//單獨檢查每一個謂詞
foreach(l, pathinfo->preds)
{
Node *np = (Node *) lfirst(l);
if (predicate_implied_by(list_make1(np), qualsofar, false))
{
redundant = true;
break; /* out of inner foreach loop */
}
}
if (redundant)
continue;
}
/* tentatively add new path to paths, so we can estimate cost */
//嘗試在路徑中新增新路徑，這樣我們就可以估算成本
paths = lappend(paths, pathinfo->path);
newcost = bitmap_and_cost_est(root, rel, paths);//估算成本
if (newcost < costsofar)//新成本更低
{
/* keep new path in paths, update subsidiary variables */
costsofar = newcost;
qualsofar = list_concat(qualsofar,
list_copy(pathinfo->quals));//新增此條件
qualsofar = list_concat(qualsofar,
list_copy(pathinfo->preds));//新增此謂詞
clauseidsofar = bms_add_members(clauseidsofar,
pathinfo->clauseids);//新增此子句ID
lastcell = lnext(lastcell);
}
else
{
/* reject new path, remove it from paths list */
paths = list_delete_cell(paths, lnext(lastcell), lastcell);//去掉新路徑
}
Assert(lnext(lastcell) == NULL);
}
/* Keep the cheapest AND-group (or singleton) */
if (i == 0 || costsofar < bestcost)//單條路徑或者取得最小的成本
{
bestpaths = paths;
bestcost = costsofar;
}
/* some easy cleanup (we don't try real hard though) */
list_free(qualsofar);
}
if (list_length(bestpaths) == 1)
return (Path *) linitial(bestpaths); /* 無需AND路徑,no need for AND */
return (Path *) create_bitmap_and_path(root, rel, bestpaths);//生成BitmapAndPath
}
//-------------------------------------------------------------------------- bitmap_scan_cost_est
/*
* Estimate the cost of actually executing a bitmap scan with a single
* index path (no BitmapAnd, at least not at this level; but it could be
* a BitmapOr).
*/
static Cost
bitmap_scan_cost_est(PlannerInfo *root, RelOptInfo *rel, Path *ipath)
{
BitmapHeapPath bpath;
Relids required_outer;
/* Identify required outer rels, in case it's a parameterized scan */
required_outer = get_bitmap_tree_required_outer(ipath);
/* Set up a dummy BitmapHeapPath */
bpath.path.type = T_BitmapHeapPath;
bpath.path.pathtype = T_BitmapHeapScan;
bpath.path.parent = rel;
bpath.path.pathtarget = rel->reltarget;
bpath.path.param_info = get_baserel_parampathinfo(root, rel,
required_outer);
bpath.path.pathkeys = NIL;
bpath.bitmapqual = ipath;
/*
* Check the cost of temporary path without considering parallelism.
* Parallel bitmap heap path will be considered at later stage.
*/
bpath.path.parallel_workers = 0;
cost_bitmap_heap_scan(&bpath.path, root, rel,
bpath.path.param_info,
ipath,
get_loop_count(root, rel->relid, required_outer));//BitmapHeapPath計算成本
return bpath.path.total_cost;
}
//-------------------------------------------------------------------------- bitmap_and_cost_est
/*
* Estimate the cost of actually executing a BitmapAnd scan with the given
* inputs.
* 給定輸入,估算實際執行BitmapAnd掃描的實際成本
*/
static Cost
bitmap_and_cost_est(PlannerInfo *root, RelOptInfo *rel, List *paths)
{
BitmapAndPath apath;
BitmapHeapPath bpath;
Relids required_outer;
/* Set up a dummy BitmapAndPath */
apath.path.type = T_BitmapAndPath;
apath.path.pathtype = T_BitmapAnd;
apath.path.parent = rel;
apath.path.pathtarget = rel->reltarget;
apath.path.param_info = NULL; /* not used in bitmap trees */
apath.path.pathkeys = NIL;
apath.bitmapquals = paths;
cost_bitmap_and_node(&apath, root);
/* Identify required outer rels, in case it's a parameterized scan */
required_outer = get_bitmap_tree_required_outer((Path *) &apath);
/* Set up a dummy BitmapHeapPath */
bpath.path.type = T_BitmapHeapPath;
bpath.path.pathtype = T_BitmapHeapScan;
bpath.path.parent = rel;
bpath.path.pathtarget = rel->reltarget;
bpath.path.param_info = get_baserel_parampathinfo(root, rel,
required_outer);
bpath.path.pathkeys = NIL;
bpath.bitmapqual = (Path *) &apath;
/*
* Check the cost of temporary path without considering parallelism.
* Parallel bitmap heap path will be considered at later stage.
*/
bpath.path.parallel_workers = 0;
/* Now we can do cost_bitmap_heap_scan */
cost_bitmap_heap_scan(&bpath.path, root, rel,
bpath.path.param_info,
(Path *) &apath,
get_loop_count(root, rel->relid, required_outer));//BitmapHeapPath計算成本
return bpath.path.total_cost;
}
//-------------------------------------------------------------------------- create_bitmap_and_path
/*
* create_bitmap_and_path
* Creates a path node representing a BitmapAnd.
*/
BitmapAndPath *
create_bitmap_and_path(PlannerInfo *root,
RelOptInfo *rel,
List *bitmapquals)
{
BitmapAndPath *pathnode = makeNode(BitmapAndPath);
pathnode->path.pathtype = T_BitmapAnd;
pathnode->path.parent = rel;
pathnode->path.pathtarget = rel->reltarget;
pathnode->path.param_info = NULL; /* not used in bitmap trees */
/*
* Currently, a BitmapHeapPath, BitmapAndPath, or BitmapOrPath will be
* parallel-safe if and only if rel->consider_parallel is set. So, we can
* set the flag for this path based only on the relation-level flag,
* without actually iterating over the list of children.
*/
pathnode->path.parallel_aware = false;
pathnode->path.parallel_safe = rel->consider_parallel;
pathnode->path.parallel_workers = 0;
pathnode->path.pathkeys = NIL; /* always unordered */
pathnode->bitmapquals = bitmapquals;
/* this sets bitmapselectivity as well as the regular cost fields: */
cost_bitmap_and_node(pathnode, root);//計算成本
return pathnode;
}
//----------------------------------------------------- cost_bitmap_and_node
/*
* cost_bitmap_and_node
* Estimate the cost of a BitmapAnd node
* 估算BitmapAnd節點成本
*
* Note that this considers only the costs of index scanning and bitmap
* creation, not the eventual heap access. In that sense the object isn't
* truly a Path, but it has enough path-like properties (costs in particular)
* to warrant treating it as one. We don't bother to set the path rows field,
* however.
*/
void
cost_bitmap_and_node(BitmapAndPath *path, PlannerInfo *root)
{
Cost totalCost;
Selectivity selec;
ListCell *l;
/*
* We estimate AND selectivity on the assumption that the inputs are
* independent. This is probably often wrong, but we don't have the info
* to do better.
*
* The runtime cost of the BitmapAnd itself is estimated at 100x
* cpu_operator_cost for each tbm_intersect needed. Probably too small,
* definitely too simplistic?
*/
totalCost = 0.0;
selec = 1.0;
foreach(l, path->bitmapquals)
{
Path *subpath = (Path *) lfirst(l);
Cost subCost;
Selectivity subselec;
cost_bitmap_tree_node(subpath, &subCost, &subselec);
selec *= subselec;
totalCost += subCost;
if (l != list_head(path->bitmapquals))
totalCost += 100.0 * cpu_operator_cost;
}
path->bitmapselectivity = selec;
path->path.rows = 0; /* per above, not used */
path->path.startup_cost = totalCost;
path->path.total_cost = totalCost;
}
三、跟蹤分析
測試指令碼如下
select t1.*
from t_dwxx t1
where (dwbh > '10000' and dwbh < '15000') AND (dwdz between 'DWDZ10000' and 'DWDZ15000');
啟動gdb跟蹤
(gdb) b choose_bitmap_and
Breakpoint 1 at 0x74e8c2: file indxpath.c, line 1372.
(gdb) c
Continuing.
Breakpoint 1, choose_bitmap_and (root=0x1666638, rel=0x1666a48, paths=0x166fdf0) at indxpath.c:1372
1372 int npaths = list_length(paths);
輸入引數
(gdb) p *paths
$1 = {type = T_List, length = 2, head = 0x166fe20, tail = 0x16706b8}
(gdb) p *(Node *)paths->head->data.ptr_value
$2 = {type = T_IndexPath}
(gdb) p *(Node *)paths->head->next->data.ptr_value
$3 = {type = T_IndexPath}
(gdb) set $p1=(IndexPath *)paths->head->data.ptr_value
(gdb) set $p2=(IndexPath *)paths->head->next->data.ptr_value
(gdb) p *$p1
$4 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x1666a48, pathtarget = 0x166d988, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 33, startup_cost = 0.28500000000000003,
total_cost = 116.20657683302848, pathkeys = 0x0}, indexinfo = 0x166e420, indexclauses = 0x166f528,
indexquals = 0x166f730, indexqualcols = 0x166f780, indexorderbys = 0x0, indexorderbycols = 0x0,
indexscandir = ForwardScanDirection, indextotalcost = 18.205000000000002, indexselectivity = 0.059246954595791879}
(gdb) p *$p2
$5 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x1666a48, pathtarget = 0x166d988, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 33, startup_cost = 0.28500000000000003,
total_cost = 111.33157683302848, pathkeys = 0x0}, indexinfo = 0x1666c58, indexclauses = 0x166fed0,
indexquals = 0x166ffc8, indexqualcols = 0x1670018, indexorderbys = 0x0, indexorderbycols = 0x0,
indexscandir = ForwardScanDirection, indextotalcost = 13.855, indexselectivity = 0.055688888888888899}
paths中的第1個元素對應(dwbh > '10000' and dwbh < '15000') ,第2個元素對應(dwdz between 'DWDZ10000' and 'DWDZ15000')
(gdb) set $ri1=(RestrictInfo *)$p1->indexclauses->head->data.ptr_value
(gdb) set $tmp=(RelabelType *)((OpExpr *)$ri1->clause)->args->head->data.ptr_value
(gdb) p *(Var *)$tmp->arg
$17 = {xpr = {type = T_Var}, varno = 1, varattno = 3, vartype = 1043, vartypmod = 104, varcollid = 100, varlevelsup = 0,
varnoold = 1, varoattno = 3, location = 76}
(gdb) p *(Node *)((OpExpr *)$ri1->clause)->args->head->next->data.ptr_value
$18 = {type = T_Const}
(gdb) p *(Const *)((OpExpr *)$ri1->clause)->args->head->next->data.ptr_value
$19 = {xpr = {type = T_Const}, consttype = 25, consttypmod = -1, constcollid = 100, constlen = -1, constvalue = 23636608,
constisnull = false, constbyval = false, location = 89}
開始遍歷paths,提取子句條件並檢測是否使用完全相同子句集的所有路徑,只保留這些路徑中成本最低的,這些路徑被放入一個陣列中進行qsort.
...
(gdb)
1444 npaths = 0;
(gdb)
1445 foreach(l, paths)
(gdb)
收集資訊到PathClauseUsage陣列中
...
(gdb) n
1471 pathinfoarray[npaths++] = pathinfo;
(gdb)
1445 foreach(l, paths)
(gdb)
1476 if (npaths == 1)
(gdb) p npaths
$26 = 2
(gdb)
按成本排序
(gdb) n
1480 qsort(pathinfoarray, npaths, sizeof(PathClauseUsage *),
遍歷路徑,找到成本最低的AND group
1488 for (i = 0; i < npaths; i++)
(gdb) n
1495 pathinfo = pathinfoarray[i];
(gdb)
1496 paths = list_make1(pathinfo->path);
(gdb)
1497 costsofar = bitmap_scan_cost_est(root, rel, pathinfo->path);
(gdb)
1499 list_copy(pathinfo->preds));
獲取當前的成本,設定當前的條件子句
(gdb) p costsofar
$27 = 89.003250000000008
(gdb) n
1498 qualsofar = list_concat(list_copy(pathinfo->quals),
執行AND操作(路徑疊加),成本更低,調整當前成本和相關變數
(gdb) n
1531 newcost = bitmap_and_cost_est(root, rel, paths);
(gdb)
1532 if (newcost < costsofar)
(gdb) p newcost
$30 = 88.375456720095343
(gdb) n
1535 costsofar = newcost;
(gdb) n
1537 list_copy(pathinfo->quals));
(gdb)
1536 qualsofar = list_concat(qualsofar,
(gdb)
1539 list_copy(pathinfo->preds));
處理下一個AND條件,單個AND條件比上一個條件成本高,保留原來的
1488 for (i = 0; i < npaths; i++)
(gdb)
1495 pathinfo = pathinfoarray[i];
(gdb)
1496 paths = list_make1(pathinfo->path);
(gdb)
1497 costsofar = bitmap_scan_cost_est(root, rel, pathinfo->path);
(gdb)
1499 list_copy(pathinfo->preds));
(gdb) p costsofar
$34 = 94.053250000000006
(gdb) n
1498 qualsofar = list_concat(list_copy(pathinfo->quals),
(gdb)
1500 clauseidsofar = bms_copy(pathinfo->clauseids);
(gdb)
1501 lastcell = list_head(paths); /* for quick deletions */
(gdb)
1503 for (j = i + 1; j < npaths; j++)
(gdb)
1553 if (i == 0 || costsofar < bestcost)
(gdb) p i
$35 = 1
(gdb) p costsofar
$36 = 94.053250000000006
(gdb) p bestcost
$37 = 88.375456720095343
(gdb)
構建BitmapAndPath,返回
(gdb) n
1563 if (list_length(bestpaths) == 1)
(gdb)
1565 return (Path *) create_bitmap_and_path(root, rel, bestpaths);
(gdb)
1566 }
DONE!
(gdb) n
create_index_paths (root=0x1666638, rel=0x1666a48) at indxpath.c:337
337 bpath = create_bitmap_heap_path(root, rel, bitmapqual,
四、參考資料
allpaths.c
cost.h
costsize.c
PG Document:Query Planning
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374844/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（45）- 查詢語句#30（query_planner函式#6）SQL原始碼函式
- PostgreSQL 原始碼解讀（70）- 查詢語句#55（make_one_rel函式#20-...SQL原始碼函式
- PostgreSQL 原始碼解讀（71）- 查詢語句#56（make_one_rel函式#21-...SQL原始碼函式
- PostgreSQL 原始碼解讀（67）- 查詢語句#52（make_one_rel函式#17-...SQL原始碼函式
- PostgreSQL 原始碼解讀（68）- 查詢語句#53（make_one_rel函式#18-...SQL原始碼函式
- PostgreSQL 原始碼解讀（69）- 查詢語句#54（make_one_rel函式#19-...SQL原始碼函式
- PostgreSQL 原始碼解讀（66）- 查詢語句#51（make_one_rel函式#16-...SQL原始碼函式
- PostgreSQL 原始碼解讀（72）- 查詢語句#57（make_one_rel函式#22-...SQL原始碼函式
- PostgreSQL 原始碼解讀（65）- 查詢語句#50（make_one_rel函式#15-...SQL原始碼函式
- PostgreSQL 原始碼解讀（62）- 查詢語句#47（make_one_rel函式#12-...SQL原始碼函式
- PostgreSQL 原始碼解讀（63）- 查詢語句#48（make_one_rel函式#13-...SQL原始碼函式
- PostgreSQL 原始碼解讀（64）- 查詢語句#49（make_one_rel函式#14-...SQL原始碼函式
- PostgreSQL 原始碼解讀（61）- 查詢語句#46（make_one_rel函式#11-...SQL原始碼函式
- PostgreSQL 原始碼解讀（52）- 查詢語句#37（make_one_rel函式#2-估...SQL原始碼函式
- PostgreSQL 原始碼解讀（53）- 查詢語句#38（make_one_rel函式#3-順...SQL原始碼函式
- PostgreSQL 原始碼解讀（54）- 查詢語句#39（make_one_rel函式#4-生...SQL原始碼函式
- PostgreSQL 原始碼解讀（55）- 查詢語句#40（make_one_rel函式#5-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（56）- 查詢語句#41（make_one_rel函式#6-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（57）- 查詢語句#42（make_one_rel函式#7-索...SQL原始碼函式
- PostgreSQL 原始碼解讀（58）- 查詢語句#43（make_one_rel函式#8-B...SQL原始碼函式
- PostgreSQL 原始碼解讀（59）- 查詢語句#44（make_one_rel函式#9-B...SQL原始碼函式
- PostgreSQL 原始碼解讀（49）- 查詢語句#34（make_one_rel函式#1-概覽）SQL原始碼函式
- PostgreSQL 原始碼解讀（83）- 查詢語句#68（PortalStart函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（75）- 查詢語句#60（Review - standard_...SQL原始碼View
- PostgreSQL 原始碼解讀（82）- 查詢語句#67（PortalXXX系列函式）SQL原始碼函式
- PostgreSQL 原始碼解讀（89）- 查詢語句#74（SeqNext函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（90）- 查詢語句#75（ExecHashJoin函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（91）- 查詢語句#76（ExecHashJoin函式#2）SQL原始碼函式
- PostgreSQL 原始碼解讀（88）- 查詢語句#73（SeqNext函式#1）SQL原始碼函式
- PostgreSQL 原始碼解讀（93）- 查詢語句#77（ExecHashJoin函式#3）SQL原始碼函式
- PostgreSQL 原始碼解讀（52）- 查詢語句#37（make_one_rel函式#2-估算關係大小）SQL原始碼函式
- PostgreSQL 原始碼解讀（73）- 查詢語句#58（grouping_planner函式...SQL原始碼函式
- PostgreSQL 原始碼解讀（95）- 查詢語句#78（ExecHashJoin函式#4-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（97）- 查詢語句#79（ExecHashJoin函式#5-H...SQL原始碼函式
- PostgreSQL 原始碼解讀（43）- 查詢語句#28（query_planner函式#5）SQL原始碼函式
- PostgreSQL 原始碼解讀（46）- 查詢語句#31（query_planner函式#7）SQL原始碼函式
- PostgreSQL 原始碼解讀（47）- 查詢語句#32（query_planner函式#8）SQL原始碼函式
- PostgreSQL 原始碼解讀（48）- 查詢語句#33（query_planner函式#9）SQL原始碼函式