pgbench | PostgreSQL Client Driver Performance Benchmarking Toolbench | Performance Testing library

The pgbench run keeps asking for the same organization_id repeatedly, so the data are probably cached. Besides, there might be organizations with more messages. So I am not surprised that the runtimes are different.

Since you have already read up on the performance of count(*), I'll spare you the details. I see two options:

1. Use a materialized view that you refresh regularly:

pgbench is the one giving accurate results, assuming you need to read the entire data set. DataGrip is reading only a subset of the rows upfront. If you were to add a LIMIT to pgbench's query, it would also be faster.

2 minutes still seems pretty slow for 50,000 rows from select * from table_name, unless the rows are very wide, or the network is very slow, or pgbench is in the midst of a swapping/paging storm due to RAM constraints.

Note that pgbench reads the entire result set (for any given query) into memory, and so might run into memory problems for very large result sets.

Upgrade. In version 12, the limit is determined dynamically based on ulimit -n.

Upgrading will allow you do break you system more elegantly. You can even use a newer pgbench against an older server (or older pgbouncer), if you want.

It doesn't know that the next row present in pgbench_branches has bid>1 until it reads the next row and sees that it has bid>1. It might be able to infer it from the primary key constraint, but it isn't written to do that.

In YugaByte DB, currently, the PRIMARY KEY clause has to be specified as part of the CREATE TABLE statement, and cannot be added after the fact via an ALTER TABLE command.

We have made a recent change to the "pgbench" utility (that's bundled as part of the YugaByte DB distribution) to specify the PRIMARY KEY as part of the CREATE TABLE statement itself.

The relevant issue is: https://github.com/YugaByte/yugabyte-db/issues/1774 The relevant commit: https://github.com/YugaByte/yugabyte-db/commit/35b79bc35eede9907d917d72e516350a4f6bd281

The key thing is that it's a ShareLock on the transaction.

This means that one transaction is waiting for another to commit/rollback before it can proceed. It's only loosely a "lock". What's happening here is that a PostgreSQL transaction takes an ExclusiveLock on its own transaction ID when it starts. Other transactions that want to wait for it to finish can try to acquire a ShareLock on the transaction, which will block until the ExclusiveLock is released on commit/abort. It's basically using the locking mechanism as a convenience to implement inter-transaction completion signalling.

This usually happens when the waiting transaction(s) are trying to INSERT a UNIQUE or PRIMARY KEY value for a row that's recently inserted/modified by the waited-on transaction. The waiting transactions cannot proceed until they know the outcome of the waited-on transaction - whether it committed or rolled back, and if it committed, whether the target row got deleted/inserted/whatever.

That's consistent with what's in your error message. proc "x" is trying to insert into "my_test_table" and has to wait until proc "y" commits xact "z" to find out whether to raise a unique violation or whether it can proceed.

Most likely you have contention in some kind of upsert or queue processing system. This can also happen if you have some function/transaction pattern that tries to insert into a heavily contended table, then does a lot of other time consuming work before it commits.

All PostgreSQL client programs take the same connection options:

• -h for the host
• -p for the port
• -U for the user

Some programs use -d for the database, some need the database as an argument to the command.

In your case, since you used a non-default -h and -p option to connect with psql, you should use the same options for pgbench.

Turned out this was because after the 9.5 -> 9.6 upgrade, you need to ANALYZE the entire DB to get the query planner humming again.

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.