Proposal for qual checking and pushdown

[abramsh]

As a follow up to #81, here is my proposal to push down quals properly while maintaining backwards compatibility with existing FDWs:

Background:
Today Multicorn2 qual pushdown is "best effort". It sends any quals it knows how to parse down to the FDW which may or may not push them down to it's underlying source system. Because they may not be pushed down, they are left in the plan for Postgres to filter again. Presumably this design was chosen because it is simple and Postgress spends very little effort for to re-check the filters.

However, the new limit/offset support is limited (no pun intended) because we can't safely push down limit/offset unless ALL the quals for a statement were pushed down.

Proposal:

• New Python method can_filter( self, quals ) that receives all quals that Multicorn knows how to parse
  • Return of “None” means "best effort". Send to execute and Postgres will filter itself regardless (existing/default behavior)
  • Return of (subset of) quals list means push down those quals. Send only the returned quals to execute, Postgres will only filter others.
• New plan state variable quals_pushed_down that is only true if
  • Multicorn parsed ALL quals (it can't support OR(s), etc, today)
  • can_filter returned ALL quals
• In both scans and parameterized paths, push down quals returned from can_filter and leave any remaining for Postgres
• Only push down limit/offset if quals_pushed_down is true

We can then, over time, add support for parsing more qual types - like OR(s)

[mfenniak]

I can't foresee any technical problem with your plan.

Do you have a specific real-world, high-value situation where you're planning to use this? My only real concern is that it seems like really complicated to write a multicorn2 python driver that takes advantage of this, and, that the work is quite significant and complex for you to take on. And then, it becomes the project's responsibility to maintain indefinitely. I'd love to hear more about what real-world problems you expect to tackle with this solution in order to understand the value that justifies all that work.

Sorry for the delay in response; I was spammed by GitHub and apparently turned off email notifications broadly, unintentionally, in the wake of the spam. 🤣

[abramsh]

Real-world? Sure - this addresses the limitations in the limit support. For example, this would let you do select * from table where x > 10 limit 1. High-value? I guess that is all relative. Right now, I can foresee this improving my future query plans, but I am not currently blocked by this. (However, I think the lack of push down might being causing problems in costs for parameterized plans. Seems like this results in cost of an FDW scan in join loop is just 1 times the cost, instead of N times, so postgres choose the parameterized plan even though in reality it results orders of magnitude more cost)

I get the concern that this feature might not be used that much, and therefore the risk/effort/maintenance of it might not be worth it. But I might be missing your concern about it being really complicated to write the python driver for it. If an FDW is already pushing down some quals, it must already know which is supports - centralizing that logic would implement the new method with almost little additional effort.

FWIW, I personally don't have time to implement this right now, so I'm not going to push too hard. I real goal in this issue was to propose what I thought would be needed to address the "hope" that @luss had in #81 (comment) .

[mfenniak]

If an FDW is already pushing down some quals, it must already know which is supports - centralizing that logic would implement the new method with almost little additional effort.

Yes, but it's the precision of the pushdown that is tricky. When PG provides the backing implementation, the python implementation can "under filter", returning more records than is correct. I'd guess that someone could easily mess up the filtering with respect to NULL values, where SQL's behavior can be unexpected at times.

But anyway, I think it's great to have a plausible design here. 👍 If we're on the same page that it's a design for now, that's great. I'm open to this change, and I think this sounds like the right design. I won't commit to reviewing and accepting a patch in the absence of a user-story that supports the work, and I want to be transparent about that so you (or another future contributor) doesn't invest the time and then reach disappointment if it isn't merged.

[luss]

I think wanting to be able to push down simple where clauses when there is a limit/offset command is a high value user story. I wonder how a mature fdw, such as oraclefdw, handles this complexity?

…

On Fri, Oct 10, 2025 at 10:33 AM Mathieu Fenniak ***@***.***> wrote: *mfenniak* left a comment (pgsql-io/multicorn2#90) <#90 (comment)> If an FDW is already pushing down some quals, it must already know which is supports - centralizing that logic would implement the new method with almost little additional effort. Yes, but it's the precision of the pushdown that is tricky. When PG provides the backing implementation, the python implementation can "under filter", returning more records than is correct. I'd guess that someone could easily mess up the filtering with respect to NULL values, where SQL's behavior can be unexpected at times. But anyway, I think it's great to have a plausible design here. 👍 If we're on the same page that it's a design for now, that's great. I'm open to this change, and I think this sounds like the right design. I won't commit to reviewing and accepting a patch in the absence of a user-story that supports the work, and I want to be transparent about that so you (or another future contributor) doesn't invest the time and then reach disappointment if it isn't merged. — Reply to this email directly, view it on GitHub <#90 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHVJ35J65ZBOMMVU56T3W67ULAVCNFSM6AAAAACIJGCBVKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTGOJQGQ4TQMBZGY> . You are receiving this because you were mentioned.Message ID: ***@***.***>