Postgres performance not increasing with increase in number of core

Question

I was trying out postgres google-cloud-sql and loaded a simple school schema

 CREATE TABLE school ( 
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE class ( 
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT,
    school_id INTEGER NOT NULL REFERENCES school
);

CREATE TABLE student ( 
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT,
    class_id INTEGER NOT NULL REFERENCES class
);

-- ALL id and foreign keys have indexs

Loaded ~15 millions row in total with 1500 school, 500 class per school, 200 student per class.

After that create a simple pgbench script

\setrandom sId1 1 20000000
\setrandom sId2 1 20000000
\setrandom sId3 1 20000000

select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId1;

select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId2;

select count(*) from school s 
join class c on s.id=c.school_id 
join student stu on c.id=stu.class_id where s.id=:sId3;

Now running the the script with

pgbench -c 90 -f ./sql.sql  -n -t 1000

2 cores, 7.5 GB, 90 client --

OUTPUT:
number of transactions actually processed: 90000/90000
tps = 1519.690555 (including connections establishing)
tps = 2320.408683 (excluding connections establishing

26 cores, 30 GB, 90 client-

number of transactions actually processed: 90000/90000
tps = 1553.721286 (including connections establishing)
tps = 2405.664795 (excluding connections establishing)

Question: Why do we have only 80 tps increase from 2 core to 26 cores ?

Indehiscent

Versions of PostgreSQL before 9.6 do not parallelise queries. It can run multiple queries at once, but it cannot use multiple threads for one query. – Daguerreotype

i understand but i have 90 client in pgbench there should be a difference in 2 core handling 90 client vs 26 cores handling 90 clients.. right?... or i am missing something.. – Indehiscent

you don't connect to it over a session pooler - right? please give use port or other way ensure you don't share 90 clients in same 1 session. – Abstruse

for example, tell if you see over 90 active sessions in pg_stat_activity when you run pg_bench?.. – Abstruse

Yes i can see 92 rows in pg_stat_activity for the db against which i am running the pgbench script...:( any other thoughts why tps is not increasing? – Indehiscent

Are you using the same storage capacity for the 2 core vs 26 core instance? You may be disk throughput limited as this is capped based on the size of the disk . – Ninos

@Ninos please refer to my answer .. got from postgres IRC – Indehiscent

Answer 1

I asked same question on the postgres irc.

Community was sure that i was maxing out the client pgbench , they suggested to use -j4 in pgbench and tps increased to 23k per sec.

Indehiscent

Answer 2

Because an individual SELECT will only operate in one process running on one core. What adding extra cores will do is to allow multiple simultaneous operations to be performed. So if you were to throw (say) 1,000 simultaneous queries at the database, they would execute more quickly on 26 cores rather than 2 cores.

Weigle

But in my case pgbench is running 90 simultaneous client which meaning at a given movement there are 90 parallel select queries are running ,, so shouldnt there be a difference in execution time between 2 vs 26 cores. – Indehiscent