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

Commit9c6ad5e

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 parent0538d4c commit9c6ad5e

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
@@ -5964,7 +5964,7 @@ get_stats_slot_range(AttStatsSlot *sslot, Oid opfuncoid, FmgrInfo *opproc,
59645964
*and fetching its low and/or high values.
59655965
*If successful, store values in *min and *max, and return true.
59665966
*(Either pointer can be NULL if that endpoint isn't needed.)
5967-
*Ifno data available, return false.
5967+
*Ifunsuccessful, return false.
59685968
*
59695969
* sortop is the "<" comparison operator to use.
59705970
* collation is the required collation.
@@ -6093,11 +6093,11 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
60936093
}
60946094
else
60956095
{
6096-
/* If min not requested,assume index is nonempty */
6096+
/* If min not requested,still want to fetch max */
60976097
have_data= true;
60986098
}
60996099

6100-
/* If max is requested, and we didn'tfind the index is empty */
6100+
/* If max is requested, and we didn'talready fail ... */
61016101
if (max&&have_data)
61026102
{
61036103
/* scan in the opposite direction; all else is the same */
@@ -6131,7 +6131,7 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
61316131

61326132
/*
61336133
* Get one endpoint datum (min or max depending on indexscandir) from the
6134-
* specified index. Return true if successful, false ifindex is empty.
6134+
* specified index. Return true if successful, false ifnot.
61356135
* On success, endpoint value is stored to *endpointDatum (and copied into
61366136
* outercontext).
61376137
*
@@ -6141,6 +6141,9 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
61416141
* to probe the heap.
61426142
* (We could compute these values locally, but that would mean computing them
61436143
* twice when get_actual_variable_range needs both the min and the max.)
6144+
*
6145+
* Failure occurs either when the index is empty, or we decide that it's
6146+
* taking too long to find a suitable tuple.
61446147
*/
61456148
staticbool
61466149
get_actual_variable_endpoint(RelationheapRel,
@@ -6157,6 +6160,8 @@ get_actual_variable_endpoint(Relation heapRel,
61576160
SnapshotDataSnapshotNonVacuumable;
61586161
IndexScanDescindex_scan;
61596162
Buffervmbuffer=InvalidBuffer;
6163+
BlockNumberlast_heap_block=InvalidBlockNumber;
6164+
intn_visited_heap_pages=0;
61606165
ItemPointertid;
61616166
Datumvalues[INDEX_MAX_KEYS];
61626167
boolisnull[INDEX_MAX_KEYS];
@@ -6199,6 +6204,12 @@ get_actual_variable_endpoint(Relation heapRel,
61996204
* might get a bogus answer that's not close to the index extremal value,
62006205
* or could even be NULL. We avoid this hazard because we take the data
62016206
* from the index entry not the heap.
6207+
*
6208+
* Despite all this care, there are situations where we might find many
6209+
* non-visible tuples near the end of the index. We don't want to expend
6210+
* a huge amount of time here, so we give up once we've read too many heap
6211+
* pages. When we fail for that reason, the caller will end up using
6212+
* whatever extremal value is recorded in pg_statistic.
62026213
*/
62036214
InitNonVacuumableSnapshot(SnapshotNonVacuumable,
62046215
GlobalVisTestFor(heapRel));
@@ -6213,13 +6224,37 @@ get_actual_variable_endpoint(Relation heapRel,
62136224
/* Fetch first/next tuple in specified direction */
62146225
while ((tid=index_getnext_tid(index_scan,indexscandir))!=NULL)
62156226
{
6227+
BlockNumberblock=ItemPointerGetBlockNumber(tid);
6228+
62166229
if (!VM_ALL_VISIBLE(heapRel,
6217-
ItemPointerGetBlockNumber(tid),
6230+
block,
62186231
&vmbuffer))
62196232
{
62206233
/* Rats, we have to visit the heap to check visibility */
62216234
if (!index_fetch_heap(index_scan,tableslot))
6235+
{
6236+
/*
6237+
* No visible tuple for this index entry, so we need to
6238+
* advance to the next entry. Before doing so, count heap
6239+
* page fetches and give up if we've done too many.
6240+
*
6241+
* We don't charge a page fetch if this is the same heap page
6242+
* as the previous tuple. This is on the conservative side,
6243+
* since other recently-accessed pages are probably still in
6244+
* buffers too; but it's good enough for this heuristic.
6245+
*/
6246+
#defineVISITED_PAGES_LIMIT 100
6247+
6248+
if (block!=last_heap_block)
6249+
{
6250+
last_heap_block=block;
6251+
n_visited_heap_pages++;
6252+
if (n_visited_heap_pages>VISITED_PAGES_LIMIT)
6253+
break;
6254+
}
6255+
62226256
continue;/* no visible tuple, try next index entry */
6257+
}
62236258

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

0 commit comments

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