Pushdown of LIMIT and OFFSET

[cmantsch]

I am using Multicorn with a datasource that can potentially have millions of rows returned for a query, therefore I would like to reduce the amount of data already on the side of the foreign datasource.

So far I only figured out that there's only ways to pass quals and columns to the foreign datasource.
Is there a way to pushdown a LIMIT Expression to the foreign datasource?

[mfenniak]

Unfortunately, there is currently no support for pushing down LIMIT expressions -- the execute method only receives the where clauses, the columns requested, and the sort conditions.

I think it would be an interesting challenge to support pushdown... the required information on the existence of LIMIT/OFFSET is available from the query plan in the multicorn2 layer, so it's not a technical restriction which would prevent it from being done. But it might be difficult to design a solution that would work well. Here are some ideas on the complexity:

• If you query from a foreign data table and join another table, it isn't going to be clear how many records need to be returned from the foreign data table in order to satisfy the query (which may eliminate records that don't match the join).
• Not all WHERE clauses can be translated by multicorn2 into the Python layer -- for example, if you do operations involving other PostgreSQL functions. (eg. WHERE UPPER(column) = 'ABC'). Currently this works fine in multicorn2 because they're not passed to the Python layer, but PostgreSQL "rechecks" them anyway to provide the right outputs. But if you were querying LIMIT 50, and that was pushed down to the FDW which only returned 50 records, then the PostgreSQL recheck eliminated some of those records, you might get fewer than 50 records despite the foreign data having more than 50 records.
• It's also totally fine for a FDW to just ignore the WHERE clauses it doesn't want to interpret/manage, and, only care about the ones it wants to care about. This makes all the LIMIT/OFFSET issues more complex too.

I think there are probably other complexities as well, the more one would think about it. I have a feeling that the right design would require the Python FDW implementation to return a generator, or something like that, so that it could return a LIMIT-sized page of data but then continue the query if that didn't suffice after further filtering...

OFFSET may be even be even more complex because you've got the same filtering visibility limitation but now on records that you don't even want the FDW to have touched.

The more I think about it, the more I think that it's pretty much incompatible with the simple nature of multicorn2, where the design is driven around "give data to PG and make it do the complex work". But there could be enough heavy lifting done in multicorn2 to make it work only in limited situations...

[mfenniak]

Further thinking: I think that the best approach to using LIMIT with multicorn2 is to just ensure that the Python FDW class returns a generator from execute. PostgreSQL will, through multicorn2, iterate the generator until the count of the LIMIT condition is satisfied.

For example, in my DynamoDB FDW, I retrieve fixed-sized page of data from DynamoDB, then iterate through it and yield the result; at the end of the page, query for the next page and repeat. This uses a fixed amount of memory, and if a query is performed with a LIMIT clause then execution will be stopped after the right number of rows are retrieved (even if more than the LIMIT clause is required in order to satisfy filter conditions).

This doesn't provide great performance for OFFSET, but it might be the ideal combination of complexity and flexibility in many cases.

[cmantsch]

Thanks for your extensive reply!

I also had your points in mind with join and where clauses, especially with joins and unsupported conditions (I noticed conditions are only passed to multicorn when they solely contain AND, otherwise it's fetching everything and applying on postgres side).

My use case would be to fetch data from some other (quite exotic) database, I might try if your workaround you use in dynamodb would work.