2

I noticed a strange perfomance issue when running sql queries on a remote Postgresql Server.

Everything good when using psql:

Connect via ssh and run things locally

$ ssh user@example.org
user@example:~$ psql -h localhost -U database -d database
database=# \timing
Timing is on.
database=# explain analyse select * from mytable where my_field='14123';
Time: 1646.577 ms (00:01.647)
database=# select * from mytable where my_field='14123';
Time: 1642.361 ms (00:01.642)

Forward postgresql to a local port and connect locally

$ ssh -L 1111:localhost:5432 user@example.org
$ psql -h localhost -p 1111 -d database -U database
database=# explain analyse select * from mytable where my_field='14123';
Time: 1669.424 ms (00:01.669)
database=# select * from mytable where my_field='14123';
Time: 1703.183 ms (00:01.703)

However, when I use GUI clients such as DBeaver or DataGrip I get the following timings:

DBeaver

Use option "SSH tunnel" in DBeaver

explain analyse select * from mytable where my_field='14123';
-- Execution time: 1665.447 ms
select * from mytable where my_field='14123';
-- Execution time: 6475 ms

"Direct" connection with locally forwarded port

explain analyse select * from mytable where my_field='14123';
-- Execution time: 1617.330 ms
select * from mytable where my_field='14123';
-- Execution time: 6370 ms

DataGrip

Use option "SSH tunnel" in DataGrip

sql> explain analyse select * from mytable where my_field='14123'
execution: 1 s 561 ms, fetching: 10 ms
sql> select * from mytable where my_field='14123' LIMIT 35
execution: 6 s 360 ms, fetching: 60 ms

"Direct" connection with locally forwarded port

sql> explain analyse select * from mytable where my_field='14123'
execution: 1 s 900 ms, fetching: 22 ms
sql> select * from mytable where my_field='14123' LIMIT 35
execution: 6 s 565 ms, fetching: 107 ms

Why are the timings so much worse when using these GUI applications? (6s instead of 1.5s) DataGrip's output suggests that it isn't the data being transferred, and in fact the select only yields 35 rows, which are < 100kb in total. Also it's much slower in both cases, and it takes about the same time in all the unexpected cases. What can cause this? Maybe something in the JDBC driver?

I connect to the server via VPN and then use an SSH-Tunnel to access the Postgresql server. Both the VPN server and database server are under little load and are very close to me (same city). Postgresql version is 10 and runs on Ubuntu 18.04.

  • 1
    Can you set either "log_min_duration_statement" or "auto_explain.log_min_duration" to see if the server itself agrees about these long execution times? – jjanes Feb 24 at 21:21
  • Those explain timings look suspicious. What is the time needed for SELECT 42;? (I get 0.2 ms on localhost, and 30 ms in the cloud.) – CL. Feb 25 at 10:10
  • @jjanes Yes, the server reports the same timings. (pastebin.com/Shk8abRa) – user1068464 Feb 25 at 11:40
  • @CL. Takes 15ms – user1068464 Feb 25 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.