如何在加入/下推到外部服务器之前强制执行子查询的评估

一尘不染

如何在加入/下推到外部服务器之前强制执行子查询的评估

sql

假设我要查询带有几个WHERE过滤器的大表。我正在使用Postgres
11和一个外部表；外部数据包装器（FDW）是clickhouse_fdw。但是我也对通用解决方案感兴趣。

我可以这样做，如下所示：

SELECT id,c1,c2,c3 from big_table where id=3 and c1=2

我的FDW能够对远程外部数据源进行过滤，从而确保上面的查询是快速的，并且不会提取太多数据。

如果我写上面的作品相同：

SELECT id,c1,c2,c3 from big_table where id IN (3,4,5) and c1=2

即，所有过滤都向下游发送。

但是，如果我要进行的过滤稍微复杂一点：

SELECT bt.id,bt.c1,bt.c2,bt.c3
from big_table bt
join lookup_table l on bt.id=l.id
where c1=2 and l.x=5

然后查询计划者决定进行c1=2远程过滤，但在本地应用其他过滤器。

在我的用例中，先计算哪个ids l.x=5，然后将其发送出去以进行远程过滤会更快，所以我尝试通过以下方式编写它：

SELECT id,c1,c2,c3
from big_table
where c1=2
and id IN (select id from lookup_table where x=5)

但是，查询计划者仍决定对big_table满足的所有结果在本地执行第二个过滤器c1=2，这非常慢。

有什么方法可以“强制”(select id from lookup_table where x=5)预先计算并作为远程过滤器的一部分发送？

阅读 133

2021-03-17

共1个答案

一尘不染

外部数据包装器

通常，联接或子查询或CTE的任何派生表在外部服务器上不可用，必须在本地执行。即，WHERE示例中在simple子句之后剩余的所有行都必须像您观察到的那样在本地进行检索和处理。

如果所有其他方法均失败，则可以执行子查询SELECT id FROM lookup_table WHERE x = 5并将结果连接到查询字符串中。

更方便的是，您可以使用动态SQL和EXECUTEPL / pgSQL函数自动执行此操作。喜欢：

CREATE OR REPLACE FUNCTION my_func(_c1 int, _l_id int)
   RETURNS TABLE(id int, c1 int, c2 int, c3 int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE
     'SELECT id,c1,c2,c3 FROM big_table
      WHERE  c1 = $1
      AND    id = ANY ($2)'
   USING _c1
       , ARRAY(SELECT l.id FROM lookup_table l WHERE l.x = _l_id);
END
$func$  LANGUAGE plpgsql;

有关的：

表名作为PostgreSQL函数参数

或尝试在SO上进行此搜索。

或者，您可以\gexec在psql中使用meta命令。看：

从现有表中为SQL DDL语句过滤列名

~~或这可能起作用：（~~反馈说 不起作用 。）

~~~~

SELECT id,c1,c2,c3
FROM   big_table
WHERE  c1 = 2
AND    id = ANY (ARRAY(SELECT id FROM lookup_table WHERE x = 5));

在本地测试，我得到这样的查询计划：

在big_table上使用big_table_idx进行索引扫描（cost = ...）
  索引条件：（id = ANY（ **$ 0** ））
  过滤器：（c1 = 2）
  InitPlan 1（返回 **$ 0** ）
    ->在lookup_table上进行Seq扫描（cost = ...）
          筛选器：（x = 5）

大胆强调我的。

$0计划中的参数激发了希望。生成的数组可能是Postgres可以传递给远程使用的东西。我看不出有其他尝试或您自己尝试过的类似计划。你可以用你的fdw测试吗？

有关的相关问题postgres_fdw：

postgres_fdw：可以将数据推送到外部服务器以进行联接吗？

SQL的通用技术

那是一个不同的故事。只需使用CTE。但是我不希望这对FDW有帮助。

WITH cte AS (SELECT id FROM lookup_table WHERE x = 5)
SELECT id,c1,c2,c3
FROM   big_table b
JOIN   cte USING (id)
WHERE  b.c1 = 2;

PostgreSQL 12
更改（改进了）行为，因此，在满足一些先决条件的情况下，可以像子查询一样内联CTE。但是，引用手册：

您可以通过指定MATERIALIZED强制对WITH查询进行单独计算来覆盖该决定

所以：

WITH cte AS MATERIALIZED (SELECT id FROM lookup_table WHERE x = 5)
...

通常，如果您的数据库服务器配置正确并且列统计信息是最新的，则无需执行任何操作。但是有些情况下数据分布不均…

2021-03-17