Slow connect time to postgresql on Windows 10

Question

Connect time to postgresql on my Win 10 machine incredible slow. It take 2.5-3.5 seconds for a connect.

$ time 'c:/Program Files/PostgreSQL/10/bin/psql.exe' -h 127.0.0.1 -p 5433 -U xxx -c 'select true'
 bool
------
 t
(1 row)
real 0m3.416s
user 0m0.015s
sys 0m0.000s

Same time when i try to use psycopg2 python driver. Same time for postgresql version 9 and 10. On the other hand connect time to mysql is 0.002, issue only with postgresql.

enter image description here

Here relevant logs with max debug from postgresql

2018年03月19日 21:24:43.654 +03 [10048] DEBUG: 00000: forked new backend, pid=21268 socket=5072
2018年03月19日 21:24:43.654 +03 [10048] LOCATION: BackendStartup, postmaster.c:4099
2018年03月19日 21:24:45.248 +03 [21268] LOG: 00000: connection received: host=127.0.0.1 port=9897

It fork new backend and then only 2 seconds later logs connection received

UPD Even if i manage to avoid connection delay of postgresql ( for example via pgbouncer, or if postgresql running in docker) request still take 1.3-2seconds, but from first sent package till last its only 0.022 second, all other time idk what happening but not a network communication between client and server. Same code if run within docker - 0.025 second. From windows - 1.3-2sec but network interaction only 0.022sec

There actually two problems that might be caused by same thing or different, no idea.

1. Postgresql not sending packet for 1.8 second for unknown reason

2. Even if first problem eliminated and network interaction down to 0.022 sec whole thing still take 1.3-2 sec instead of 2.5-3.5 ( using either psql or psycopg2)

• Patrick Mevzek: Do you use SSL for connecting? Try psql postgresql://127.0.0.1:5333/template1?sslmode=disable
• Aldarund: @PatrickMevzek tried. Changed nothing. I have attached packet dump, seems like pg send ACK packet than for some reason it wait 2 second sending nothing.. and then it continue to send data.
• jjanes: What authentication method are you using (from pg_hba.conf)?
• Aldarund: @jjanes trust right now. was using md5 before. changes nothing.
• jjanes: I cannot reproduce the problem on Windows 10, invoking from psql.exe from cygwin (which your shell looks like to me). I get 0.1 second. I think it might be a firewall or AV program mucking things up. Do you have one? Do you have any non-default settings for postgres, or extensions installed?

Answer 1

If PostgreSQL listen on localhost only, ensure IPv4 and IPv6 both addresses are set in postgresql.conf.

listen_addresses = '127.0.0.1,::1'.

• Alexey Trofimov: That saved me. I have an app running on windows 10 which connects to postgresql installed inside WSL. I had a 2 seconds lag each query run but this settings solve this totally. Can you provide some context on why this helps?
• Milo: Windows 10 prefers IPv6. If you connect to localhost, system translates it to ::1 (the IPv6 localhost address). If PostgreSQL does not listen on it, connection timeouts and connects to 127.0.0.1 (the IPv4 locahost address).

Answer 2

RST means something is telling you the connection is closed or is otherwise needing a reset (reinitializing). I would be looking at

1. Software Firewalls on the box.
2. Anti-Virus.
3. Underlying networking infrastructure that mucks with the ability for a machine to community with itself on localhost, evil glances at AWS.

No guarantee any of this is the problem. I would expect those arrows to switch direction based on the source and destination of the packet (incoming and outgoing table on the networking stack). In that display, all of the arrows are facing the same direction. That makes that display pretty difficult to parse.

Answer 3

I ended up at this question because in my pgAdmin4 (Version 6.12, on windows 10) it sometimes took many tens of seconds or even minutes for a simple SELECT * FROM mytable even for tables with only a few entries.

The solution was similar to the one suggested by user Milo:

In my C:\Program Files\PostgreSQL14円\data\postgresql.conf file I found listen_addresses = "*" which I changed to listen_addresses = 'localhost'.

All queries now only take a few milliseconds to complete and everything works fine!