PostgreSQL 原始碼解讀（69）- 查詢語句#54（make_one_rel函式#19-...
本節大體介紹了動態規劃演算法實現(standard_join_search)中的join_search_one_level->make_join_rel->populate_joinrel_with_paths->add_paths_to_joinrel->match_unsorted_outer中的initial_cost_nestloop和final_cost_nestloop函式，這些函式用於計算nestloop join的Cost。
一、資料結構
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數
二、原始碼解讀
nested loop join的演算法實現虛擬碼如下:
FOR row#1 IN (select * from dataset#1) LOOP
FOR row#2 IN (select * from dataset#2 where row#1 is matched) LOOP
output values from row#1 and row#2
END LOOP
END LOOP
initial_cost_nestloop
該函式預估nestloop join訪問路徑的成本
//---------------------------------------------------------------------- initial_cost_nestloop
/*
* initial_cost_nestloop
* Preliminary estimate of the cost of a nestloop join path.
* 預估nestloop join訪問路徑的成本
*
* This must quickly produce lower-bound estimates of the path's startup and
* total costs. If we are unable to eliminate the proposed path from
* consideration using the lower bounds, final_cost_nestloop will be called
* to obtain the final estimates.
*
* The exact division of labor between this function and final_cost_nestloop
* is private to them, and represents a tradeoff between speed of the initial
* estimate and getting a tight lower bound. We choose to not examine the
* join quals here, since that's by far the most expensive part of the
* calculations. The end result is that CPU-cost considerations must be
* left for the second phase; and for SEMI/ANTI joins, we must also postpone
* incorporation of the inner path's run cost.
*
* 'workspace' is to be filled with startup_cost, total_cost, and perhaps
* other data to be used by final_cost_nestloop
* 'jointype' is the type of join to be performed
* 'outer_path' is the outer input to the join
* 'inner_path' is the inner input to the join
* 'extra' contains miscellaneous information about the join
*/
void
initial_cost_nestloop(PlannerInfo *root, JoinCostWorkspace *workspace,
JoinType jointype,
Path *outer_path, Path *inner_path,
JoinPathExtraData *extra)
{
Cost startup_cost = 0;
Cost run_cost = 0;
double outer_path_rows = outer_path->rows;
Cost inner_rescan_start_cost;
Cost inner_rescan_total_cost;
Cost inner_run_cost;
Cost inner_rescan_run_cost;
/* 估算重新掃描內表的成本.estimate costs to rescan the inner relation */
cost_rescan(root, inner_path,
&inner_rescan_start_cost,
&inner_rescan_total_cost);
/* cost of source data */
/*
* NOTE: clearly, we must pay both outer and inner paths' startup_cost
* before we can start returning tuples, so the join's startup cost is
* their sum. We'll also pay the inner path's rescan startup cost
* multiple times.
* 注意:顯然，在開始返回元組之前，必須耗費外表訪問路徑和內表訪問路徑的啟動成本startup_cost，
* 因此連線的啟動成本是它們的總和。我們還需要多次計算內表訪問路徑的重新掃描啟動成本。
*/
startup_cost += outer_path->startup_cost + inner_path->startup_cost;
run_cost += outer_path->total_cost - outer_path->startup_cost;
if (outer_path_rows > 1)
run_cost += (outer_path_rows - 1) * inner_rescan_start_cost;
inner_run_cost = inner_path->total_cost - inner_path->startup_cost;
inner_rescan_run_cost = inner_rescan_total_cost - inner_rescan_start_cost;
if (jointype == JOIN_SEMI || jointype == JOIN_ANTI ||
extra->inner_unique)
{
/*
* With a SEMI or ANTI join, or if the innerrel is known unique, the
* executor will stop after the first match.
* 對於半連線或反連線，或者如果內表已知是唯一的，執行器將在第一次匹配後停止。
*
* Getting decent estimates requires inspection of the join quals,
* which we choose to postpone to final_cost_nestloop.
* 獲得適當的估算需要檢查join quals，我們選擇將其推遲到final_cost_nestloop中實現。
*/
/* Save private data for final_cost_nestloop */
workspace->inner_run_cost = inner_run_cost;
workspace->inner_rescan_run_cost = inner_rescan_run_cost;
}
else
{
/* Normal case; we'll scan whole input rel for each outer row */
//常規的情況,對於每一個外表行,將掃描整個內表
run_cost += inner_run_cost;
if (outer_path_rows > 1)
run_cost += (outer_path_rows - 1) * inner_rescan_run_cost;
}
/* CPU costs left for later */
/* Public result fields */
//結果賦值
workspace->startup_cost = startup_cost;
workspace->total_cost = startup_cost + run_cost;
/* Save private data for final_cost_nestloop */
workspace->run_cost = run_cost;
}
//-------------------------------------- cost_rescan
/*
* cost_rescan
* Given a finished Path, estimate the costs of rescanning it after
* having done so the first time. For some Path types a rescan is
* cheaper than an original scan (if no parameters change), and this
* function embodies knowledge about that. The default is to return
* the same costs stored in the Path. (Note that the cost estimates
* actually stored in Paths are always for first scans.)
* 給定一個已完成的訪問路徑，估算在第一次完成之後重新掃描它的成本。
* 對於某些訪問路徑，重新掃描比原始掃描成本更低(如果沒有引數更改)，
* 該函式體現了這方面的資訊。
* 預設值是返回儲存在路徑中的相同成本。
* (請注意，實際儲存在路徑中的成本估算總是用於首次掃描。)
*
* This function is not currently intended to model effects such as rescans
* being cheaper due to disk block caching; what we are concerned with is
* plan types wherein the executor caches results explicitly, or doesn't
* redo startup calculations, etc.
* 該函式目前並不打算對諸如由於磁碟塊快取而使後續的掃描成本更低等效果進行建模;
* 我們關心的是計劃型別，其中執行器顯式快取結果，或不重做啟動計算，等等。
*/
static void
cost_rescan(PlannerInfo *root, Path *path,
Cost *rescan_startup_cost, /* output parameters */
Cost *rescan_total_cost)
{
switch (path->pathtype)//路徑型別
{
case T_FunctionScan:
/*
* Currently, nodeFunctionscan.c always executes the function to
* completion before returning any rows, and caches the results in
* a tuplestore. So the function eval cost is all startup cost
* and isn't paid over again on rescans. However, all run costs
* will be paid over again.
* 目前nodeFunctionscan.c在在返回資料行之前完成執行,同時會在tuplestore中快取結果.
* 因此,函式估算成本是啟動成本,同時不需要考慮重新掃描.
* 但是所有的執行成本在重新掃描時是需要的.
*/
*rescan_startup_cost = 0;
*rescan_total_cost = path->total_cost - path->startup_cost;
break;
case T_HashJoin://Hash Join
/*
* If it's a single-batch join, we don't need to rebuild the hash
* table during a rescan.
* 如果是單批連線，則不需要在重新掃描期間重新構建雜湊表。
*/
if (((HashPath *) path)->num_batches == 1)
{
/* Startup cost is exactly the cost of hash table building */
*rescan_startup_cost = 0;//啟動成本為0(構建hash表)
*rescan_total_cost = path->total_cost - path->startup_cost;
}
else
{
/* Otherwise, no special treatment */
*rescan_startup_cost = path->startup_cost;
*rescan_total_cost = path->total_cost;
}
break;
case T_CteScan:
case T_WorkTableScan:
{
/*
* These plan types materialize their final result in a
* tuplestore or tuplesort object. So the rescan cost is only
* cpu_tuple_cost per tuple, unless the result is large enough
* to spill to disk.
* 這些計劃型別在tuplestore或tuplesort物件中物化它們的最終結果。
* 因此，重新掃描的成本只是每個元組的cpu_tuple_cost，除非結果大到足以溢位到磁碟。
*/
Cost run_cost = cpu_tuple_cost * path->rows;
double nbytes = relation_byte_size(path->rows,
path->pathtarget->width);
long work_mem_bytes = work_mem * 1024L;
if (nbytes > work_mem_bytes)
{
/* It will spill, so account for re-read cost */
//如果溢位到磁碟,那麼需考慮重新讀取的成本
double npages = ceil(nbytes / BLCKSZ);
run_cost += seq_page_cost * npages;
}
*rescan_startup_cost = 0;
*rescan_total_cost = run_cost;
}
break;
case T_Material:
case T_Sort:
{
/*
* These plan types not only materialize their results, but do
* not implement qual filtering or projection. So they are
* even cheaper to rescan than the ones above. We charge only
* cpu_operator_cost per tuple. (Note: keep that in sync with
* the run_cost charge in cost_sort, and also see comments in
* cost_material before you change it.)
* 這些計劃型別不僅物化了它們的結果，而且沒有物化qual條件子句過濾或投影。
* 所以重新掃描比上面的路徑成本更低。每個元組耗費的成本只有cpu_operator_cost。
* (注意:請與cost_sort中的run_cost成本保持一致，並在更改cost_material之前檢視註釋)。
*/
Cost run_cost = cpu_operator_cost * path->rows;
double nbytes = relation_byte_size(path->rows,
path->pathtarget->width);
long work_mem_bytes = work_mem * 1024L;
if (nbytes > work_mem_bytes)
{
/* It will spill, so account for re-read cost */
//如果溢位到磁碟,那麼需考慮重新讀取的成本
double npages = ceil(nbytes / BLCKSZ);
run_cost += seq_page_cost * npages;
}
*rescan_startup_cost = 0;
*rescan_total_cost = run_cost;
}
break;
default:
*rescan_startup_cost = path->startup_cost;
*rescan_total_cost = path->total_cost;
break;
}
}
final_cost_nestloop
該函式實現nestloop join訪問路徑的成本和結果大小的最終估算。
//---------------------------------------------------------------------- final_cost_nestloop
/*
* final_cost_nestloop
* Final estimate of the cost and result size of a nestloop join path.
* nestloop join訪問路徑的成本和結果大小的最終估算。
*
* 'path' is already filled in except for the rows and cost fields
* 'workspace' is the result from initial_cost_nestloop
* 'extra' contains miscellaneous information about the join
*/
void
final_cost_nestloop(PlannerInfo *root, NestPath *path,//NL訪問路徑
JoinCostWorkspace *workspace,//initial_cost_nestloop返回的結果
JoinPathExtraData *extra)//額外的資訊
{
Path *outer_path = path->outerjoinpath;//外表訪問路徑
Path *inner_path = path->innerjoinpath;//內部訪問路徑
double outer_path_rows = outer_path->rows;//外表訪問路徑行數
double inner_path_rows = inner_path->rows;//內部訪問路徑行數
Cost startup_cost = workspace->startup_cost;//啟動成本
Cost run_cost = workspace->run_cost;//執行成本
Cost cpu_per_tuple;//處理每個tuple的CPU成本
QualCost restrict_qual_cost;//表示式處理成本
double ntuples;//元組數目
/* Protect some assumptions below that rowcounts aren't zero or NaN */
//確保引數正確
if (outer_path_rows <= 0 || isnan(outer_path_rows))
outer_path_rows = 1;
if (inner_path_rows <= 0 || isnan(inner_path_rows))
inner_path_rows = 1;
/* Mark the path with the correct row estimate */
//修正行數估算
if (path->path.param_info)
path->path.rows = path->path.param_info->ppi_rows;
else
path->path.rows = path->path.parent->rows;
/* For partial paths, scale row estimate. */
//調整並行執行的行數估算
if (path->path.parallel_workers > 0)
{
double parallel_divisor = get_parallel_divisor(&path->path);
path->path.rows =
clamp_row_est(path->path.rows / parallel_divisor);
}
/*
* We could include disable_cost in the preliminary estimate, but that
* would amount to optimizing for the case where the join method is
* disabled, which doesn't seem like the way to bet.
* 我們可以將disable_cost包含在初步估算中，但是這相當於為禁用join方法的情況進行了最佳化。
*/
if (!enable_nestloop)
startup_cost += disable_cost;
/* cost of inner-relation source data (we already dealt with outer rel) */
// 內部源資料的成本
if (path->jointype == JOIN_SEMI || path->jointype == JOIN_ANTI ||
extra->inner_unique)//半連線/反連線或者內部返回唯一值
{
/*
* With a SEMI or ANTI join, or if the innerrel is known unique, the
* executor will stop after the first match.
* 執行器在第一次匹配後立即停止.
*/
Cost inner_run_cost = workspace->inner_run_cost;
Cost inner_rescan_run_cost = workspace->inner_rescan_run_cost;
double outer_matched_rows;
double outer_unmatched_rows;
Selectivity inner_scan_frac;
/*
* For an outer-rel row that has at least one match, we can expect the
* inner scan to stop after a fraction 1/(match_count+1) of the inner
* rows, if the matches are evenly distributed. Since they probably
* aren't quite evenly distributed, we apply a fuzz factor of 2.0 to
* that fraction. (If we used a larger fuzz factor, we'd have to
* clamp inner_scan_frac to at most 1.0; but since match_count is at
* least 1, no such clamp is needed now.)
* 對於至少有一個匹配的外表行，如果匹配是均勻分佈的，
* 那麼可以任務內表掃描在內部行的:1/(match_count+1)之後停止。
* 因為它們可能不是均勻分佈的，所以我們對這個分數乘上2.0的係數。
* (如果我們使用更大的因子，我們將不得不將inner_scan_frac限制為1.0;
* 但是因為match_count至少為1，所以現在不需要這樣的處理了。)
*/
outer_matched_rows = rint(outer_path_rows * extra->semifactors.outer_match_frac);
outer_unmatched_rows = outer_path_rows - outer_matched_rows;
inner_scan_frac = 2.0 / (extra->semifactors.match_count + 1.0);
/*
* Compute number of tuples processed (not number emitted!). First,
* account for successfully-matched outer rows.
* 計算處理的元組的數量(而不是發出的數量!)
* 首先，計算成功匹配的外表行。
*/
ntuples = outer_matched_rows * inner_path_rows * inner_scan_frac;
/*
* Now we need to estimate the actual costs of scanning the inner
* relation, which may be quite a bit less than N times inner_run_cost
* due to early scan stops. We consider two cases. If the inner path
* is an indexscan using all the joinquals as indexquals, then an
* unmatched outer row results in an indexscan returning no rows,
* which is probably quite cheap. Otherwise, the executor will have
* to scan the whole inner rel for an unmatched row; not so cheap.
* 現在需要估算掃描內部關係的實際成本，由於先前的掃描停止，這可能會比inner_run_cost小很多。
* 需要考慮兩種情況。如果內表訪問路徑是使用所有的joinquals連線條件作為indexquals的索引掃描indexscan，
* 那麼一個不匹配的外部行將導致indexscan不返回任何行，這個成本可能相當低。
* 否則，執行器將必須掃描整個內表以尋找一個不匹配的行;這個成本就非常的高了。
*/
if (has_indexed_join_quals(path))//連線條件上存在索引
{
/*
* Successfully-matched outer rows will only require scanning
* inner_scan_frac of the inner relation. In this case, we don't
* need to charge the full inner_run_cost even when that's more
* than inner_rescan_run_cost, because we can assume that none of
* the inner scans ever scan the whole inner relation. So it's
* okay to assume that all the inner scan executions can be
* fractions of the full cost, even if materialization is reducing
* the rescan cost. At this writing, it's impossible to get here
* for a materialized inner scan, so inner_run_cost and
* inner_rescan_run_cost will be the same anyway; but just in
* case, use inner_run_cost for the first matched tuple and
* inner_rescan_run_cost for additional ones.
* 成功匹配的外部行只需要掃描內部關係的一部分(比例是inner_scan_frac)。
* 在這種情況下，不需要增加整個內表執行成本inner_run_cost，即使這比inner_rescan_run_cost還要高，
* 因為可以假設任何內表掃描都不會掃描整個內表。
* 因此，可以假設所有內部掃描執行都可以是全部成本的一部分，即使物化降低了重新掃描成本。
* 在寫這篇文章時，不可能在這裡進行物化的內部掃描，因此inner_run_cost和inner_rescan_run_cost是相同的;
* 但是為了以防萬一，對第一個匹配的元組使用inner_run_cost，對其他元組使用inner_rescan_run_cost。
*/
run_cost += inner_run_cost * inner_scan_frac;
if (outer_matched_rows > 1)
run_cost += (outer_matched_rows - 1) * inner_rescan_run_cost * inner_scan_frac;
/*
* Add the cost of inner-scan executions for unmatched outer rows.
* We estimate this as the same cost as returning the first tuple
* of a nonempty scan. We consider that these are all rescans,
* since we used inner_run_cost once already.
* 為不匹配的外部行新增內部掃描執行的成本。
* 這與返回非空掃描的第一個元組的成本相同。
* 我們認為這些都是rescans，因為已經使用了inner_run_cost一次。
*/
run_cost += outer_unmatched_rows *
inner_rescan_run_cost / inner_path_rows;
/*
* We won't be evaluating any quals at all for unmatched rows, so
* don't add them to ntuples.
* 對於所有不匹配的行,不會解析約束條件,因此不需要增加這些成本.
*/
}
else
{
/*
* Here, a complicating factor is that rescans may be cheaper than
* first scans. If we never scan all the way to the end of the
* inner rel, it might be (depending on the plan type) that we'd
* never pay the whole inner first-scan run cost. However it is
* difficult to estimate whether that will happen (and it could
* not happen if there are any unmatched outer rows!), so be
* conservative and always charge the whole first-scan cost once.
* We consider this charge to correspond to the first unmatched
* outer row, unless there isn't one in our estimate, in which
* case blame it on the first matched row.
* 在這裡，一個複雜的因素是重新掃描可能比第一次掃描成本更低。
* 如果我們從來沒有掃描到內表的末尾，那麼(取決於計劃型別)可能永遠不會耗費整個內表first-scan執行成本。
* 然而，很難估計這種情況是否會發生(如果存在無法匹配的外部行，這是不可能發生的!)
*/
/* First, count all unmatched join tuples as being processed */
//首先,統計所有處理過程中不匹配的行數
ntuples += outer_unmatched_rows * inner_path_rows;
/* Now add the forced full scan, and decrement appropriate count */
//現在強制新增全表掃描,並減少相應的計數
run_cost += inner_run_cost;
if (outer_unmatched_rows >= 1)
outer_unmatched_rows -= 1;
else
outer_matched_rows -= 1;
/* Add inner run cost for additional outer tuples having matches */
//對於已經匹配的外表行,增加內表執行成本
if (outer_matched_rows > 0)
run_cost += outer_matched_rows * inner_rescan_run_cost * inner_scan_frac;
/* Add inner run cost for additional unmatched outer tuples */
//對於未匹配的外表行,增加內表執行成本
if (outer_unmatched_rows > 0)
run_cost += outer_unmatched_rows * inner_rescan_run_cost;
}
}
else//普通連線
{
/* Normal-case source costs were included in preliminary estimate */
//正常情況下,源成本已在預估計算過程中統計
/* Compute number of tuples processed (not number emitted!) */
//計算處理的元組數量
ntuples = outer_path_rows * inner_path_rows;
}
/* CPU成本.CPU costs */
cost_qual_eval(&restrict_qual_cost, path->joinrestrictinfo, root);
startup_cost += restrict_qual_cost.startup;
cpu_per_tuple = cpu_tuple_cost + restrict_qual_cost.per_tuple;
run_cost += cpu_per_tuple * ntuples;
/* tlist eval costs are paid per output row, not per tuple scanned */
startup_cost += path->path.pathtarget->cost.startup;
run_cost += path->path.pathtarget->cost.per_tuple * path->path.rows;
path->path.startup_cost = startup_cost;
path->path.total_cost = startup_cost + run_cost;
}
三、跟蹤分析
測試指令碼如下
select a.*,b.grbh,b.je
from t_dwxx a,
lateral (select t1.dwbh,t1.grbh,t2.je
from t_grxx t1
inner join t_jfxx t2 on t1.dwbh = a.dwbh and t1.grbh = t2.grbh) b
where a.dwbh = '1001'
order by b.dwbh;
啟動gdb,設定斷點
(gdb) b try_nestloop_path
Breakpoint 1 at 0x7ae950: file joinpath.c, line 373.
(gdb) c
Continuing.
Breakpoint 1, try_nestloop_path (root=0x2fb3b30, joinrel=0x2fc6d28, outer_path=0x2fc2540, inner_path=0x2fc1280,
pathkeys=0x0, jointype=JOIN_INNER, extra=0x7ffec5f496e0) at joinpath.c:373
373 RelOptInfo *innerrel = inner_path->parent;
進入函式initial_cost_nestloop
(gdb)
422 initial_cost_nestloop(root, &workspace, jointype,
(gdb) step
initial_cost_nestloop (root=0x2fb3b30, workspace=0x7ffec5f49540, jointype=JOIN_INNER, outer_path=0x2fc2540,
inner_path=0x2fc1280, extra=0x7ffec5f496e0) at costsize.c:2323
2323 Cost startup_cost = 0;
進入initial_cost_nestloop->cost_rescan函式
(gdb)
2332 cost_rescan(root, inner_path,
(gdb) step
cost_rescan (root=0x2fb3b30, path=0x2fc1280, rescan_startup_cost=0x7ffec5f494a0, rescan_total_cost=0x7ffec5f49498)
at costsize.c:3613
3613 switch (path->pathtype)
路徑型別為T_SeqScan(在執行該SQL語句前,刪除了t_grxx.dwbh上的索引)
(gdb) p path->pathtype
$1 = T_SeqScan
進入相應的處理邏輯,直接複製,啟動成本&總成本與T_SeqScan一樣
(gdb) n
3699 *rescan_startup_cost = path->startup_cost;
(gdb) n
3700 *rescan_total_cost = path->total_cost;
(gdb)
3701 break;
(gdb)
回到initial_cost_nestloop,執行完成,最終結果
外表存在約束條件dwbh='1001',只有一行,內表在dwbh上沒有索引,使用了順序全表掃描
...
(gdb)
2381 workspace->run_cost = run_cost;
(gdb)
2382 }
(gdb) p *workspace
$4 = {startup_cost = 0.28500000000000003, total_cost = 1984.3025, run_cost = 1984.0174999999999,
inner_run_cost = 2.4712728827210812e-316, inner_rescan_run_cost = 6.9530954948263344e-310,
outer_rows = 3.9937697668447996e-317, inner_rows = 2.4712728827210812e-316, outer_skip_rows = 6.9530954948287059e-310,
inner_skip_rows = 6.9443062041807458e-310, numbuckets = 50092024, numbatches = 0,
inner_rows_total = 2.4751428001118265e-316}
回到try_nestloop_path
(gdb) n
try_nestloop_path (root=0x2fb3b30, joinrel=0x2fc6d28, outer_path=0x2fc2540, inner_path=0x2fc1280, pathkeys=0x0,
jointype=JOIN_INNER, extra=0x7ffec5f496e0) at joinpath.c:425
425 if (add_path_precheck(joinrel,
設定斷點,進入final_cost_nestloop
(gdb) b final_cost_nestloop
Breakpoint 2 at 0x79f3ff: file costsize.c, line 2397.
(gdb) c
Continuing.
Breakpoint 2, final_cost_nestloop (root=0x2fb3b30, path=0x2fc2ef0, workspace=0x7ffec5f49540, extra=0x7ffec5f496e0)
at costsize.c:2397
2397 Path *outer_path = path->outerjoinpath;
外表訪問路徑是索引掃描(t_dwxx),內表訪問路徑是全表順序掃描
(gdb) p *outer_path
$6 = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x2fb3570, pathtarget = 0x2fb8ee0, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 1, startup_cost = 0.28500000000000003,
total_cost = 8.3025000000000002, pathkeys = 0x0}
內錶行數為10,PG透過統計資訊準確計算了該值
(gdb) n
2400 double inner_path_rows = inner_path->rows;
(gdb)
2401 Cost startup_cost = workspace->startup_cost;
(gdb) p inner_path_rows
$8 = 10
計算成本
(gdb)
2556 cost_qual_eval(&restrict_qual_cost, path->joinrestrictinfo, root);
(gdb)
2557 startup_cost += restrict_qual_cost.startup;
(gdb) p restrict_qual_cost
$10 = {startup = 0, per_tuple = 0}
最終結果,T_NestPath,總成本total_cost = 1984.4024999999999,啟動成本startup_cost = 0.28500000000000003
2567 }
(gdb) p *path
$11 = {path = {type = T_NestPath, pathtype = T_NestLoop, parent = 0x2fc6d28, pathtarget = 0x2fc6f60, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.28500000000000003,
total_cost = 1984.4024999999999, pathkeys = 0x0}, jointype = JOIN_INNER, inner_unique = false,
outerjoinpath = 0x2fc2540, innerjoinpath = 0x2fc1280, joinrestrictinfo = 0x0}
完成呼叫
(gdb) n
create_nestloop_path (root=0x2fb3b30, joinrel=0x2fc6d28, jointype=JOIN_INNER, workspace=0x7ffec5f49540,
extra=0x7ffec5f496e0, outer_path=0x2fc2540, inner_path=0x2fc1280, restrict_clauses=0x0, pathkeys=0x0,
required_outer=0x0) at pathnode.c:2229
2229 return pathnode;
DONE!
四、參考資料
allpaths.c
cost.h
costsize.c
PG Document:Query Planning
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374830/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQL 原始碼解讀（54）- 查詢語句#39（make_one_rel函式#4-生...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 原始碼解讀（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 原始碼解讀（60）- 查詢語句#45（make_one_rel函式#10-...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 原始碼解讀（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 原始碼解讀（84）- 查詢語句#69（PortalStart->InitP...SQL原始碼
- 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 原始碼解讀（45）- 查詢語句#30（query_planner函式#6）SQL原始碼函式
- PostgreSQL 原始碼解讀（46）- 查詢語句#31（query_planner函式#7）SQL原始碼函式
- PostgreSQL 原始碼解讀（47）- 查詢語句#32（query_planner函式#8）SQL原始碼函式
- PostgreSQL 原始碼解讀（48）- 查詢語句#33（query_planner函式#9）SQL原始碼函式