Search code examples
sqldatabasepostgresqlforeign-data-wrapperpostgres-fdw

Remote query execute with postgres_fdw, using an indexes

As mentioned here postgres_fdw has no access to indexes.

A workaround is to create an view on remote server and then create foreign table wrapper to this view on local one.

But what if I want to pass a parameter to my view? Normally I would create a function(myparam) that RETURNS TABLE(). But how call it via postgres_fdw?

Any ideas to solve this situation (prefer to not use dblink if it is not needed)?

EXAMPLE

I have query like this to execute on my remote database:

select count(f.id_foo)
from foo f 
where f.date < _my_date

As you can see there is an param _my_date inside.

So I have created foreign table my_remote_server_public.my_remote_server_public_foo and run it from local database like:

select count(f.id_foo)
from my_remote_server_public.my_remote_server_public_foo f 
where f.date < _my_date

But when I do this - it lasts 2-3 minutes becouse postgres_fdw has no access to foo indexes.

I thought about creating a function get_foo_by_date(_my_date date) on a remote database and call it via postgres_fdw from the local one but dont know is it even possible...

UPDATE

Let's assume I will handle a normal view as a foreign table with constant date inside.

This view will return me an list of IDs from remote table.

I want to delete listed rows from remote table and archive these into local one.

When I call it like:

EXECUTE
'WITH rows_to_delete 
AS (DELETE from my_remote_server_public_foo 
    WHERE id_foo 
    IN 
    (SELECT * FROM my_remote_server_public_view_of_rows_to_delete) RETURNING *) 
INSERT INTO my_local_table 
SELECT * FROM rows_to_delete';

It lasts 5 min... Again becouse DELETE query have no access to indexes... Do I need to use dblink calling a function here too? Any other workaround?

Solution

  • The problem is not that postgres_fdw “has no access to indexes”, it is that aggregate functions are not “pushed down” to the remote server.

    There is no good way to do this yet, although aggregate push down is clearly on the list of things that PostgreSQL would like to add.

    The best way is to use dblink for requirements like that.

    In PostgreSQL 9.6 there is a new feature in postgres_fdw that you could abuse to push down a function.
    You'd have to create an extension that contains the function and install it on both the local and the remote database. Then you can add this extension to the extensions property on the foreign server. If the function is IMMUTABLE and you call it locally, it will be pushed down to the remote server.
    But that's ugly beyond description, and I would not recommend it.

    The question raised as “update” has nothing to do with the problem and should have been raised as a separate question.

    The problems here are that with PostgreSQL 9.5 or lower

    • joins between foreign tables are not pushed down to the remote side

    • updates and deletes operate row by row, necessitating a round trip per deleted row

    Both have been improved with PostgreSQL 9.6 (not so the aggregates).