search

Segfault-Inc / Multicorn

Data Access Library
https://multicorn.org/
PostgreSQL License
699 stars 145 forks source link

Push grouping to the fdw level. #215

Open cristianprice opened 5 years ago

cristianprice commented 5 years ago

Hi there, Thanks for this awesome project. Quick question: is it possible to push the group by clause at the level of the foreign data wrapper? There are situations when i want to push this as low as possible to avoid network traffic by bringing data to postgres . Thanks, C

arturgspb commented 4 years ago

Hi! Any updates?

arturgspb commented 4 years ago

@rdunklau ping, please

rdunklau commented 4 years ago

Hello ! In theory it is possible, however I have no idea how to map the API to do that cleanly. We can keep this issue open in case someone has an idea or proposal, though.

arturgspb commented 4 years ago

Thx, a lot!

frank-jiao-tre commented 2 years ago

@rdunklau Do you have any plans to add this feature ? thanks a lot

gruuya commented 2 years ago

Hey there! First of all, thanks for making Multicorn - we at Splitgraph love it and have found it well suited for our own needs.

In fact, we went a step further and just implemented aggregation/grouping pushdown capability into our Multicorn fork. This capability is quite versatile, and basically allows for combinations of aggregation functions, GROUP BY clauses and qual (WHERE) clauses to be sent to the remote data source (if it supports those constructs of course). The relevant PRs are https://github.com/splitgraph/Multicorn/pull/1 and https://github.com/splitgraph/Multicorn/pull/2, while the practical FDW example that utilizes this feature (and only one so far) is our fork of Elastic search FDW. You can also take a look at our suite of end-to-end tests that exercises the full range of these capabilities.

Basically, our implementation closely follows the standard FDW approach for handling these things, best illustrated by postgres_fdw extension itself.

That said, our fork has diverged from the upstream significantly, such that contributing these changes back seems quite laborious. Nonetheless, we figured drawing attention to this can't hurt, as perhaps someone may find it useful. :)

Cheers!

df7cb commented 2 years ago

Hi @gruuya, were you able to fix the problems with the recent PG major versions in that fork?

gruuya commented 2 years ago

Hello there @df7cb! We are (for now) focused on PG 12 exclusively, so we haven't dealt with any problems in the later PG versions.