Commits on Aug 22, 2026
-
Add slot-based table AM index scan interface.
Add table_index_getnext_slot, a new table AM interface that wraps both plain and index-only index scans that use amgettuple. Two new heapam callbacks are introduced -- one for plain scans and one for index-only scans -- which an upcoming commit that adds the amgetbatch interface will expand to four. The appropriate callback is resolved once in index_scan_begin, and called through a function pointer on the IndexScanDesc (xs_getnext_slot) when the table_index_getnext_slot shim function is called from executor nodes. That way table AMs can create specialized variants to help the compiler produce more efficient code (but they should always be able to provide exactly one generic callback, if that makes sense). This moves VM checks for index-only scans out of the executor and into heapam, enabling batching of visibility map lookups (though for now we continue to just perform retail lookups). Using the new higher level slot-based interface greatly simplifies nodeIndexonlyscan.c, which no longer has to deal with the visibility map directly. More importantly, this is a significant architectural improvement: table AMs can now implement index-only scans that are not tied to heapam's visibility map. A small minority of callers (2 callers in total) fundamentally need to pass a TID to the table AM (both perform constraint enforcement). These callers don't actually perform index scans (even if their TIDs are taken from an index), and have no need for most of the index scan machinery. Switch these callers over to the new fetch_tid interface. All true index scan callers now use the new slot-based interface. (Note that fetch_tid doesn't perform on-access pruning, but that doesn't seem desirable anyway; an index page buffer lock will often be held.) The VISITED_PAGES_LIMIT mechanism used by get_actual_variable_range to cap scan overhead during planning is reworked to go through a new scan descriptor interface (xs_visited_pages_limit), rather than tracking the costs directly and terminating the scan itself, in an ad-hoc way. This is necessary because callers that use the new slot-based interface no longer have direct access to which heap blocks were fetched. Similarly, nodeIndexonlyscan.c can no longer use InstrCountTuples2 to count heap fetches during an EXPLAIN ANALYZE. EXPLAIN ANALYZE now obtains this information from a new IndexScanInstrumentation field, which table AMs are required to maintain. Though independently useful, this commit is preparatory work for an upcoming commit that will add an amgetbatch index AM interface, where the table AM takes full responsibility for managing the progress of index scans. That will move most of the implementation of scrollable cursors out of index AMs and into table AMs, making it essential that executor nodes pass the current scan direction down to the table AM. The heapam table_index_getnext_slot callbacks make aggressive use of forced inlining to ensure that plain and index-only code paths are fully specialized at compile time despite sharing a common implementation. Testing has shown this is necessary to keep icache misses to a minimum, at least with the two upcoming amgetbatch variants. Author: Peter Geoghegan <pg@bowt.ie> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Tomas Vondra <tomas@vondra.me> Discussion: https://postgr.es/m/CAH2-WzmYqhacBH161peAWb5eF=Ja7CFAQ+0jSEMq=qnfLVTOOg@mail.gmail.com
authored and Commitfest Bot committed
Aug 22, 2026 -
Limit get_actual_variable_range leaf page reads.
get_actual_variable_range scans an index to find actual min/max values for planner selectivity estimation. Since this happens during planning, we can't afford to spend too much time on it. Commit 9c6ad5e added VISITED_PAGES_LIMIT (a limit of 100 heap page visits) to bound the amount of work performed, giving up and falling back to the pg_statistic extremal value when the limit is exceeded. But that isn't effective in cases with more extreme concentrations of dead index tuples. Benchmark results from Mark Callaghan show that VISITED_PAGES_LIMIT stops being effective once the dead index tuple problem gets out of hand (which is expected with queue-like tables that continually delete older records and insert newer ones). VISITED_PAGES_LIMIT counts heap page visits, but when many index tuples are marked LP_DEAD, _bt_readpage traverses arbitrarily many index pages without returning any tuples. The heap page counter never gets a chance to increment, so VISITED_PAGES_LIMIT never triggers. The more LP_DEAD bits we set, the less effective the limit becomes at bailing out early. Add a complementary mechanism that limits get_actual_variable_range to scanning only three index leaf pages (INDEX_PAGES_LIMIT-many pages) that have exactly zero matching items. When the limit is exceeded, the scan returns without returning any matches, forcing get_actual_variable_range to give up. INDEX_PAGES_LIMIT provides a backstop against reading an excessive number of leaf pages, without fundamentally altering the existing VISITED_PAGES_LIMIT design. Leaf page reads that find at least one matching item aren't tallied against the new limit. This balances the need for get_actual_variable_range to locate a min/max value when that's feasible against the need to bound the amount of work it must perform to do so. The first leaf page read isn't counted against INDEX_PAGES_LIMIT, so as to avoid adding handling to _bt_readfirstpage. Only _bt_readnextpage tallies the number of pages read, to avoid adding any overhead to simple point queries. XXX Once "Add slot-based table AM index scan interface" is committed, this patch can be spun off into its own independent project, tracked through another CF entry. Author: Peter Geoghegan <pg@bowt.ie> Discussion: https://postgr.es/m/CAH2-Wzkt1WkKp4VRJu3qHfmKXc8W+XYv1RXg5d2d3fSvAeO=rg@mail.gmail.com
-
Add amgetbatch interface and adopt it in nbtree.
Add a new amgetbatch index AM interface that allows index access methods to implement plain index scans and index-only scans that return index entries in batches comprising all matching items from an index page, rather than one match at a time. Also switch nbtree over from amgettuple to the new amgetbatch interface. The new interface allows the table AM to apply knowledge of which TIDs will be returned to the scan in the near future to perform optimizations like I/O prefetching. Prefetching is set to be added by an upcoming commit. With amgetbatch, a scan-level policy determines whether each batch's index page buffer pin is dropped eagerly by the index AM (for plain scans with an MVCC snapshot, where the snapshot itself prevents TID recycling problems) or retained as an interlock against concurrent TID recycling by VACUUM. The interlock is retained for plain non-MVCC scans and for index-only scans, and is dropped by the table AM via the new amunguardbatch callback when it is safe to do so. (Actually, index AMs are usually able to drop the pin at the same time that they release the lock. In practice, the amunguardbatch callback is only really needed during index-only scans, where dropping the pin interlock might need to be delayed ever so slightly, as explained below.) This extends the dropPin mechanism added to nbtree by commit 2ed5b87 , and generalizes it to work with all index AMs that support the new amgetbatch interface (LP_DEAD marking of index entries must be performed by implementing the new amkillitemsbatch callback, which has a documented contract describing how index AMs must reason about concurrent TID recycling). Scans can always safely drop index page pins eagerly, provided the scan uses an MVCC snapshot (unlike the nbtree dropPin optimization, which had no way of doing this safely during index-only scans due to how amgettuple works, and only gained support for scans of unlogged relations in recent commit 8a87911 ). The old ammarkpos and amrestrpos index AM callbacks are removed. With amgetbatch, mark/restore of scan positions is managed by the table AM, with help from indexbatch.c utility functions, rather than being wholly delegated to the index AM. The new index_scan_markpos and index_scan_restrpos table AM callbacks must be implemented to make all this work. As a further condition, mark/restore is only supported by index AMs that opt in by setting the new amcanmarkpos flag (only nbtree sets this to true). This amcanmarkpos flag scheme avoids the assumption that every index AM is capable of picking up a scan from a previously saved markBatch. An upcoming commit that will add index prefetching will use a read stream to read heap pages during index scans. Read stream is careful to limit how many things it pins, lest we run into problems due to having too many buffers pinned. Simply never holding on to index page buffer pins greatly simplifies resource management for index prefetching; there's no risk of unintended interactions between the read stream and index AM. The only downside is that we cannot support prefetching during scans that use a non-MVCC snapshot, which seems quite acceptable. In practice, heapam doesn't drop each batch's index page buffer pin at the earliest opportunity during index-only scans. This was deemed necessary to avoid regressing index-only scans with a LIMIT, in particular with nestloop anti-joins and nestloop semi-joins; eagerly loading all the visibility information up front regressed such queries. The new amgetbatch interface gives table AMs the authority to decide when to drop index page pins/unguard batches, so this can be considered a heapam implementation detail (index AMs don't need to know about it). This scheme enables index prefetching to acquire and then drop any extra batch index page pin within its read stream callback -- even when an index-only scan (that must perform some heap fetches) holds open several index batches at once in order to maintain an adequate prefetch distance. The read stream cannot observe any change in the backend's buffer pin limit. Index access methods that support plain index scans must now implement either the amgetbatch interface or the amgettuple interface (not both). Upcoming patches will add support for amgetbatch to the hash, GiST, and SP-GiST index AMs. Author: Tomas Vondra <tomas@vondra.me> Author: Peter Geoghegan <pg@bowt.ie> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Thomas Munro <thomas.munro@gmail.com> Discussion: https://postgr.es/m/cf85f46f-b02f-05b2-5248-5000b894ebab@enterprisedb.com Discussion: https://postgr.es/m/efac3238-6f34-41ea-a393-26cc0441b506%40vondra.me
-
Adopt amgetbatch interface in hash index AM.
Replace hashgettuple with hashgetbatch, a function that implements the new amgetbatch interface added by commit FIXME. Plain index scans of hash indexes now return matching items in batches consisting of all of the matches from a given bucket page or overflow page. This gives the table AM the ability to perform optimizations like index prefetching during hash index scans. The amgetbatch interface requires that index AMs take the same standardized approach to pin management for pins that are used to prevent unsafe concurrent TID recycling by VACUUM (that way prefetching can hold open multiple batches without it affecting the read stream). Note, however, that hash still holds on to pins needed for its own internal purposes (e.g., it'll still hold onto a pin during a bucket split). hashkillitemsbatch (the hash implementation of the new amkillitemsbatch interface) performs LP_DEAD marking of dead index entries, while following slightly different rules to the old approach. It relies on comparing the batch's saved LSN against the current page LSN to detect concurrent page modifications, which in turn requires fake LSN support for unlogged relations. Preparatory commit e5836f7 added that support to the hash index AM. TODO: Integrate upstream bucket split bug fix: https://postgr.es/m/CAH2-Wz=4CJK9ysZzbzxBGGTkgAg9ib8cJyCx=9q_d1r8tf0ang@mail.gmail.com Author: Peter Geoghegan <pg@bowt.ie> Reviewed-by: Tomas Vondra <tomas@vondra.me> Reviewed-by: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/CAH2-WzmYqhacBH161peAWb5eF=Ja7CFAQ+0jSEMq=qnfLVTOOg@mail.gmail.com
-
WIP: Adopt amgetbatch interface in GiST index AM.
Replace gistgettuple with gistgetbatch, a function that implements the new amgetbatch interface added by commit FIXME. Plain index scans of GiST indexes now return matching items in batches consisting of all of the matches from a given leaf page. This gives the table AM the ability to perform optimizations like index prefetching during GiST index scans. The amgetbatch interface requires that index AMs take the same standardized approach to pin management for pins that are used to prevent unsafe concurrent TID recycling by VACUUM (that way prefetching can hold open multiple batches without it affecting the read stream). For an ordinary GiST batch this interlock pin is the pin on its single leaf page, held only for as long as the table AM still needs it as an interlock (just like during nbtree and hash scans). Nearest-neighbor (ordered) scans are handled quite differently, because their matches don't naturally arrive one leaf page at a time. Here gistgetbatch instead drains the scan's distance-ordered pairing heap, packing the matching leaf items into a single "virtual" batch in distance order, typically spanning many leaf pages. We're effectively pretending that the matches we found were in useful order, together on the same leaf page -- though that isn't really true. Virtual batches come with restrictions that make the pretense safe: an ordered scan is never planned as an index-only scan, and gistkillitemsbatch does nothing for a virtual batch. A virtual batch therefore never holds a TID recycling interlock pin at all; the pin on each underlying leaf page is instead dropped right away, as the page is scanned into the queue. The interlock pin also fixes a pre-existing bug in which GiST index-only scans could return wrong answers [1]. An index-only scan trusts the visibility map instead of fetching the heap tuple, so it must keep VACUUM from recycling a heap TID between the moment it reads an index entry and the moment it consults the visibility map; otherwise it can report indexed values that belong to an unrelated, since-recycled heap tuple. The retained leaf-page buffer pin is that interlock -- but only if VACUUM honors it. gistvacuumpage therefore now acquires a cleanup lock on each page (rather than a plain exclusive lock), so a concurrent scan's pin holds VACUUM off from recycling that page's TIDs until the scan has finished its visibility checks. This same interlock requirement is why ordered scans cannot be index-only: a virtual batch drops each leaf page's pin as soon as the page is scanned, so it has no bounded pin to offer as the recycling interlock that an index-only scan depends on. Rather than work around that (which seems prohibitively complicated), the planner never builds an index-only scan that uses ordering operators; ordered scans must be plain index scans, which fetch and recheck the heap tuple and so were never subject to the bug. This warrants an incompatibility item in the Postgres 20 release notes (note that both GiST and SP-GiST are affected). The gistgetbatch implementation makes use of new batch-related core infrastructure. GiST now registers an amgettransform callback, which sets the scan descriptor's per-tuple recheck flags. It also sets order-by distances, and reconstructs a heap tuple for index-only scans. It is called just before table_index_getnext_slot returns another tuple. Like nbtree, the scan uses a currTuples storage area to store IndexTuple structs in their original on-disk representation. Unlike nbtree, GiST uses amgettransform to convert the representation of the tuples into a heap tuple representation of the underlying indexed type. This scheme also relies on a new facility that allows index AMs to request their own separate dynamically sized area for supplemental metadata (GiST opclasses have the ability to represent that any tuple needs a recheck, so we have to shuttle that information around with the batch). [1] https://postgr.es/m/CAH2-Wz=jjiNL9FCh8C1L-GUH15f4WFTWub2x+_NucngcDDcHKw@mail.gmail.com Author: Peter Geoghegan <pg@bowt.ie>
-
WIP: Adopt amgetbatch interface in SP-GiST index AM.
Replace spggettuple with spggetbatch, which implements the amgetbatch interface added by commit FIXME. Plain index scans of SP-GiST indexes now return matching items in batches consisting of all of the matches from a given leaf page, giving the table AM the ability to perform optimizations like index prefetching during SP-GiST index scans. As in nbtree, hash, and GiST, an ordinary batch's only retained buffer pin is the one on its single leaf page, held as the standardized interlock against unsafe concurrent TID recycling by VACUUM, for as long as the table AM still needs it. Nearest-neighbor (ordered) scans work as in GiST: spggetbatch drains the distance-ordered queue into one "virtual" batch spanning many leaf pages. The interlock pin also fixes a pre-existing bug in which SP-GiST index-only scans could return wrong answers. This is exactly the same race condition that commit FIXME (which taught GiST to use the amgetbatch interface) fixed in GiST. As with GiST, we rely on the planner disallowing ordered SP-GiST scans to close the gap there (SP-GiST also uses "virtual batches" during ordered scans, which make a conventional leaf page pin interlock impractical, just like in GiST). There is an additional restriction on index-only scans, which is a separate issue that is peculiar to SP-GiST: index-only scans are now disabled for "long values" opclasses such as the text radix opclass. These opclasses use reconstructed values whose size is essentially unbounded. The prefix cannot reliably fit into a fixed per-batch reconstruction workspace. There doesn't appear to be a simple way to solve that resource management problem within the confines of the amgetbatch design, and inventing new infrastructure to make it work doesn't seem likely to pay for itself. This warrants a separate SP-GiST only incompatibility item in the Postgres 20 release notes (in addition to an item about GiST _and_ SP-GiST not supporting ordered index-only scans anymore). Author: Peter Geoghegan <pg@bowt.ie>
-
heapam: Optimize pin transfers during index scans.
Add an xs_lastinblock flag to IndexScanHeapData, to track whether the current item's heap block differs from the next item's heap block ("next" in terms of the current scan direction). When these adjacent blocks differ, heapam_index_heap_fetch will transfer its buffer pin to its table slot instead of incrementing the pin count. This avoids an immediate IncrBufferRefCount call. It also avoids a ReleaseBuffer call later on, during the next call to heapam_index_heap_fetch (when the scan has to return the aforementioned "next" item). Also add an explicit ExecClearTuple to the block-switch path in heapam_index_heap_fetch to release the pin on the slot (which is often the pin transferred to the slot during the previous call). This fixes a performance problem where GetPrivateRefCountEntrySlow is called more often than one would hope. The underlying issue has been tied to the pin in the slot being held, even if we decide to release the buffer and move on: ExecStoreBufferHeapTuple will first fail to hit the backend-local cache for the release of the old pin (because we just pinned and locked the new buffer), causing a cache miss. Author: Peter Geoghegan <pg@bowt.ie> Suggested-by: Andres Freund <andres@anarazel.de> Reviewed-by: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/CAH2-Wz=D4Lru9BkvqaRnFRPDaZbfTOdWcxw13zyG6GVFTtz_vw@mail.gmail.com
-
heapam: Add index scan I/O prefetching.
This commit implements I/O prefetching for index scans (and index-only scans that require heap fetches). This was made possible by the recent addition of batching interfaces to both the table AM and index AM APIs. The amgetbatch index AM interface provides batches of matching TIDs (rather than one tuple at a time), each of which must be taken from index tuples that appear together on a single index page. This allows multiple batches to be held open simultaneously. Giving the table AM an explicit understanding of index AM concepts/index page boundaries allows it to consider all of the relevant costs and benefits. Prefetching is implemented using a prefetching position under the control of the table AM. This is closely related to the scan position added by commit FIXME, which introduced the amgetbatch interface. A read stream callback advances the read stream as needed to provide sufficiently many heap block numbers to maintain the read stream's target prefetch distance. Testing has shown that index prefetching can make index scans much faster. Large range scans that return many tuples can be as much as 30x faster with local SSDs when buffered I/O is used, and 50x faster or more with higher-latency storage such as network-attached block devices, where the benefit of hiding I/O latency through prefetching is even greater. An important goal of the amgetbatch design is to enable the table AM's read stream callback to advance its prefetch position using TIDs that appear on a leaf page that's ahead of the current scan position's leaf page. This is crucial with scans of indexes where each leaf page happens to have relatively few distinct heap blocks among its matching TIDs (as well as with scans with leaf pages that have relatively few total matching items). Index scans can have as many as 64 open batches, which testing has shown to be about the maximum number that can ever be useful. Batches are maintained in scan order using a simple ring buffer data structure. In rare cases where the scan exceeds this quasi-arbitrary limit of 64, the read stream is temporarily paused using the read stream pausing mechanism added by commit 38229cb . Prefetching (via the read stream) is resumed only after the scan position advances beyond its current open batch and then frees and removes the batch from the scan's batch ring buffer. Testing has shown that it isn't very common for scans to hold open more than about 10 batches to get the desired I/O prefetch distance. The heuristic used to decide when to begin prefetching delays initialization of the scan's read stream until the scan must read a fourth heap page. Note that the rule is the same for index-only scans. As a result, index-only scans won't create a read stream whenever they require no (or only very few) heap fetches. A new GUC (enable_indexscan_prefetch) controls the use of index prefetching. The default setting is 'on', so all amgetbatch index scans use prefetching. Index-only scans apply the usual "start prefetching on the fourth heap page" test to gate prefetching, and so will never create a read stream in cases where all (or almost all) relevant visibility map bits are set. Author: Tomas Vondra <tomas@vondra.me> Author: Peter Geoghegan <pg@bowt.ie> Reviewed-by: Andres Freund <andres@anarazel.de> Reviewed-by: Thomas Munro <thomas.munro@gmail.com> Discussion: https://postgr.es/m/cf85f46f-b02f-05b2-5248-5000b894ebab@enterprisedb.com
-
Add EXPLAIN (IO) support for plain index scans.
Extends the EXPLAIN (IO) instrumentation added for ReadStream-backed scans (bitmap scans in 681daed , then sequential scans in 3b1117d ) to the heap page prefetching now performed during index scans (and Index-only scans). Their Prefetch and I/O lines describe read-ahead of table (heap) blocks only: the blocks the scan prefetches as it follows index entries back to the table. Index pages are never prefetched this way, and so are neither relevant to nor counted in these numbers. For an index-only scan the only heap blocks prefetched are the occasional pages that are not yet all-visible (the same ones counted by "Heap Fetches"), so a scan that never falls back to the table shows no such lines at all. Unlike the other scan nodes that display this kind of instrumentation, the SO_SCAN_INSTRUMENT table AM scan flag isn't passed down to the scan. SeqScan, BitmapHeapScan and TidRangeScan set that flag (and only under INSTRUMENT_IO), so that the table AM allocates the IOStats and enables ReadStream statistics in beginscan only when it is present. An index scan is rescanned far more often than those nodes, so we avoid passing down the flag. Instead we reuse the IndexScanInstrumentation struct that the executor already allocates for every index scan during EXPLAIN ANALYZE. We always capture I/O instrumentation when running under EXPLAIN ANALYZE -- though we only show it to the user when they asked for it. Author: Tomas Vondra <tomas@vondra.me> Author: Peter Geoghegan <pg@bowt.ie> Discussion: https://postgr.es/m/CAH2-Wz=DhG5=sw9P1A5=bnXxCz4UJaWgH5WQA-3pEckXRucZdQ@mail.gmail.com
-
Allow read_stream_reset() to not wait for IO completion
Not waiting for IO during read_stream_reset() can be important for performance in cases where read streams are frequently reset before the end is reached. Current users do not commonly do that, but the upcoming work to use a read stream to prefetch table blocks as part of index scans can do so frequently in some query patterns. E.g. if there is an index scan on the inner side of a nested loop antijoin. This takes a bit of care to do right. Just introducing support for abandoning a AIO handle could lead to the IO's completion not being processed until the backend exits. That's bad because it would lead to resources held onto for the IO (e.g. buffer pins) not being released and the handle showing up in pg_aios. To avoid that, the existing resowner cleanup is changed to wait for the IO's completion, which guarantees that by the end of the statement the IO has completed. We might eventually want to relax that for some operations (e.g., for background WAL writes or opportunistic prefetching). Discussion: https://postgr.es/m/f3xxfrkafjxpyqxywcxricxgyizjirfceychyxsgn7bwjp5eda@kwbduhy7tfmu
anarazel authored and Commitfest Bot committed
Aug 22, 2026 -
aio: Fix pgaio_io_wait() for staged IOs (B).
Previously, pgaio_io_wait()'s cases for PGAIO_HS_DEFINED and PGAIO_HS_STAGED fell through to waiting for completion. The owner only promises to advance it to PGAIO_HS_SUBMITTED. The waiter needs to be prepared to call ->wait_one() itself once the IO is submitted in order to guarantee progress and avoid deadlocks on IO methods that provide ->wait_one(). Introduce a new per-backend condition variable submit_cv, woken by by pgaio_submit_stage(), and use it to wait for the state to advance. The new broadcast doesn't seem to cause any measurable slowdown, so ideas for optimizing the common no-waiters case were abandoned for now. It may not be possible to reach any real deadlock with existing AIO users, but that situation could change. There's also no reason the waiter shouldn't begin to wait via the IO method as soon as possible even without a deadlock. Picked up by testing a proposed IO method that has ->wait_one(), like io_method=io_uring, and code review. Backpatch-through: 18 Reviewed-by: Andres Freund <andres@anarazel.de> Discussion: https://postgr.es/m/CA%2BhUKG%2BmZYrSdnhk-XrBYO18H829K77S9gMKUsykOiTJtqB43g%40mail.gmail.com
anarazel authored and Commitfest Bot committed
Aug 22, 2026 -
WIP: aio: bufmgr: Fix race condition leading to deadlocks with io_uring
If backend A is in the process of starting IO for a buffer, there is a short period in which the buffer is marked as IO_IN_PROGRESS without having an associated AIO wait reference. If a backend B does WaitIO() on that buffer, it'll wait for the buffer's IO condition variable to be set. Most of the time that is OK, when the IO on the buffer finishes, the CV will be signalled. However, with io_uring, it is possible that the issuer (A) of the IO never gets around to doing so, e.g. because it is waiting for something done by B. To fix that, we need to signal the CV when staging IO. That's annoying as CV broadcasts are not cheap. So we at least avoid it for the common case of IO being executed synchronously. I hope that eventually we can get away from needing multiple systems for signalling IO completion, but we are clearly not there yet.
anarazel authored and Commitfest Bot committed
Aug 22, 2026
