如何在PostgreSQL中的外表SELECT MAX（id）查询中使用索引？ [英] How to use index in foreign table SELECT MAX(id) query in PostgreSQL?

问题描述

我有一个外部表（使用 postgresql_fdw 外部数据包装器），我需要找到最大ID才能复制所有记录。当我从foreign_table运行 SELECT MAX（id）时，似乎没有使用索引：

I've got a foreign table (using postgresql_fdw foreign data wrapper) and I need to find maximum ID to copy all records. When I run SELECT MAX(id) FROM foreign_table it doesn't seem to be using index:

Aggregate  (cost=205.06..205.07 rows=1 width=4) (actual time=13999.535..13999.535 rows=1 loops=1)
  ->  Foreign Scan on foreign_table  (cost=100.00..197.75 rows=2925 width=4) (actual time=1.288..13632.768 rows=1849305 loops=1)
Planning time: 0.087 ms
Execution time: 14019.179 ms

当我运行相同的查询时（ SELECT MAX（id）FROM table ）使用索引：

When I run the same query (SELECT MAX(id) FROM table) on the "real" table it uses index:

Result  (cost=0.45..0.46 rows=1 width=0) (actual time=0.164..0.165 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.43..0.45 rows=1 width=4) (actual time=0.152..0.153 rows=1 loops=1)
       ->  Index Only Scan Backward using table_pkey on table  (cost=0.43..46102.55 rows=1821907 width=4) (actual time=0.143..0.143 rows=1 loops=1)
             Index Cond: (id IS NOT NULL)
             Heap Fetches: 1
Total runtime: 0.270 ms

外部表的版本为9.4.4，带有真实表的表的版本为9.3.9。

The database with the foreign table has version 9.4.4 and the one with the "real" table has version 9.3.9.

在第一个查询中是否有使用索引的方法？

Is there some way to use the index in the first query?

推荐答案

Postgres_fdw无法访问索引。在远程服务器上使用视图，例如：

Postgres_fdw has no access to indexes. Use view on a remote server, e.g.:

create view test_max as
select max(val) max_val
from test;

在本地服务器上，为远程视图定义一个包装器：

On local server define a wrapper for the remote view:

create foreign table back_test_max (
    max_val int
)
    server back_server
    options (schema_name 'public', table_name 'test_max');

back_test_max 上的选择将使用远程视图，因此也是原始远程表的索引。

Selects on back_test_max will use a remote view, and therefore also the index of the original remote table.

这篇关于如何在PostgreSQL中的外表SELECT MAX（id）查询中使用索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！