On Mon, 29 Apr 2024 at 18:17, Chris Cleveland <cclevel...@dieselpoint.com> wrote: > > I'm developing an index access method. > > SELECT * > FROM foo > WHERE col <=> constant > ORDER BY col <==> constant > LIMIT 10; > > I'm successfully getting the WHERE and the ORDER BY clauses in my beginscan() > method. Is there any way to get the LIMIT (or OFFSET, for that matter)?
No, that is not possible. The index AM does not know (*should* not know) about the visibility state of indexed entries, except in those cases where the indexed entries are dead to all running transactions. Additionally, it can't (shouldn't) know about filters on columns that are not included in the index. As such, pushing down limits into the index AM is only possible in situations where you know that the table is fully visible (which can't be guaranteed at runtime) and that no other quals on the table's columns exist (which is possible, but unlikely to be useful). GIN has one "solution" to this when you enable gin_fuzzy_search_limit (default: disabled), where it throws an error if you try to extract more results from the resultset after it's been exhausted while the AM knows more results could exist. > My access method is designed such that you have to fetch the entire result > set in one go. It's not streaming, like most access methods. As such, it > would be very helpful to know up front how many items I need to fetch from > the index. Sorry, but I don't think we can know in advance how many tuples are going to be extracted from an index scan. Kind regards, Matthias van de Meent.
