For my work I often have to to large selects loading a lot of data. The connection happens with JDBC driver and high fetch size. This is not a problem when working directly on the server of the database. However when connecting from a different machine, the load times get terrible and it takes >10x times longer to load the data.
Note that the amount of data is actually small (100s of MB, 200k rows) and the network bandwidth can't be the limiting factor. Therefore it's almost certainly latency and hence round trips that cause the bad performance.
Latency issue seem confirmed by checking with wireshark and I see a huge back and forth between the client and server. (note both windows). Similar what was observed in this old topic here.
Are there any server or client side (JDBC) settings that would reduce roundtrips and lower the latency costs?
On same machine: Remote: 20 min to load data (ping ~120ms) local: 50 seconds (ping < 1ms)
where local means intranet and db server is in a building about 300 feet away. And on the actual server it's also 50 seconds. Explain analyze says 105ms.
I do understand the issue I simply wasn't aware the impact is that big. I also wonder if it's a windows issue as it seems there are far too many ack after very few data when connecting from remote. I should probably ask about that in a network forum.
explain (analyze) select .... The difference between the reported runtime in the result and the runtime you see is due to the network overhead and/or the overhead your SQL client has when processing the data