From: | "Armand Pirvu (home)" <armand(dot)pirvu(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | views and fdw usage and performance |
Date: | 2017-05-09 21:36:59 |
Message-ID: | C58163C7-F60A-4862-9E16-12166F838079@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick B | 2017-05-09 22:00:02 | Re: Select from tableA - if not exists then tableB |
Previous Message | basti | 2017-05-09 20:38:30 | Convert from LATIN9 to UTF-8 / WAL Replication |