sql - 使用索引使用 postgres_fdw 执行远程查询

Question

如前所述here postgres_fdw 无法访问索引。

解决方法是在远程服务器上创建一个 View ，然后在本地服务器上为该 View 创建外部表包装器。

但是如果我想将参数传递给我的 View 怎么办？通常我会创建一个 function(myparam) 来 RETURNS TABLE()。但是如何通过 postgres_fdw 调用它呢？

有什么办法可以解决这种情况(如果不需要，最好不要使用 dblink)？

示例

我有这样的查询要在我的远程数据库上执行:

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

如你所见，里面有一个参数_my_date。

所以我创建了外部表 my_remote_server_public.my_remote_server_public_foo 并从本地数据库运行它，例如:

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

但是当我这样做时 - 它会持续 2-3 分钟，因为 postgres_fdw 无法访问 foo 索引。

我考虑过在远程数据库上创建一个函数 get_foo_by_date(_my_date date) 并通过本地数据库的 postgres_fdw 调用它，但不知道是否可行。 .

更新

假设我将普通 View 处理为内部具有常量日期的外部表。

此 View 将从远程表中返回一个 ID 列表。

我想从远程表中删除列出的行并将它们归档到本地表中。

当我这样调用它时:

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';

它持续 5 分钟...再次因为 DELETE 查询无法访问索引...我是否也需要使用 dblink 在这里调用函数？还有其他解决方法吗？

Answer 1

问题不是 postgres_fdw“无法访问索引”，而是聚合函数没有“下推”到远程服务器。

虽然聚合下推显然在 PostgreSQL 想要添加的内容列表中，但目前还没有好的方法来做到这一点。

最好的方法是使用dblink对于这样的要求。

在 PostgreSQL 9.6 中有一项新功能 postgres_fdw你可能会滥用下推功能。
您必须创建一个包含该函数的扩展并将其安装在本地和远程数据库上。然后，您可以将此扩展添加到外部服务器上的 extensions 属性。如果该函数是 IMMUTABLE 并且您在本地调用它，它将被推送到远程服务器。
但这丑陋得无法形容，我不推荐它。

作为“更新”提出的问题与问题无关，应该作为一个单独的问题提出。

这里的问题是 PostgreSQL 9.5 或更低版本

• 外部表之间的连接不会向下推送到远程端

• 逐行更新和删除操作，需要对每个删除的行进行一次往返

两者都在 PostgreSQL 9.6 中得到了改进(聚合不是这样)。