本篇内容主要讲解“PostgreSQL中create_index_path函数有什么作用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中create_index_path函数有什么作用”吧!
函数build_index_paths中的子函数create_index_path实现了索引扫描成本的估算主逻辑。
一、数据结构
IndexOptInfo
回顾IndexOptInfo索引信息结构体
typedef struct IndexOptInfo
{
NodeTag type;
Oid indexoid;
Oid reltablespace;
RelOptInfo *rel;
BlockNumber pages;
double tuples;
int tree_height;
int ncolumns;
int nkeycolumns;
int *indexkeys;
Oid *indexcollations;
Oid *opfamily;
Oid *opcintype;
Oid *sortopfamily;
bool *reverse_sort;
bool *nulls_first;
bool *canreturn;
Oid relam;
List *indexprs;
List *indpred;
List *indextlist;
List *indrestrictinfo;
bool predOK;
bool unique;
bool immediate;
bool hypothetical;
//从Index Relation拷贝过来的AM(访问方法)API信息
bool amcanorderbyop;
bool amoptionalkey;
bool amsearcharray;
bool amsearchnulls;
bool amhasgettuple;
bool amhasgetbitmap;
bool amcanparallel;
void (*amcostestimate) ();
} IndexOptInfo;Cost相关
注意:实际使用的参数值通过系统配置文件定义,而不是这里的常量定义!
typedef double Cost;
#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
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数二、源码解读
create_index_path
该函数创建索引扫描路径节点,其中调用函数cost_index计算索引扫描成本.
//----------------------------------------------- create_index_path
IndexPath *
create_index_path(PlannerInfo *root,//优化器信息
IndexOptInfo *index,//索引信息
List *indexclauses,//索引约束条件链表
List *indexclausecols,//索引约束条件列编号链表,与indexclauses一一对应
List *indexorderbys,//ORDER BY原始表达式链表
List *indexorderbycols,//ORDER BY列编号链表
List *pathkeys,//排序路径键
ScanDirection indexscandir,//扫描方向
bool indexonly,//纯索引扫描?
Relids required_outer,//需依赖的外部Relids
double loop_count,//用于估计缓存的重复次数
bool partial_path)//是否并行索引扫描
{
IndexPath *pathnode = makeNode(IndexPath);//构建节点
RelOptInfo *rel = index->rel;//索引对应的Rel
List *indexquals,
*indexqualcols;
pathnode->path.pathtype = indexonly ? T_IndexOnlyScan : T_IndexScan;//路径类型
pathnode->path.parent = rel;//Relation
pathnode->path.pathtarget = rel->reltarget;//路径最终的投影列
pathnode->path.param_info = get_baserel_parampathinfo(root, rel,
required_outer);//参数化信息
pathnode->path.parallel_aware = false;//
pathnode->path.parallel_safe = rel->consider_parallel;//是否并行
pathnode->path.parallel_workers = 0;//worker数目
pathnode->path.pathkeys = pathkeys;//排序路径键
//转换条件子句(clauses)为执行器可处理的索引表达式(indexquals)
expand_indexqual_conditions(index, indexclauses, indexclausecols,
&indexquals, &indexqualcols);
pathnode->indexinfo = index;
pathnode->indexclauses = indexclauses;
pathnode->indexquals = indexquals;
pathnode->indexqualcols = indexqualcols;
pathnode->indexorderbys = indexorderbys;
pathnode->indexorderbycols = indexorderbycols;
pathnode->indexscandir = indexscandir;
cost_index(pathnode, root, loop_count, partial_path);//估算成本
return pathnode;
}
//------------------------------------ expand_indexqual_conditions
void
expand_indexqual_conditions(IndexOptInfo *index,
List *indexclauses, List *indexclausecols,
List **indexquals_p, List **indexqualcols_p)
{
List *indexquals = NIL;
List *indexqualcols = NIL;
ListCell *lcc,
*lci;
forboth(lcc, indexclauses, lci, indexclausecols)//扫描索引子句链表和匹配的列号
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcc);
int indexcol = lfirst_int(lci);
Expr *clause = rinfo->clause;//条件子句
Oid curFamily;
Oid curCollation;
Assert(indexcol < index->nkeycolumns);
curFamily = index->opfamily[indexcol];//索引列的opfamily
curCollation = index->indexcollations[indexcol];//排序规则
if (IsBooleanOpfamily(curFamily))//布尔
{
Expr *boolqual;
boolqual = expand_boolean_index_clause((Node *) clause,
indexcol,
index);//布尔表达式
if (boolqual)
{
indexquals = lappend(indexquals,
make_simple_restrictinfo(boolqual));//添加到结果中
indexqualcols = lappend_int(indexqualcols, indexcol);//列号
continue;
}
}
if (is_opclause(clause))//普通的操作符子句
{
indexquals = list_concat(indexquals,
expand_indexqual_opclause(rinfo,
curFamily,
curCollation));//合并到结果链表中
while (list_length(indexqualcols) < list_length(indexquals))
indexqualcols = lappend_int(indexqualcols, indexcol);
}
else if (IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr
{
indexquals = lappend(indexquals, rinfo);
indexqualcols = lappend_int(indexqualcols, indexcol);
}
else if (IsA(clause, RowCompareExpr))//RowCompareExpr
{
indexquals = lappend(indexquals,
expand_indexqual_rowcompare(rinfo,
index,
indexcol));
indexqualcols = lappend_int(indexqualcols, indexcol);
}
else if (IsA(clause, NullTest))//NullTest
{
Assert(index->amsearchnulls);
indexquals = lappend(indexquals, rinfo);
indexqualcols = lappend_int(indexqualcols, indexcol);
}
else
elog(ERROR, "unsupported indexqual type: %d",
(int) nodeTag(clause));
}
*indexquals_p = indexquals;//结果赋值
*indexqualcols_p = indexqualcols;
}
//------------------------------------ cost_index
void
cost_index(IndexPath *path, PlannerInfo *root, double loop_count,
bool partial_path)
{
IndexOptInfo *index = path->indexinfo;//索引信息
RelOptInfo *baserel = index->rel;//RelOptInfo信息
bool indexonly = (path->path.pathtype == T_IndexOnlyScan);//是否纯索引扫描
amcostestimate_function amcostestimate;//索引访问方法成本估算函数
List *qpquals;//qpquals链表
Cost startup_cost = 0;//启动成本
Cost run_cost = 0;//执行成本
Cost cpu_run_cost = 0;//cpu执行成本
Cost indexStartupCost;//索引启动成本
Cost indexTotalCost;//索引总成本
Selectivity indexSelectivity;//选择率
double indexCorrelation,//
csquared;//
double spc_seq_page_cost,
spc_random_page_cost;
Cost min_IO_cost,//最小IO成本
max_IO_cost;//最大IO成本
QualCost qpqual_cost;//表达式成本
Cost cpu_per_tuple;//每个tuple处理成本
double tuples_fetched;//取得的元组数量
double pages_fetched;//取得的page数量
double rand_heap_pages;//随机访问的堆page数量
double index_pages;//索引page数量
Assert(IsA(baserel, RelOptInfo) &&
IsA(index, IndexOptInfo));
Assert(baserel->relid > 0);
Assert(baserel->rtekind == RTE_RELATION);
if (path->path.param_info)//存在参数化信息
{
path->path.rows = path->path.param_info->ppi_rows;
qpquals = list_concat(
extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
path->indexquals),
extract_nonindex_conditions(path->path.param_info->ppi_clauses,
path->indexquals));
}
else
{
path->path.rows = baserel->rows;//基表的估算行数
跑
qpquals = extract_nonindex_conditions(path->indexinfo->indrestrictinfo,
path->indexquals);//从rel的约束条件子句中获取qpquals
}
if (!enable_indexscan)
startup_cost += disable_cost;//禁用索引扫描
amcostestimate = (amcostestimate_function) index->amcostestimate;//索引访问路径成本估算函数
amcostestimate(root, path, loop_count,
&indexStartupCost, &indexTotalCost,
&indexSelectivity, &indexCorrelation,
&index_pages);//调用函数btcostestimate
path->indextotalcost = indexTotalCost;//赋值
path->indexselectivity = indexSelectivity;
startup_cost += indexStartupCost;
run_cost += indexTotalCost - indexStartupCost;
tuples_fetched = clamp_row_est(indexSelectivity * baserel->tuples);//取得的元组数量
get_tablespace_page_costs(baserel->reltablespace,
&spc_random_page_cost,
&spc_seq_page_cost);//表空间访问page成本
if (loop_count > 1)//次数 > 1
{
pages_fetched = index_pages_fetched(tuples_fetched * loop_count,
baserel->pages,
(double) index->pages,
root);
if (indexonly)
pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));
rand_heap_pages = pages_fetched;
max_IO_cost = (pages_fetched * spc_random_page_cost) / loop_count;
pages_fetched = ceil(indexSelectivity * (double) baserel->pages);
pages_fetched = index_pages_fetched(pages_fetched * loop_count,
baserel->pages,
(double) index->pages,
root);
if (indexonly)
pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));
min_IO_cost = (pages_fetched * spc_random_page_cost) / loop_count;
}
else //次数 <= 1
{
pages_fetched = index_pages_fetched(tuples_fetched,
baserel->pages,
(double) index->pages,
root);//取得的page数量
if (indexonly)
pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));//纯索引扫描
rand_heap_pages = pages_fetched;//随机访问的堆page数量
//最大IO成本,假定所有的page都是随机访问获得(csquared=0)
max_IO_cost = pages_fetched * spc_random_page_cost;
//最小IO成本,假定索引和堆数据都是顺序存储(csquared=1)
pages_fetched = ceil(indexSelectivity * (double) baserel->pages);
if (indexonly)
pages_fetched = ceil(pages_fetched * (1.0 - baserel->allvisfrac));
if (pages_fetched > 0)
{
min_IO_cost = spc_random_page_cost;
if (pages_fetched > 1)
min_IO_cost += (pages_fetched - 1) * spc_seq_page_cost;
}
else
min_IO_cost = 0;
}
if (partial_path)//并行
{
if (indexonly)
rand_heap_pages = -1;
path->path.parallel_workers = compute_parallel_worker(baserel,
rand_heap_pages,
index_pages,
max_parallel_workers_per_gather);
if (path->path.parallel_workers <= 0)
return;
path->path.parallel_aware = true;
}
csquared = indexCorrelation * indexCorrelation;
run_cost += max_IO_cost + csquared * (min_IO_cost - max_IO_cost);
cost_qual_eval(&qpqual_cost, qpquals, root);
startup_cost += qpqual_cost.startup;
cpu_per_tuple = cpu_tuple_cost + qpqual_cost.per_tuple;
cpu_run_cost += cpu_per_tuple * tuples_fetched;
startup_cost += path->path.pathtarget->cost.startup;
cpu_run_cost += path->path.pathtarget->cost.per_tuple * path->path.rows;
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);
cpu_run_cost /= parallel_divisor;
}
run_cost += cpu_run_cost;
path->path.startup_cost = startup_cost;
path->path.total_cost = startup_cost + run_cost;
}
//------------------------- btcostestimate
void
btcostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
Cost *indexStartupCost, Cost *indexTotalCost,
Selectivity *indexSelectivity, double *indexCorrelation,
double *indexPages)
{
IndexOptInfo *index = path->indexinfo;
List *qinfos;
GenericCosts costs;
Oid relid;
AttrNumber colnum;
VariableStatData vardata;
double numIndexTuples;
Cost descentCost;
List *indexBoundQuals;
int indexcol;
bool eqQualHere;
bool found_saop;
bool found_is_null_op;
double num_sa_scans;
ListCell *lc;
qinfos = deconstruct_indexquals(path);//拆解路径,生成条件链表
indexBoundQuals = NIL;//索引边界条件
indexcol = 0;//索引列编号
eqQualHere = false;//
found_saop = false;
found_is_null_op = false;
num_sa_scans = 1;
foreach(lc, qinfos)//遍历条件链表
{
IndexQualInfo *qinfo = (IndexQualInfo *) lfirst(lc);
RestrictInfo *rinfo = qinfo->rinfo;
Expr *clause = rinfo->clause;
Oid clause_op;
int op_strategy;
if (indexcol != qinfo->indexcol)//indexcol匹配才进行后续处理
{
if (!eqQualHere)
break;
eqQualHere = false;
indexcol++;
if (indexcol != qinfo->indexcol)
break;
}
if (IsA(clause, ScalarArrayOpExpr))//ScalarArrayOpExpr
{
int alength = estimate_array_length(qinfo->other_operand);
found_saop = true;
if (alength > 1)
num_sa_scans *= alength;
}
else if (IsA(clause, NullTest))
{
NullTest *nt = (NullTest *) clause;
if (nt->nulltesttype == IS_NULL)
{
found_is_null_op = true;
eqQualHere = true;
}
}
clause_op = qinfo->clause_op;
if (OidIsValid(clause_op))//普通的操作符
{
op_strategy = get_op_opfamily_strategy(clause_op,
index->opfamily[indexcol]);
Assert(op_strategy != 0);
if (op_strategy == BTEqualStrategyNumber)
eqQualHere = true;
}
indexBoundQuals = lappend(indexBoundQuals, rinfo);
}
if (index->unique &&
indexcol == index->nkeycolumns - 1 &&
eqQualHere &&
!found_saop &&
!found_is_null_op)
numIndexTuples = 1.0;//唯一索引
else//非唯一索引
{
List *selectivityQuals;
Selectivity btreeSelectivity;//选择率
selectivityQuals = add_predicate_to_quals(index, indexBoundQuals);//添加谓词
btreeSelectivity = clauselist_selectivity(root, selectivityQuals,
index->rel->relid,
JOIN_INNER,
NULL);//获取选择率
numIndexTuples = btreeSelectivity * index->rel->tuples;//索引元组数目
numIndexTuples = rint(numIndexTuples / num_sa_scans);
}
MemSet(&costs, 0, sizeof(costs));
costs.numIndexTuples = numIndexTuples;
genericcostestimate(root, path, loop_count, qinfos, &costs);
if (index->tuples > 1)
{
descentCost = ceil(log(index->tuples) / log(2.0)) * cpu_operator_cost;
costs.indexStartupCost += descentCost;
costs.indexTotalCost += costs.num_sa_scans * descentCost;
}
descentCost = (index->tree_height + 1) * 50.0 * cpu_operator_cost;
costs.indexStartupCost += descentCost;
costs.indexTotalCost += costs.num_sa_scans * descentCost;
MemSet(&vardata, 0, sizeof(vardata));
if (index->indexkeys[0] != 0)
{
RangeTblEntry *rte = planner_rt_fetch(index->rel->relid, root);
Assert(rte->rtekind == RTE_RELATION);
relid = rte->relid;
Assert(relid != InvalidOid);
colnum = index->indexkeys[0];
if (get_relation_stats_hook &&
(*get_relation_stats_hook) (root, rte, colnum, &vardata))
{
if (HeapTupleIsValid(vardata.statsTuple) &&
!vardata.freefunc)
elog(ERROR, "no function provided to release variable stats with");
}
else
{
vardata.statsTuple = SearchSysCache3(STATRELATTINH,
ObjectIdGetDatum(relid),
Int16GetDatum(colnum),
BoolGetDatum(rte->inh));
vardata.freefunc = ReleaseSysCache;
}
}
else
{
relid = index->indexoid;
colnum = 1;
if (get_index_stats_hook &&
(*get_index_stats_hook) (root, relid, colnum, &vardata))
{
if (HeapTupleIsValid(vardata.statsTuple) &&
!vardata.freefunc)
elog(ERROR, "no function provided to release variable stats with");
}
else
{
vardata.statsTuple = SearchSysCache3(STATRELATTINH,
ObjectIdGetDatum(relid),
Int16GetDatum(colnum),
BoolGetDatum(false));
vardata.freefunc = ReleaseSysCache;
}
}
if (HeapTupleIsValid(vardata.statsTuple))
{
Oid sortop;
AttStatsSlot sslot;
sortop = get_opfamily_member(index->opfamily[0],
index->opcintype[0],
index->opcintype[0],
BTLessStrategyNumber);
if (OidIsValid(sortop) &&
get_attstatsslot(&sslot, vardata.statsTuple,
STATISTIC_KIND_CORRELATION, sortop,
ATTSTATSSLOT_NUMBERS))
{
double varCorrelation;
Assert(sslot.nnumbers == 1);
varCorrelation = sslot.numbers[0];
if (index->reverse_sort[0])
varCorrelation = -varCorrelation;
if (index->ncolumns > 1)
costs.indexCorrelation = varCorrelation * 0.75;
else
costs.indexCorrelation = varCorrelation;
free_attstatsslot(&sslot);
}
}
ReleaseVariableStats(vardata);
*indexStartupCost = costs.indexStartupCost;
*indexTotalCost = costs.indexTotalCost;
*indexSelectivity = costs.indexSelectivity;
*indexCorrelation = costs.indexCorrelation;
*indexPages = costs.numIndexPages;
}
//------------------------- index_pages_fetched
double
index_pages_fetched(double tuples_fetched, BlockNumber pages,
double index_pages, PlannerInfo *root)
{
double pages_fetched;
double total_pages;
double T,
b;
T = (pages > 1) ? (double) pages : 1.0;
total_pages = root->total_table_pages + index_pages;
total_pages = Max(total_pages, 1.0);
Assert(T <= total_pages);
b = (double) effective_cache_size * T / total_pages;
if (b <= 1.0)
b = 1.0;
else
b = ceil(b);
if (T <= b)
{
pages_fetched =
(2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched);
if (pages_fetched >= T)
pages_fetched = T;
else
pages_fetched = ceil(pages_fetched);
}
else
{
double lim;
lim = (2.0 * T * b) / (2.0 * T - b);
if (tuples_fetched <= lim)
{
pages_fetched =
(2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched);
}
else
{
pages_fetched =
b + (tuples_fetched - lim) * (T - b) / T;
}
pages_fetched = ceil(pages_fetched);
}
return pages_fetched;
}三、跟踪分析
测试脚本如下
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 create_index_path
Breakpoint 1 at 0x78f050: file pathnode.c, line 1037.
(gdb) c
Continuing.主要考察t_grxx上的索引访问路径,即t_grxx.dwbh = '1001'(通过等价类产生并下推的限制条件)
(gdb) c
Continuing.
Breakpoint 1, create_index_path (root=0x2737d70, index=0x274be80, indexclauses=0x274f1f8, indexclausecols=0x274f248,
indexorderbys=0x0, indexorderbycols=0x0, pathkeys=0x0, indexscandir=ForwardScanDirection, indexonly=false,
required_outer=0x0, loop_count=1, partial_path=false) at pathnode.c:1037
1037 IndexPath *pathnode = makeNode(IndexPath);索引信息:树高度为1/索引列1个/indexlist链表,元素为TargetEntry,相关信息为varno = 3, varattno = 1,索引访问方法成本估算使用的函数为btcostestimate
(gdb) p *index
$3 = {type = T_IndexOptInfo, indexoid = 16752, reltablespace = 0, rel = 0x274b870, pages = 276, tuples = 100000,
tree_height = 1, ncolumns = 1, nkeycolumns = 1, indexkeys = 0x274bf90, indexcollations = 0x274bfa8, opfamily = 0x274bfc0,
opcintype = 0x274bfd8, sortopfamily = 0x274bfc0, reverse_sort = 0x274c008, nulls_first = 0x274c020,
canreturn = 0x274bff0, relam = 403, indexprs = 0x0, indpred = 0x0, indextlist = 0x274c0f8, indrestrictinfo = 0x274dc58,
predOK = false, unique = false, immediate = true, hypothetical = false, amcanorderbyop = false, amoptionalkey = true,
amsearcharray = true, amsearchnulls = true, amhasgettuple = true, amhasgetbitmap = true, amcanparallel = true,
amcostestimate = 0x94f0ad <btcostestimate>}执行各项赋值操作
(gdb) n
1038 RelOptInfo *rel = index->rel;
(gdb)
1042 pathnode->path.pathtype = indexonly ? T_IndexOnlyScan : T_IndexScan;
(gdb)
1043 pathnode->path.parent = rel;
(gdb)
1044 pathnode->path.pathtarget = rel->reltarget;
(gdb)
1045 pathnode->path.param_info = get_baserel_parampathinfo(root, rel,
(gdb)
1047 pathnode->path.parallel_aware = false;
(gdb)
1048 pathnode->path.parallel_safe = rel->consider_parallel;
(gdb)
1049 pathnode->path.parallel_workers = 0;
(gdb)
1050 pathnode->path.pathkeys = pathkeys;
(gdb)
1053 expand_indexqual_conditions(index, indexclauses, indexclausecols,
(gdb)
1057 pathnode->indexinfo = index;执行expand_indexqual_conditions,给定RestrictInfo节点(约束条件),产生直接可用的索引表达式子句
(gdb) p *indexclauses
$4 = {type = T_List, length = 1, head = 0x274f1d8, tail = 0x274f1d8} -->t_grxx.dwbh = '1001'
(gdb) p *indexclausecols
$9 = {type = T_IntList, length = 1, head = 0x274f228, tail = 0x274f228}
(gdb) p indexclausecols->head->data.int_value
$10 = 0进入cost_index函数
(gdb)
1065 cost_index(pathnode, root, loop_count, partial_path);
(gdb) step
cost_index (path=0x274ecb8, root=0x2737d70, loop_count=1, partial_path=false) at costsize.c:480
480 IndexOptInfo *index = path->indexinfo;调用访问方法成本估算函数
...
(gdb)
547 amcostestimate(root, path, loop_count,
(gdb)
557 path->indextotalcost = indexTotalCost;相关返回值
(gdb) p indexStartupCost
$22 = 0.29249999999999998
(gdb) p indexTotalCost
$23 = 4.3675000000000006
(gdb) p indexSelectivity
$24 = 0.00010012021638664612
(gdb) p indexCorrelation
$25 = 0.82452213764190674
(gdb) p index_pages
$26 = 1loop_count=1
599 if (loop_count > 1)
(gdb)
651 (double) index->pages,
(gdb) p loop_count
$27 = 1取得的page数量,计算IO大小等
(gdb) n
649 pages_fetched = index_pages_fetched(tuples_fetched,
(gdb)
654 if (indexonly)
(gdb) p pages_fetched
$28 = 10
...
(gdb) p max_IO_cost
$30 = 40
(gdb) p min_IO_cost
$31 = 4调用完成,查看最终结果
749 path->path.total_cost = startup_cost + run_cost;
(gdb)
750 }
(gdb) p *path
$37 = {path = {type = T_IndexPath, pathtype = T_IndexScan, parent = 0x274b870, pathtarget = 0x274ba98, param_info = 0x0,
parallel_aware = false, parallel_safe = true, parallel_workers = 0, rows = 10, startup_cost = 0.29249999999999998,
total_cost = 19.993376803383146, pathkeys = 0x0}, indexinfo = 0x274be80, indexclauses = 0x274f1f8,
indexquals = 0x274f3a0, indexqualcols = 0x274f3f0, indexorderbys = 0x0, indexorderbycols = 0x0,
indexscandir = ForwardScanDirection, indextotalcost = 4.3675000000000006, indexselectivity = 0.00010012021638664612}
(gdb) n
create_index_path (root=0x2737d70, index=0x274be80, indexclauses=0x274f1f8, indexclausecols=0x274f248, indexorderbys=0x0,
indexorderbycols=0x0, pathkeys=0x0, indexscandir=ForwardScanDirection, indexonly=false, required_outer=0x0,
loop_count=1, partial_path=false) at pathnode.c:1067
1067 return pathnode;该SQL语句的执行计划,其中Index Scan using idx_t_grxx_dwbh on public.t_grxx t1 (cost=0.29..19.99...的成本0.29/19.99,与访问路径中的startup_cost/total_cost相对应.
testdb=# explain verbose 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;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.87..111.60 rows=10 width=37)
Output: a.dwmc, a.dwbh, a.dwdz, t1.grbh, t2.je, t1.dwbh
-> Nested Loop (cost=0.58..28.40 rows=10 width=29)
Output: a.dwmc, a.dwbh, a.dwdz, t1.grbh, t1.dwbh
-> Index Scan using t_dwxx_pkey on public.t_dwxx a (cost=0.29..8.30 rows=1 width=20)
Output: a.dwmc, a.dwbh, a.dwdz
Index Cond: ((a.dwbh)::text = '1001'::text)
-> Index Scan using idx_t_grxx_dwbh on public.t_grxx t1 (cost=0.29..19.99 rows=10 width=9)
Output: t1.dwbh, t1.grbh, t1.xm, t1.xb, t1.nl
Index Cond: ((t1.dwbh)::text = '1001'::text)
-> Index Scan using idx_t_jfxx_grbh on public.t_jfxx t2 (cost=0.29..8.31 rows=1 width=13)
Output: t2.grbh, t2.ny, t2.je
Index Cond: ((t2.grbh)::text = (t1.grbh)::text)
(13 rows)到此，相信大家对“PostgreSQL中create_index_path函数有什么作用”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
