9

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)

7
  • Do you use SSL for connecting? Try psql postgresql://127.0.0.1:5333/template1?sslmode=disable Mar 19, 2018 at 14:30
  • @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.
    – Aldarund
    Mar 19, 2018 at 15:14
  • What authentication method are you using (from pg_hba.conf)?
    – jjanes
    Mar 20, 2018 at 17:14
  • @jjanes trust right now. was using md5 before. changes nothing.
    – Aldarund
    Mar 20, 2018 at 18:29
  • 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?
    – jjanes
    Mar 20, 2018 at 23:27

3 Answers 3

6

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

listen_addresses = '127.0.0.1,::1'.

2
  • 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? Mar 11, 2020 at 10:40
  • 1
    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).
    – Milo
    Mar 12, 2020 at 12:13
1

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.

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.