REAL HARDWARE.
I ran the same SQL via pgsql it took only 13 seconds.
My bottleneck has everything to be network.
#tcptrack -i eth1
Analyzing the traffic on the network, the speed is only 512Kb / s on port 5432.
# ethtool eth1
Settings for eth1:
Supported ports: [ TP ]
Supported link modes: 10baseT/Half 10baseT/Full
100baseT/Half 100baseT/Full
1000baseT/Full
Supported pause frame use: No
Supports auto-negotiation: Yes
Advertised link modes: 10baseT/Half 10baseT/Full
100baseT/Half 100baseT/Full
1000baseT/Full
Advertised pause frame use: No
Advertised auto-negotiation: Yes
Speed: 1000Mb/s
Duplex: Full
Port: Twisted Pair
PHYAD: 1
Transceiver: internal
Auto-negotiation: on
MDI-X: off
Supports Wake-on: pumbg
Wake-on: g
Current message level: 0x00000007 (7)
drv probe link
Link detected: yes
# iptables --list
Chain INPUT (policy ACCEPT)
target prot opt source destination
Chain FORWARD (policy ACCEPT)
target prot opt source destination
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
I tested the file transfer, the port speed did not exceed 512Kb/s on port 22.
I have some limitation on the network.
But I can not figure out why. This linux was installed by me, with only minimal packages to install postgres.
What can it be?
Well it is identified that the problem is not the postgres, but the operating systems, maybe I should look for the solution in another list.