I am trying to use pgbench to test the DB performance when querying a users table. The table contains millions of users.
We have one query that is called a high number of times:
select id,company,name,attributes from users where id = 'foo' and company 'bar' limit 1;
I was trying to use pgbench, but its not behaving how I would expect. I created a file query_test.sql and populated it with queries for 10,000 different users i.e
select id,company,name,attributes from users where id = 'foo1' and company 'bar' limit 1;
select id,company,name,attributes from users where id = 'foo2' and company 'bar' limit 1;
select id,company,name,attributes from users where id = 'foo3' and company 'other' limit 1;
select id,company,name,attributes from users where id = 'foo4' and company 'other' limit 1;
...
...
I was expecting pgbench to run, for the set time, and execute as many of the queries in the file in turn as possible.
pgbench --host <host> --port 5432 --username <user> --time 10 --jobs 1 --client 1 --file ~/query_test.sql -r --verbose-errors user_db
It looks like the client tries to execute all the queries in the file.
Is there a way to have pg_bench execute this query a specified number of times. I want to pass in different user and company names to ensure its as realistic as possible.
What I want to see is the query latency time.