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

Question

假设我想查询一个包含几个 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远程但在本地应用其他过滤器。

在我的用例中，计算哪个 id有 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)要预先计算并作为远程过滤器的一部分发送？

Answer 1

外部数据包装器

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

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

更方便的是，您可以使用动态 SQL 和 EXECUTE 自动执行此操作。在 PL/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;

有关的:

Table name as a PostgreSQL function parameter

或者试试 this search on SO .

或者你可以使用元命令 \gexec在 psql 中。看:

Filter column names from existing table for SQL DDL statement

或 这可能有效:(反馈说 不起作用 。)

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

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

Index Scan using big_table_idx on big_table (cost= ...)  Index Cond: (id = ANY ($0))  Filter: (c1 = 2)  InitPlan 1 (returns $0)    ->  Seq Scan on lookup_table  (cost= ...)          Filter: (x = 5)

Bold emphasis mine.

The parameter $0 in the plan inspires hope. The generated array might be something Postgres can pass on to be used remotely. I don't see a similar plan with any of your other attempts or some more I tried myself. Can you test with your fdw?

Related question concerning postgres_fdw:

• postgres_fdw: possible to push data to foreign server for join?

General technique in SQL

That's a different story. Just use a CTE. But I don't expect that to help with the 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 可以像子查询一样内联。但是，引用 the manual :

You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query

所以:

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

通常，如果您的数据库服务器配置正确并且列统计信息是最新的，则这些都不是必需的。但是也有数据分布不均匀的极端情况......