Início / dba / Perguntas / 179744
Accepted
Luan Huynh
Asked: 2017-07-14 02:52:48 +0800 CST

ORDER BY muito lento na tabela estrangeira usando postgres_fdw

  • 772

PostgreSQL v9.6, postgres_fdw

Mesa estrangeira

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)

Dados de amostra da info coluna

{"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}

Consulta na tabela estrangeira ( atualizada )

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

Consulta em 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

Demora 10 milissegundos para executar a consulta no user_info_raw ( servidor remoto ).

Mas, leva muito tempo ao usar a user_infotabela estrangeira. Quando eu removo ORDER BY id, a consulta é executada muito rápido .

Eu acho que minha consulta na tabela estrangeira deve enviar para o servidor remoto para execução, mas não é, não sei porque, pode ser devido a esse trecho do documento postgres_fdw

Por padrão, apenas as cláusulas WHERE que usam operadores e funções integrados serão consideradas para execução no servidor remoto. As cláusulas que envolvem funções não internas são verificadas localmente depois que as linhas são buscadas. Se tais funções estiverem disponíveis no servidor remoto e puderem ser confiáveis ​​para produzir os mesmos resultados que produzem localmente, o desempenho pode ser melhorado enviando tais cláusulas WHERE para execução remota

Como posso corrigir o problema relacionado ao ORDER BY na tabela estrangeira?

ATUALIZADA

Adicionando use_remote_estimatee servernão foreign tableajuda.

postgresql postgresql-9.6

2 respostas

  1. Best Answer

    A operação de classificação é executada no servidor postgres local, não no remoto . Se o seu código sempre ordenar os resultados, você pode simplesmente criar uma view no postgres remoto com a cláusula order by e então criar uma tabela externa apontando para a view.

    No servidor remoto, adicionando uma visualização

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

    No servidor local, crie referências de tabela estrangeira para essa visualização

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

    Consulte o fórum de administração do Postgres

    • 1

  2. O pushdown LIMIT foi corrigido no PostgreSQL 12.

    Aqui está a discussão https://www.postgresql.org/message-id/[email protected] .

    Aqui está o commit https://github.com/postgres/postgres/commit/d50d172e517c1d2aabff3ceb3ad3113b909c5017 .

    • 1

relate perguntas