I've got a postgresql DB with > 3 million rows in 2 tables. Something slows lower the entire performance, and so i examined a little using the Ubuntu command pg_top.

I discovered there's always a question such as this:

SELECT 1 FROM <tablename>;

Appears like an association test statement in the java.sql.Connection, right?

After I go into the same command within the postgres command, it requires 4 seconds! Indexes are there, I checked that.

Question: How do i alter the test statement utilized by the bond object?

Thanks!

You're most likely with a couple type of connection pool, directly or not directly on the datasource as well as other framework like hibernate.

Sign in the documentation from the connection pool and check for that term 'validation query'. For postgres you need to have the ability to set a question like:

select version();

This ought to be considerably faster.

Real Cause has been discovered: A framework my code was depending on did test if the table can be obtained or otherwise. It was done via

SELECT 1 from TABLENAME;

This really returns ALL ROWS from that table and since I've 3 million rows inside it, it requires some time.

A far greater approach (a minimum of for POSTGRESQL) is that one:

SELECT relname FROM pg_class WHERE relname = 'mytable';

Thank you for all for that already published comments!

See source of the solution .