Can you create an index on a foreign table?(外部表可以建立索引嗎？)

來自google搜尋的一段回答

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!