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

Commitec10b61

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 parentbaa78ff commitec10b61

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
@@ -5358,7 +5358,7 @@ get_variable_range(PlannerInfo *root, VariableStatData *vardata,
53585358
*and fetching its low and/or high values.
53595359
*If successful, store values in *min and *max, and return true.
53605360
*(Either pointer can be NULL if that endpoint isn't needed.)
5361-
*Ifno data available, return false.
5361+
*Ifunsuccessful, return false.
53625362
*
53635363
* sortop is the "<" comparison operator to use.
53645364
* collation is the required collation.
@@ -5487,11 +5487,11 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
54875487
}
54885488
else
54895489
{
5490-
/* If min not requested,assume index is nonempty */
5490+
/* If min not requested,still want to fetch max */
54915491
have_data= true;
54925492
}
54935493

5494-
/* If max is requested, and we didn'tfind the index is empty */
5494+
/* If max is requested, and we didn'talready fail ... */
54955495
if (max&&have_data)
54965496
{
54975497
/* scan in the opposite direction; all else is the same */
@@ -5525,7 +5525,7 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
55255525

55265526
/*
55275527
* Get one endpoint datum (min or max depending on indexscandir) from the
5528-
* specified index. Return true if successful, false ifindex is empty.
5528+
* specified index. Return true if successful, false ifnot.
55295529
* On success, endpoint value is stored to *endpointDatum (and copied into
55305530
* outercontext).
55315531
*
@@ -5535,6 +5535,9 @@ get_actual_variable_range(PlannerInfo *root, VariableStatData *vardata,
55355535
* to probe the heap.
55365536
* (We could compute these values locally, but that would mean computing them
55375537
* twice when get_actual_variable_range needs both the min and the max.)
5538+
*
5539+
* Failure occurs either when the index is empty, or we decide that it's
5540+
* taking too long to find a suitable tuple.
55385541
*/
55395542
staticbool
55405543
get_actual_variable_endpoint(RelationheapRel,
@@ -5551,6 +5554,8 @@ get_actual_variable_endpoint(Relation heapRel,
55515554
SnapshotDataSnapshotNonVacuumable;
55525555
IndexScanDescindex_scan;
55535556
Buffervmbuffer=InvalidBuffer;
5557+
BlockNumberlast_heap_block=InvalidBlockNumber;
5558+
intn_visited_heap_pages=0;
55545559
ItemPointertid;
55555560
Datumvalues[INDEX_MAX_KEYS];
55565561
boolisnull[INDEX_MAX_KEYS];
@@ -5592,6 +5597,12 @@ get_actual_variable_endpoint(Relation heapRel,
55925597
* might get a bogus answer that's not close to the index extremal value,
55935598
* or could even be NULL. We avoid this hazard because we take the data
55945599
* from the index entry not the heap.
5600+
*
5601+
* Despite all this care, there are situations where we might find many
5602+
* non-visible tuples near the end of the index. We don't want to expend
5603+
* a huge amount of time here, so we give up once we've read too many heap
5604+
* pages. When we fail for that reason, the caller will end up using
5605+
* whatever extremal value is recorded in pg_statistic.
55955606
*/
55965607
InitNonVacuumableSnapshot(SnapshotNonVacuumable,RecentGlobalXmin);
55975608

@@ -5605,13 +5616,37 @@ get_actual_variable_endpoint(Relation heapRel,
56055616
/* Fetch first/next tuple in specified direction */
56065617
while ((tid=index_getnext_tid(index_scan,indexscandir))!=NULL)
56075618
{
5619+
BlockNumberblock=ItemPointerGetBlockNumber(tid);
5620+
56085621
if (!VM_ALL_VISIBLE(heapRel,
5609-
ItemPointerGetBlockNumber(tid),
5622+
block,
56105623
&vmbuffer))
56115624
{
56125625
/* Rats, we have to visit the heap to check visibility */
56135626
if (!index_fetch_heap(index_scan,tableslot))
5627+
{
5628+
/*
5629+
* No visible tuple for this index entry, so we need to
5630+
* advance to the next entry. Before doing so, count heap
5631+
* page fetches and give up if we've done too many.
5632+
*
5633+
* We don't charge a page fetch if this is the same heap page
5634+
* as the previous tuple. This is on the conservative side,
5635+
* since other recently-accessed pages are probably still in
5636+
* buffers too; but it's good enough for this heuristic.
5637+
*/
5638+
#defineVISITED_PAGES_LIMIT 100
5639+
5640+
if (block!=last_heap_block)
5641+
{
5642+
last_heap_block=block;
5643+
n_visited_heap_pages++;
5644+
if (n_visited_heap_pages>VISITED_PAGES_LIMIT)
5645+
break;
5646+
}
5647+
56145648
continue;/* no visible tuple, try next index entry */
5649+
}
56155650

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

0 commit comments

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