Re: [GENERAL] views and fdw usage and performance

On 05/09/2017 02:36 PM, Armand Pirvu (home) wrote:
> 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

Why?
If it is all on the same database why not just use 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 

Connections are to the database not the schema.

>
> 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
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com