Re: PostgreSQL over internet

On Sun, Jan 27, 2013 at 03:15:45AM +0300, belal hamed wrote:
>
> I connect to my server through ADSL connection 4Mbps
>

Here is your "problem". You need to understand the performance
characteristics of your communication channel. ADSL is a VERY
asymmetric communications channel. Down is usually much faster
than up.

> I try this query
>
> select "MatID", "MatName", "MatCode"
> from "Materials"
> where "MatCode" ~* '^1101'
> order by "MatCode"
> limit 2
>
> by wireshark I monitor TCP packets I found total data transmit/received 400B
> I took about 2.5s to fetch results why ??????
>
> after trying every solution mentioned in previous messages (DNS, tcpip,
> postgresql.conf, ...) not found any improve,
>
> I tried this one:
>
> using Zebedee(http://www.winton.org.uk/zebedee/)
> I build an IP tunnel between me and my data server (I used compression
> level 9)
>
> surprisingly same query now took about 600 ms, "very impressive"
>
> same thing with this query
> select "MatID", "MatName", "MatCode", "MatParent" from "Materials"
> from 48s down to 17s
>
> all these tests done on same connection with same devices so same dns,
> tcp-ip, ....
>
> now I am sure there is something wrong with libpq.

When you wrap the communication channel in an IP tunnel, you are
collapsing much of the syn-ack of the libpq protocol. You can see
the same effect trying to run any sort of X windows application.

Regards,
Ken