[GENERAL] views and fdw usage and performance - Mailing list pgsql-general

Hi

I have two schemas jt1, and jt2 in the same db
In both I have the same table tbl3
The idea is to keep in sync jt1.tbl3 from jt2.tbl3 each time I have an insert/update/delete on jt2.tbl3

So I was thinking about the following cases to avoid replication

1) in jt2 rather than have the tbl3 table, have a view named tbl3 based on jt1.tbl3

2) use the postgtres fdw and in jt2 have a foreign table tbl3 to jt1.tbl3

create table tbl3 (col1 integer, col2 integer, col3 integer, primary key (col1));

insert into tbl3
select generate_series(1, 10000000), 111,222;

Questions:

q1 - Any issues with this logic ? Or any other ways to do this better ? jt2 usage in terms of concurrent users and so
onis by far much less than jt1, at least one order of magnitude less 

q2 - query performance (select * from tbl3 where col1=499123;
) . While using views it is clear cut the output of explain, on fdw not so much



explain analyze select * from jt2.tbl3 where col1=874433;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Foreign Scan on tbl3  (cost=100.00..138.66 rows=11 width=12) (actual time=0.204..0.205 rows=1 loops=1)
 Planning time: 0.043 ms
 Execution time: 0.374 ms
(3 rows)


explain analyze select * from jt1.tbl3 where col1=874433;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using tbl3_pkey on tbl3  (cost=0.43..8.45 rows=1 width=12) (actual time=0.010..0.011 rows=1 loops=1)
   Index Cond: (col1 = 874433)
 Planning time: 0.035 ms
 Execution time: 0.021 ms
(4 rows)


Do I understand correctly that the output of (explain analyze select * from jt2.tbl3 where col1=874433) is in essence
(forall practical purposes) the same as the one from (explain analyze select * from jt1.tbl3 where col1=874433;) and
nota sequential scan like the following ? 

explain analyze select * from jt1.tbl3 where col2=874433;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Seq Scan on tbl3  (cost=0.00..179053.25 rows=1 width=12) (actual time=498.020..498.020 rows=0 loops=1)
   Filter: (col2 = 874433)
   Rows Removed by Filter: 9999998
 Planning time: 0.030 ms
 Execution time: 498.034 ms
(5 rows)



Thanks
Armand