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 on is 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 (for all practical purposes) the same as the one from (explain analyze select * from jt1.tbl3 where col1=874433;) and not a 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
