Create index on foreign table PostgreSQL

Question

I am using postgres_fdw to create a link between two databases. I then setup the foreign tables and do some inserts from the foreign tables to my live tables. I've noticed it is taking quite some time though because they don't have indexes.

Can you create an index on a foreign table, is it the standard

CREATE INDEX ON foreign_table_name (column)?  

Answer 1

No, you will get an error:

ERROR:  cannot create index on foreign table "tablename"
********** Error **********

ERROR: cannot create index on foreign table "tablename"
SQL state: 42809

And it makes sense as the query will "travel" througth the net and retrieve the data from the original database each time you query the table (will not store data to index).

What you can do is to use explain verbose to get the query that is being execute on the other side , and index the remote table accordingly .

explain verbose select * from schema.foreign_table

"Foreign Scan on schema.foreign_table  (cost=25.00..1025.00 rows=1000 width=84)"
"  Output: field1, field2, field3
"  Remote server startup cost: 25"
"  Remote query: SELECT field1, field2, field3 FROM schema.original_table

Hope that helps. Good luck!