主页 / dba / 问题 / 179744
Accepted
Luan Huynh
Asked: 2017-07-14 02:52:48 +0800 CST

使用 postgres_fdw 在外部表中 ORDER BY 太慢

  • 772

PostgreSQL v9.6，postgres_fdw

国外表

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb 
) 
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' );
-- user_info_raw is a large table (100 million records, 200 GB)

info 列样本数据

{"key1": 1, "key2": 0.678}
{"key1": 1, "key2": 1.0}
{"key1": 1, "key2": 0.986} 
{"key1": 2, "key2": 0.75}
{"key1": 2, "key2": 0.639}

外表查询 （更新）

SELECT id, info 
FROM user_info
WHERE info ->> 'key1'= '1' -- OR using jsonb_extract_path_text(info, 'key1')  = '1'
ORDER BY id 
LIMIT 10; 

Limit  (cost=10750829.63..10750829.65 rows=10 width=40) (actual time=550059.320..550059.326 rows=10 loops=1)
   ->  Sort  (cost=10750829.63..10751772.77 rows=377257 width=40) (actual time=550059.318..550059.321 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Foreign Scan on user_info (cost=100.00..10742677.24 rows=377257 width=40) (actual time=1.413..536718.366 rows=68281020 loops=1)
               Filter: ((info ->> 'key1'::text) = '1'::text)
               Rows Removed by Filter: 7170443
 Planning time: 4.097 ms
 Execution time: 550059.597 ms

查询 user_info_raw

EXPLAIN ANALYSE
SELECT id, info 
FROM user_info_raw
WHERE info ->> 'key1'= '1'
ORDER BY id 
LIMIT 10;

 Limit  (cost=0.57..1296.95 rows=10 width=59) (actual time=0.043..0.073 rows=10 loops=1)
   ->  Index Scan using idx_user_info_raw_info on user_info_raw  (cost=0.57..68882850.88 rows=531346 width=59) (actual time=0.042..0.070 rows=10 loops=1)
         Filter: ((info ->> 'key1'::text) = '1'::text)
 Planning time: 0.192 ms
 Execution time: 0.102 ms 

select pg_size_pretty(pg_table_size('user_info_raw'));
 pg_size_pretty 
----------------
 223 GB

在user_info_raw（远程服务器）上执行查询需要10 毫秒。

但是，使用外部表需要很多时间。user_info当我删除ORDER BY id时，查询执行得非常快。

我认为我对外表的查询 应该发送到远程服务器执行，但不是，我不知道为什么，可能是由于postgres_fdw 文档的摘录

默认情况下，只有使用内置运算符和函数的WHERE 子句 才会被考虑在远程服务器上执行。获取行后，在本地检查涉及非内置函数的子句。如果这些函数在远程服务器上可用并且可以依赖它们产生与本地相同的结果，则可以通过发送这样的 WHERE 子句进行远程执行来提高性能

如何解决外部表中与 ORDER BY 相关的问题？

更新

添加use_remote_estimate并server没有foreign table帮助。

postgresql postgresql-9.6

2 个回答

  1. Best Answer

    排序操作在本地 postgres 服务器上执行，而不是在远程. 如果您的代码总是对结果进行排序，您可以使用order by子句在远程 postgres 上创建一个视图，然后创建一个指向该视图的外部表。

    在远程服务器上，添加一个视图

    CREATE VIEW vw_user_info_raw
SELECT id, info 
FROM user_info_raw
ORDER BY id

    在本地服务器上，创建对该视图的外部表引用

    CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb 
) 
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'vw_user_info_raw' );

    请参阅 Postgres 管理员论坛

    • 1
    • 1

相关问题