在Postgres服务器a上，我正在调用一个查询，该查询使用来自服务器B的外部表(from mav4_gmd_data):
EXPLAIN ANALYZE VERBOSE
SELECT
d.mgd_mav4_gmd_object_mgo_id,
d.mgd_creation_date_iso,
d.mgd_data
FROM mav4_gmd_data AS d
WHERE
d.mgd_creation_date_iso > '2021-08-5 10:00' AND
d.mgd_mav4_gmd_object_mgo_id IN (
SELECT pg.mgo_id
FROM mav4_gmd_object as pg
WHERE pg.mgo_class = 'Ibc'
)
这个查询花费了相当长的时间。查询规划器显示服务器B上的SELECT (public.mav4_gmd_data上的外扫描)需要8550ms (public.mav4_gmd_data上的外扫描)
QUERY PLAN |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Semi Join (cost=235.92..266.63 rows=17 width=56) (actual time=8572.409..8572.412 rows=0 loops=1) |
Output: d.mgd_mav4_gmd_object_mgo_id, d.mgd_creation_date_iso, d.mgd_data |
Hash Cond: (d.mgd_mav4_gmd_object_mgo_id = pg.mgo_id) |
-> Foreign Scan on public.mav4_gmd_data d (cost=100.00..129.62 rows=341 width=56) (actual time=24.787..8550.000 rows=135856 loops=1) |
Output: d.mgd_id, d.mgd_creation_date_iso, d.mgd_creation_date_unix, d.mgd_mav4_gmd_system_mgs_id, d.mgd_mav4_gmd_object_mgo_id, d.mgd_data |
Remote SQL: SELECT mgd_creation_date_iso, mgd_mav4_gmd_object_mgo_id, mgd_data FROM public.mav4_gmd_data WHERE ((mgd_creation_date_iso > '2021-08-05 10:00:00+02'::timestamp with time zone))|
-> Hash (cost=135.80..135.80 rows=10 width=16) (actual time=0.761..0.762 rows=51 loops=1) |
Output: pg.mgo_id |
Buckets: 1024 Batches: 1 Memory Usage: 11kB |
-> Foreign Scan on public.mav4_gmd_object pg (cost=100.00..135.80 rows=10 width=16) (actual time=0.744..0.751 rows=51 loops=1) |
Output: pg.mgo_id |
Remote SQL: SELECT mgo_id FROM public.mav4_gmd_object WHERE ((mgo_class = 'Ibc'::text)) |
Planning Time: 0.164 ms |
Execution Time: 8573.195 ms |
但是，如果我直接在服务器B上运行相同的子查询，
EXPLAIN ANALYZE VERBOSE
SELECT mgd_creation_date_iso, mgd_mav4_gmd_object_mgo_id, mgd_data FROM public.mav4_gmd_data WHERE ((mgd_creation_date_iso > '2021-08-05 10:00:00+02'::timestamp with time zone))
运行速度明显更快(100ms):
QUERY PLAN |
------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Index Scan using idx_mgd_mgd_creation_date_iso on public.mav4_gmd_data (cost=0.43..16638.90 rows=42119 width=695) (actual time=0.021..96.663 rows=136032 loops=1)|
Output: mgd_creation_date_iso, mgd_mav4_gmd_object_mgo_id, mgd_data |
Index Cond: (mav4_gmd_data.mgd_creation_date_iso > '2021-08-05 10:00:00+02'::timestamp with time zone) |
Planning Time: 0.147 ms |
Execution Time: 103.860 ms |
对于更大的数据集，总时间的差异甚至更显著。我还尝试修改fetch_size和use_remote_estimate参数，但没有任何成功。可能是外部包装器没有使用服务器B上的索引吗?还有什么可能导致这个问题?或者这是Postgres的限制?
(PostgreSQL 13.3)
对于EXPLAIN ANALYZE，它确实需要执行查询，但它所需要做的就是计算结果有多少行。但是对于fdw，它必须在外部端执行查询，为传输格式化数据，实际上将其通过网络(或至少通过IPC)，然后解析它(至少足以识别行边界)，然后计算行数。
您可以期望fdw比直接执行它慢，但是您的测试并不一定实际地知道它会慢多少。如果您不想对结果做一些事情，那么您可能不会运行查询，并且对结果做一些重要的事情将按比例增加更快的查询比较慢的查询更多的时间。
对于更实际的测试，您可以这样做:
COPY (<query>) to '/dev/null';
和时间
更好的是，实际处理结果，无论它是什么，你想对结果做什么，这促使你在第一个地方编写查询。
可能是外部包装器没有使用服务器B上的索引吗?
我看不出有任何理由认为会是这种情况(毕竟，"远程sql"行确实显示了传递的可转位条件)。但是当你真的能看到的时候，猜测是没有意义的。不幸的是，EXPLAIN ANALYZE的输出不能递归到外部服务器，但幸运的是，如果您控制外部服务器，您可以在外部服务器端设置auto_explain来捕获计划，然后从日志文件中获得关于它正在做什么的直接证据。