How would you create a unique index on a foreign table?

Question

Database 1 has foreign tables a and b on database 2.

How can we create indexes on these foreign tables a and b. These foreign tables are wrappers over database2.c and database2.d tables respectively, which do have the necessary indexes in place.

How would you create indexes on foreign tables a and b? Is that even possible?

I get a cannot create index on foreign table a - when I try a simple Create Index command in postgres

why would you want such thing? If tables have UK on database2, why would you want to have them in databse1?.. They already have a unique constraint?.. — Vao Tsun, Nov 28 '16 at 08:43
Hmmm may be you can take a look at this question for more context - http://stackoverflow.com/questions/40803345/deleting-rows-which-do-not-have-entries-in-another-table-as-foreign-keys-takes-a?noredirect=1#comment68896624_40803345 — Tisha, Nov 28 '16 at 08:48
so it's postgres-9.4 then. Foreign tables query planning for optimizer is indeed very limited with 9.4 (and 9.5 as well). I know 9.5 fdw offers remote joins — Vao Tsun, Nov 28 '16 at 09:00

score 4 · Accepted Answer · answered Nov 28 '16 at 12:17

4

You cannot create index on a foreign table, instead write a trigger on foreign table and create a local table in postgres such that whenever an insert, update or delete is happening in your foreign table it will be reflected in your local table and index it.

answered Nov 28 '16 at 12:17

user3837299

329
2
12

My local tables have indexes. I am not sure I completely understand what you are saying – Tisha Nov 28 '16 at 12:35
create local table with same structure of your foreign table, write a trigger such that whatever data comes into your foreign table is replicated in your local table. You can add index to your local table – user3837299 Nov 28 '16 at 13:14
I have accepted your answer but frankly I am using postgres_fdw and insert queries to insert data to my local tables. These do not even need foreign tables. – Tisha Nov 28 '16 at 16:36
It's just the delete that I need – Tisha Nov 28 '16 at 16:36

score 3 · Answer 2 · answered May 22 '17 at 06:13

Joining with a foreign table can result to slow query's. Since indexes are not a option with foreign table, consider making a materialized view on a foreign table . Materialized views do allow indexing

CREATE FOREIGN TABLE members_fdw(...)

CREATE MATERIALIZED VIEW members AS 
       select * from members_fdw  
       WITH  DATA 

CREATE UNIQUE INDEX "member_id" ON members USING btree ("id");

How would you create a unique index on a foreign table?

2 Answers2