Ygthkb

Can you prevent a man in the middle from reading the message?

Is it possible to narrate a novel in a faux-historical style without alienating the reader?

Was there a pre-determined arrangment for division of Germany in case it surrendered before any Soviet forces entered its territory?

Renting a 2CV in France

Is there a way to pause a running process on Linux systems and resume later?

Is Screenshot Time-tracking Common?

How do I avoid the "chosen hero" feeling?

In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?

Are there historical references that show that "diatonic" is a version of 'di-tonic' meaning 'two tonics'?

Was Opportunity's last message to Earth "My battery is low and it's getting dark"?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Visit to Paris in layover time, which visa?

Is there any way to play D&D without a DM?

Why did Ylvis use "go" instead of "say" in phrases like "Dog goes 'woof'"?

Can I legally make a website about boycotting a certain company?

What happened to Hermione’s clothing and other possessions after she wiped her parents’ memories of her?

Why does a single AND gate need 60 transistors?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Protagonist constantly has to have long words explained to her. Will this get tedious?

How would an EMP effect spacesuits (and small-arms weapons)?

Is the percentage symbol a constant?

Is "accuse people to be racist" grammatical?

Is the UK legally prevented from having another referendum on Brexit?

Postgresql slow remote execution when using alternative clients

I would like to launch a pg_dump command from a remote computerSlow queries to remote PostgreSQL dbHaving a PostgreSQL database exposed to the internetConnect to remote EC2 Postgresql databaseFinding the current prices for n Fuelstations at a certain point in timeMySQL workbench: Cannot Connect to Database Server (ssh)pg_ctl won't start postgresPostgresql: newly created database does not existSELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same viewwhy can I sign-in through PyCharm, but not on a server using the same settings and credentials?

0

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.

postgresql query-performance postgresql-performance remote

share

asked 4 mins ago

user1068464user1068464

101

New contributor

user1068464 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

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.

postgresql query-performance postgresql-performance remote

share

asked 4 mins ago

user1068464user1068464

101

New contributor

user1068464 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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.

postgresql query-performance postgresql-performance remote

share

asked 4 mins ago

user1068464user1068464

101

New contributor

user1068464 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

$ 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)

$ 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)

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

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

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

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

share

asked 4 mins ago

user1068464user1068464

101

New contributor

user1068464 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 4 mins ago

user1068464user1068464

101

New contributor

user1068464 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

user1068464user1068464

101

New contributor

user1068464 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

user1068464user1068464

101