I'm experiencing severe performance issues when trying to access data from a remote Postgresql server running on AWS, accessed via an SSH tunnel, using a foreign data wrapper on a local non-AWS Postgresql server.

The setup:

• Remote data is on a Postgres instance running in AWS
• I connect to that server from my local host via an SSH tunnel to an AWS "jumpbox" instance, which then connects to the host and port of the AWS Postgres server
• The local server and the SSH connection is running on MacOS, the jumpbox and remote Postgresql server are all on Linux
• Local server Postgresql version is 14.2, remote AWS server version is 11.9

Something like this:

Local host port 4001 <-- SSH tunnel --> | AWS firewall | <-- Jumpbox --> Target host port 5432 <--> Target Postgres server

I've set up a Foreign Data Wrapper in a local schema. This works correctly from a functional aspect but the performance is very poor.

If I use the exact same FDW configuration on an AWS-hosted server (but still on a different host to the AWS Postgresql instance), performance is fine.

So the problem is clearly something to do with the connection, via an SSH tunnel, through the AWS firewall and on to the remote AWS Postgresql instance.

Queries with specific literal key values seem to perform reasonably well, e.g. SELECT colA FROM tableX WHERE colB IN (value1, value2,....valueN). I can understand why this might be, since the optimiser can easily cut down the amount of data to be retrieved in the remote instance. However, any more nuanced queries, especially involving joins to local tables, perform terribly. The same queries, when run from the identical FDW setup inside the AWS firewall mentioned above, are orders of magnitude faster. Running ANALYZE locally on one of the larger remote tables completes within seconds on the AWS-hosted FDW, but is still running after 20 minutes on the local non-AWS FDW server which uses the SSH tunnel. I've tried adjusting the fetch_size and use_remote_estimate options, to no avail. I've also tried out dblink to see if that helps, but it's not really any better.

It's unclear from Postgresql documentation, and I haven't been able to find anything online which makes it clearer, exactly what's going on beneath the covers with the FDW server. My assumptions, which are probably wrong, are:

• For a purely remote query, (i.e. no local objects involved), with use_remote_estimate set to true, I'd expect the entire query to be optimised and executed on the remote server, with just the results being passed back across the network, in chunks determined by the fetch_size
• For a mixed local-remote query (i.e. joins between foreign and local tables), with use_remote_estimate set to true, I'd expect stats of local non-foreign tables to be interrogated and passed to the remote server, which then adds the stats of its own local versions of the foreign tables to come up with a suitable query plan to minimise both the reads on the remote data and also the amount of traffic between local and remote server; however, I'm wondering whether large amounts of data are actually being passed between the local and remote server, and this is what can cause the bottleneck when passing data via the SSH tunnel?

So, finally, my questions are:

1. Can anyone suggest what to check, or ways to try and improve performance when using a FDW from a non-AWS server to query data on an AWS server, via an SSH tunnel?
2. Can anyone shed more light on exactly what types of conversation and data pass between the local and remote server during query optimisation and execution using a FDW, both for remote-only and remote-local mixed queries, and how this is affected by the fetch_size and use_remote_estimate options? (with or without an SSH tunnel, I'm really just trying to understand the process)
3. Are there any other ways of doing these types of cross-server query (e.g. query data on a remote server, optionally joined with local data) which are likely to perform much better?

I haven't provided any specific queries because I believe the issue is far more generic than a single query.