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)