通过oracle_fdw从Postgres外部表中选择数据不使用oracle端的索引

Question

简而言之：我们面临的问题是在远程Oracle数据库上执行全表扫描，而不是使用索引。

设置：

Postgres 12.3在一个带有oracle基本客户端的丰富docker容器中，连接到19c版本的远程oracle数据库。访问的表有2M个条目。已安装的oracle_fdw版本为2.30。

问题：

外部表上的select似乎没有使用外部表的索引。我们希望根据本地表中的数据来选择外部表中的信息。我们尝试了不同的方法，如联接或子选择，但没有使用外部表上的索引。我们试图用一个函数生成不可变的数据，这确实奏效了。对于这个单一id，语句在12毫秒内返回，解释计划显示使用了索引。

CREATE FUNCTION f_single()
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT id FROM local_table';

SELECT r.* FROM remote_table r WHERE r.id IN (SELECT f_ single ());

"Insert on another_local_table  (cost=10000.00..10010.00 rows=1 width=5981) (actual time=11.855..11.855 rows=0 loops=1)"
"  ->  Foreign Scan on remote_table r  (cost=10000.00..10010.00 rows=1 width=5981) (actual time=11.095..11.793 rows=1 loops=1)"
"        Output: r.id"
"        Oracle query: SELECT /*fcb71071ce9258eac9244f42c3067c30*/ r3."ID"FROM " REMOTE_TABLE " r3 WHERE (r3."ID" = '2351923')"
"        Oracle plan: SELECT STATEMENT"
"        Oracle plan:   TABLE ACCESS BY INDEX ROWID REMOTE_TABLE "
"        Oracle plan:     INDEX UNIQUE SCAN PK_REMOTE_TABLE (condition "R3"."ID"='2351923')"
"Planning Time: 5.128 ms"
"Execution Time: 11.998 ms"

但如果我们返回多行，函数如下所示：，它实际上不起作用

CREATE FUNCTION f_multi()
RETURNS setof text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT id FROM local_table';

SELECT r.* FROM remote_table r WHERE r.id IN (SELECT f_ multi ());

"Insert on another_local_table  (cost=10022.26..20451397.84 rows=1000 width=5981) (actual time=264112.346..264112.346 rows=0 loops=1)"
"  ->  Hash Join  (cost=10022.26..20451397.84 rows=1000 width=5981) (actual time=17482.841..264112.267 rows=1 loops=1)"
"        Output: r.id "
"        Inner Unique: true"
"        Hash Cond: ((r.id)::text = (f_multi()))"
"        ->  Foreign Scan on remote_table r  (cost=10000.00..20446000.00 rows=2043600 width=5981) (actual time=319.042..263161.299 rows=1981851 loops=1)"
"              Output: r.id"
"              Oracle query: SELECT /*ceeb047d793530c693667f5f6fada4d8*/ r3."ID FROM " REMOTE_TABLE" r3"
"              Oracle plan: SELECT STATEMENT"
"              Oracle plan:   TABLE ACCESS FULL REMOTE_TABLE "
"        ->  Hash  (cost=19.77..19.77 rows=200 width=32) (actual time=419.881..419.881 rows=1 loops=1)"
"              Output: (f_multi())"
"              Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"              ->  HashAggregate  (cost=17.77..19.77 rows=200 width=32) (actual time=419.878..419.878 rows=1 loops=1)"
"                    Output: (f_multi())"
"                    Group Key: f_multi()"
"                    ->  ProjectSet  (cost=0.00..5.27 rows=1000 width=32) (actual time=419.867..419.870 rows=1 loops=1)"
"                          Output: f_multi()"
"                          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=419.744..419.745 rows=1 loops=1)"
"Planning Time: 4.804 ms"
"JIT:"
"  Functions: 11"
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 1.896 ms, Inlining 3.663 ms, Optimization 82.373 ms, Emission 333.437 ms, Total 421.368 ms"
"Execution Time: 264114.529 ms"

在这种情况下，即使对于一行，语句也需要大约4分钟才能返回。解释计划显示将执行"全表扫描"。

为什么不使用索引？我们能做些什么来强制使用索引？

如果需要有关设置或表格的更多信息，我们将更新此问题。

---

我们基本上追踪到了这方面的需求(WHERE语句似乎没有强制到Oracle(：

SELECT r.* FROM remote_table r
INNER JOIN local_table l 
ON l.id = r.id;

感谢您的帮助。非常感谢。

Answer 1

这是因为PostgreSQL优化器将查询转换为联接，而oracle_fdw不支持参数化路径，这可能会使嵌套循环联接内侧的外部扫描变得高效。

在第一种情况下，优化器知道IN列表必须包含一个值，并将查询转换为一个简单的相等条件。

使用两个查询可能会更成功：一个查询f_multi()的结果，另一个查询使用根据第一个查询的结果构建的常量IN列表。