Re: Possible to get LIMIT in an index access method? - Mailing list pgsql-hackers

From Matthias van de Meent
Subject Re: Possible to get LIMIT in an index access method?
Date
Msg-id CAEze2WiQcrEiD1JWfaV3++e9hB6ZMOK6eX-PNa8SeBfH_RSL=Q@mail.gmail.com
Whole thread Raw
In response to Possible to get LIMIT in an index access method?  (Chris Cleveland <ccleveland@dieselpoint.com>)
List pgsql-hackers
On Mon, 29 Apr 2024 at 18:17, Chris Cleveland
<ccleveland@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
mostaccess 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.



pgsql-hackers by date:

Previous
From: Ranier Vilela
Date:
Subject: Re: Direct SSL connection and ALPN loose ends
Next
From: Daniel Gustafsson
Date:
Subject: Re: DROP OWNED BY fails to clean out pg_init_privs grants