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.

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)

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'.

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\PostgreSQL\14\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!