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

Commit6e63926

Browse files
committed
YA attempt at taming worst-case behavior of get_actual_variable_range.
We've made multiple attempts at preventing get_actual_variable_rangefrom taking an unreasonable amount of time (3ca930f,fccebe4).But there's still an issue for the very first planning attempt afterdeletion of a large number of extremal-valued tuples. While thatplanning attempt will set "killed" bits on the tuples it visits andthereby reduce effort for next time, there's still a lot of work ithas to do to visit the heap and then set those bits. It's (usually?)not worth it to do that much work at plan time to have a slightlybetter estimate, especially in a context like this where the tablecontents are known to be mutating rapidly.Therefore, let's bound the amount of work to be done by giving upafter we've visited 100 heap pages. Giving up just means we'llfall back on the extremal value recorded in pg_statistic, so itshouldn't mean that planner estimates suddenly become worthless.Note that this means we'll still gradually whittle down the problemby setting a few more index "killed" bits in each planning attempt;so eventually we'll reach a good state (barring further deletions),even in the absence of VACUUM.Simon Riggs, per a complaint from Jakub Wartak (with cosmeticadjustments by me). Back-patch to all supported branches.Discussion:https://postgr.es/m/CAKZiRmznOwi0oaV=4PHOCM4ygcH4MgSvt8=5cu_vNCfc8FSUug@mail.gmail.com
1 parent4b9013d commit6e63926

File tree

1 file changed

+40
-5
lines changed

1 file changed

+40
-5
lines changed

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

Lines changed: 40 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5609,7 +5609,7 @@ get_stats_slot_range(AttStatsSlot *sslot, Oid opfuncoid, FmgrInfo *opproc,
56095609
*and fetching its low and/or high values.
56105610
*If successful, store values in *min and *max, and return true.
56115611
*(Either pointer can be NULL if that endpoint isn't needed.)
5612-
*Ifno data available, return false.
5612+
*Ifunsuccessful, return false.
56135613
*
56145614
* sortop is the "<" comparison operator to use.
56155615
* collation is the required collation.
@@ -5738,11 +5738,11 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
57385738
}
57395739
else
57405740
{
5741-
/* If min not requested,assume index is nonempty */
5741+
/* If min not requested,still want to fetch max */
57425742
have_data= true;
57435743
}
57445744

5745-
/* If max is requested, and we didn'tfind the index is empty */
5745+
/* If max is requested, and we didn'talready fail ... */
57465746
if (max&&have_data)
57475747
{
57485748
/* scan in the opposite direction; all else is the same */
@@ -5776,7 +5776,7 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
57765776

57775777
/*
57785778
* Get one endpoint datum (min or max depending on indexscandir) from the
5779-
* specified index. Return true if successful, false ifindex is empty.
5779+
* specified index. Return true if successful, false ifnot.
57805780
* On success, endpoint value is stored to *endpointDatum (and copied into
57815781
* outercontext).
57825782
*
@@ -5786,6 +5786,9 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
57865786
* to probe the heap.
57875787
* (We could compute these values locally, but that would mean computing them
57885788
* twice when get_actual_variable_range needs both the min and the max.)
5789+
*
5790+
* Failure occurs either when the index is empty, or we decide that it's
5791+
* taking too long to find a suitable tuple.
57895792
*/
57905793
staticbool
57915794
get_actual_variable_endpoint(RelationheapRel,
@@ -5802,6 +5805,8 @@ get_actual_variable_endpoint(Relation heapRel,
58025805
SnapshotDataSnapshotNonVacuumable;
58035806
IndexScanDescindex_scan;
58045807
Buffervmbuffer=InvalidBuffer;
5808+
BlockNumberlast_heap_block=InvalidBlockNumber;
5809+
intn_visited_heap_pages=0;
58055810
ItemPointertid;
58065811
Datumvalues[INDEX_MAX_KEYS];
58075812
boolisnull[INDEX_MAX_KEYS];
@@ -5843,6 +5848,12 @@ get_actual_variable_endpoint(Relation heapRel,
58435848
* might get a bogus answer that's not close to the index extremal value,
58445849
* or could even be NULL. We avoid this hazard because we take the data
58455850
* from the index entry not the heap.
5851+
*
5852+
* Despite all this care, there are situations where we might find many
5853+
* non-visible tuples near the end of the index. We don't want to expend
5854+
* a huge amount of time here, so we give up once we've read too many heap
5855+
* pages. When we fail for that reason, the caller will end up using
5856+
* whatever extremal value is recorded in pg_statistic.
58465857
*/
58475858
InitNonVacuumableSnapshot(SnapshotNonVacuumable,RecentGlobalXmin);
58485859

@@ -5856,13 +5867,37 @@ get_actual_variable_endpoint(Relation heapRel,
58565867
/* Fetch first/next tuple in specified direction */
58575868
while ((tid=index_getnext_tid(index_scan,indexscandir))!=NULL)
58585869
{
5870+
BlockNumberblock=ItemPointerGetBlockNumber(tid);
5871+
58595872
if (!VM_ALL_VISIBLE(heapRel,
5860-
ItemPointerGetBlockNumber(tid),
5873+
block,
58615874
&vmbuffer))
58625875
{
58635876
/* Rats, we have to visit the heap to check visibility */
58645877
if (!index_fetch_heap(index_scan,tableslot))
5878+
{
5879+
/*
5880+
* No visible tuple for this index entry, so we need to
5881+
* advance to the next entry. Before doing so, count heap
5882+
* page fetches and give up if we've done too many.
5883+
*
5884+
* We don't charge a page fetch if this is the same heap page
5885+
* as the previous tuple. This is on the conservative side,
5886+
* since other recently-accessed pages are probably still in
5887+
* buffers too; but it's good enough for this heuristic.
5888+
*/
5889+
#defineVISITED_PAGES_LIMIT 100
5890+
5891+
if (block!=last_heap_block)
5892+
{
5893+
last_heap_block=block;
5894+
n_visited_heap_pages++;
5895+
if (n_visited_heap_pages>VISITED_PAGES_LIMIT)
5896+
break;
5897+
}
5898+
58655899
continue;/* no visible tuple, try next index entry */
5900+
}
58665901

58675902
/* We don't actually need the heap tuple for anything */
58685903
ExecClearTuple(tableslot);

0 commit comments

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