PostgreSQL different database connection slows down

306 views Asked by At

How to speed up PostgreSQL different database connection

Query

SELECT  t1.is_crmtran_sync_time, t1.is_crmtran_sync_retry, t1.txn_date, t1.mpos_me_id
FROM dblink('dbname=mpos user=mpos password=mpos_access'::text, 
             'SELECT is_crmtran_sync boolean, is_crmtran_sync_time timestamp with time zone, is_crmtran_sync_retry integer, txn_date date, mpos_me_id integer);

I am trying to get data from a different database this is very slow is there any better way to connect it or I have some other problem how to identify that.

I have also attached the analysis report for the same can any one help me in this.

Analysis Report using dblink

us_pymt_web=# explain analyze select count(*) from pos_txns_view;

Aggregate  (cost=12.50..12.51 rows=1 width=0) (actual time=153547.721..153547.722 rows=1 loops=1)
           ->  Function Scan on dblink t1  (cost=0.00..10.00 rows=1000 width=0) (actual time=144960.072..150326.998 rows=1374456 loops=1)
Planning time: 0.821 ms
Execution time: 153901.597 ms
(4 rows)
0

There are 0 answers