Postgresql – postgres is unbearably sluggish after a 130MB pg_restore. how to fix

It's because if the DB is not on the same server as the frontend you have to do a round trip for each query (potentially more, depending on the size of the answer). It can easily take up to 1 ms to ping another server in the same datacenter. Much less if the two servers are on the same rack, but 1 ms is a good rule of thumb.

That means that for ~300 small queries you should expect ~300ms, pretty close to what you are seeing.

If the two servers are on the same machine, you only have to do some context switches between processes to move the data from the DB process to the frontend. Usually a context switch (with all the typical flushes) take ~40us (very broadly speaking), and you need at least a couple of them (frontend asks for data, DB reads requests and prepares and serves the data, and frontend reads back the data). So I would expect ~80us for each query (let's round it to 0.1ms to make the math easier). You can thus expect ~30ms for 300 small queries, which is also pretty close to what you are seeing.

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password: 

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

1. By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.

2. by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.