Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcd9479a

Browse files
committed
Improve GIN cost estimation
GIN index scans were not taking any descent CPU-based cost into account. Thatmade them look cheaper than other types of indexes when they shouldn't be.We use the same heuristic as for btree indexes, but multiply it by the numberof searched entries.Additionally, the CPU cost for the tree was based largely on agenericcostestimate. For a GIN index, we should not charge index quals pertuple, but per entry. On top of this, charge cpu_index_tuple_cost per actualtuple.This should fix the cases where a GIN index is preferred over a btree andthe ones where a memoize node is not added on top of the GIN index scanbecause it seemed too cheap.We don't packpatch this to evade unexpected plan changes in stable versions.Discussion:https://postgr.es/m/CABs3KGQnOkyQ42-zKQqiE7M0Ks9oWDSee%3D%2BJx3-TGq%3D68xqWYw%40mail.gmail.comDiscussion:https://postgr.es/m/3188617.44csPzL39Z%40aivenronanAuthor: Ronan DunklauReported-By: Hung NguyenReviewed-by: Tom Lane, Alexander Korotkov
1 parenteb5c4e9 commitcd9479a

File tree

1 file changed

+64
-5
lines changed

1 file changed

+64
-5
lines changed

‎src/backend/utils/adt/selfuncs.c

Lines changed: 64 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7453,6 +7453,7 @@ gincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
74537453
qual_arg_cost,
74547454
spc_random_page_cost,
74557455
outer_scans;
7456+
CostdescentCost;
74567457
RelationindexRel;
74577458
GinStatsDataginStats;
74587459
ListCell*lc;
@@ -7677,6 +7678,47 @@ gincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
76777678
*/
76787679
dataPagesFetched=ceil(numDataPages*partialScale);
76797680

7681+
*indexStartupCost=0;
7682+
*indexTotalCost=0;
7683+
7684+
/*
7685+
* Add a CPU-cost component to represent the costs of initial entry btree
7686+
* descent. We don't charge any I/O cost for touching upper btree levels,
7687+
* since they tend to stay in cache, but we still have to do about log2(N)
7688+
* comparisons to descend a btree of N leaf tuples. We charge one
7689+
* cpu_operator_cost per comparison.
7690+
*
7691+
* If there are ScalarArrayOpExprs, charge this once per SA scan. The
7692+
* ones after the first one are not startup cost so far as the overall
7693+
* plan is concerned, so add them only to "total" cost.
7694+
*/
7695+
if (numEntries>1)/* avoid computing log(0) */
7696+
{
7697+
descentCost=ceil(log(numEntries) /log(2.0))*cpu_operator_cost;
7698+
*indexStartupCost+=descentCost*counts.searchEntries;
7699+
*indexTotalCost+=counts.arrayScans*descentCost*counts.searchEntries;
7700+
}
7701+
7702+
/*
7703+
* Add a cpu cost per entry-page fetched. This is not amortized over a
7704+
* loop.
7705+
*/
7706+
*indexStartupCost+=entryPagesFetched*DEFAULT_PAGE_CPU_MULTIPLIER*cpu_operator_cost;
7707+
*indexTotalCost+=entryPagesFetched*counts.arrayScans*DEFAULT_PAGE_CPU_MULTIPLIER*cpu_operator_cost;
7708+
7709+
/*
7710+
* Add a cpu cost per data-page fetched. This is also not amortized over a
7711+
* loop. Since those are the data pages from the partial match algorithm,
7712+
* charge them as startup cost.
7713+
*/
7714+
*indexStartupCost+=DEFAULT_PAGE_CPU_MULTIPLIER*cpu_operator_cost*dataPagesFetched;
7715+
7716+
/*
7717+
* Since we add the startup cost to the total cost later on, remove the
7718+
* initial arrayscan from the total.
7719+
*/
7720+
*indexTotalCost+=dataPagesFetched* (counts.arrayScans-1)*DEFAULT_PAGE_CPU_MULTIPLIER*cpu_operator_cost;
7721+
76807722
/*
76817723
* Calculate cache effects if more than one scan due to nestloops or array
76827724
* quals. The result is pro-rated per nestloop scan, but the array qual
@@ -7700,7 +7742,7 @@ gincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
77007742
* Here we use random page cost because logically-close pages could be far
77017743
* apart on disk.
77027744
*/
7703-
*indexStartupCost= (entryPagesFetched+dataPagesFetched)*spc_random_page_cost;
7745+
*indexStartupCost+= (entryPagesFetched+dataPagesFetched)*spc_random_page_cost;
77047746

77057747
/*
77067748
* Now compute the number of data pages fetched during the scan.
@@ -7728,6 +7770,15 @@ gincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
77287770
if (dataPagesFetchedBySel>dataPagesFetched)
77297771
dataPagesFetched=dataPagesFetchedBySel;
77307772

7773+
/* Add one page cpu-cost to the startup cost */
7774+
*indexStartupCost+=DEFAULT_PAGE_CPU_MULTIPLIER*cpu_operator_cost*counts.searchEntries;
7775+
7776+
/*
7777+
* Add once again a CPU-cost for those data pages, before amortizing for
7778+
* cache.
7779+
*/
7780+
*indexTotalCost+=dataPagesFetched*counts.arrayScans*DEFAULT_PAGE_CPU_MULTIPLIER*cpu_operator_cost;
7781+
77317782
/* Account for cache effects, the same as above */
77327783
if (outer_scans>1||counts.arrayScans>1)
77337784
{
@@ -7739,19 +7790,27 @@ gincostestimate(PlannerInfo *root, IndexPath *path, double loop_count,
77397790
}
77407791

77417792
/* And apply random_page_cost as the cost per page */
7742-
*indexTotalCost=*indexStartupCost+
7793+
*indexTotalCost+=*indexStartupCost+
77437794
dataPagesFetched*spc_random_page_cost;
77447795

77457796
/*
7746-
* Add on index qual eval costs, much as in genericcostestimate. But we
7747-
* can disregard indexorderbys, since GIN doesn't support those.
7797+
* Add on index qual eval costs, much as in genericcostestimate. We charge
7798+
* cpu but we can disregard indexorderbys, since GIN doesn't support
7799+
* those.
77487800
*/
77497801
qual_arg_cost=index_other_operands_eval_cost(root,indexQuals);
77507802
qual_op_cost=cpu_operator_cost*list_length(indexQuals);
77517803

77527804
*indexStartupCost+=qual_arg_cost;
77537805
*indexTotalCost+=qual_arg_cost;
7754-
*indexTotalCost+= (numTuples**indexSelectivity)* (cpu_index_tuple_cost+qual_op_cost);
7806+
7807+
/*
7808+
* Add a cpu cost per search entry, corresponding to the actual visited
7809+
* entries.
7810+
*/
7811+
*indexTotalCost+= (counts.searchEntries*counts.arrayScans)* (qual_op_cost);
7812+
/* Now add a cpu cost per tuple in the posting lists / trees */
7813+
*indexTotalCost+= (numTuples**indexSelectivity)* (cpu_index_tuple_cost);
77557814
*indexPages=dataPagesFetched;
77567815
}
77577816

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp