How to use index in external SELECT MAX (id) query table in PostgreSQL?

Question

I have an external table (using postgresql_fdw external data shell), and I need to find the maximum identifier to copy all the records. When I run SELECT MAX(id) FROM foreign_table , it does not seem to use an 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 

When I run the same query ( SELECT MAX(id) FROM table ) in a "real" table, it uses the 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 

A database with an external table has version 9.4.4, and one that has a "real" table has version 9.3.9.

Is it possible to use an index in the first query?

Answer 1

Postgres_fdw does not have access to indexes. Use a view on a remote server, for example:

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

On the local server, define a shell 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 on back_test_max will use the remote view and therefore also the index of the original remote table.