On Mon, 29 Apr 2024 at 18:17, Chris Cleveland
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.
