I have made an sql query that runs in remote database (using postgres_fdw extension) and combines information from 2 different tables in the remote database. The query execution time takes about 5 mins although i have create the remote server with the option of use_remote_estimate as true. When i run the same query directly in remote database itself, it takes less than 5s to get the results. Can i do something for better performance? Are there any settings available for better performance? When query runs from my database to the remote database, does use the foreign keys and indexes or no? I am not sure about that. i have search in the net for a solution but no answer cover my needs. Thanks

When i execute the EXPLAIN (ANALYZE BUFFER) to remote database i get those results:

QUERY PLAN
Merge Join  (cost=2920.60..6345984.84 rows=56546 width=440) (actual time=5874.882..834358.774 rows=3338 loops=1)
Merge Cond: (str.store_id = prf.store_id)
->  Sort  (cost=1221.49..1229.84 rows=3338 width=18) (actual time=2835.745..2840.122 rows=3338 loops=1)
Sort Key: str.store_id
Sort Method: quicksort  Memory: 314kB
->  Foreign Scan on store str  (cost=100.00..1026.14 rows=3338 width=18) (actual time=158.809..2832.790 rows=3338 loops=1)
->  Sort  (cost=1699.11..1707.58 rows=3388 width=23) (actual time=2882.143..2891.705 rows=3338 loops=1)
Sort Key: prf.store_id
Sort Method: quicksort  Memory: 350kB
->  Foreign Scan on store_profile prf  (cost=100.00..1500.47 rows=3388 width=23) (actual time=166.818..2878.937 rows=3338 loops=1)
SubPlan 1
->  Foreign Scan on store_profile p  (cost=100.29..112.16 rows=1 width=19) (actual time=165.285..165.287 rows=1 loops=3338)
Planning time: 495.796 ms
Execution time: 834678.186 ms