java.sql.Connection tests against a PostgreSQL 8.4.6

I have a postgresql DB with > 3 mi开发者_JS百科llion rows in two tables. Something slows down the whole performance, so I analyzed a bit with the Ubuntu command pg_top.

I found out there is always a query like this:

SELECT 1 FROM <tablename>;

Looks like a connection test statement from the java.sql.Connection, right?

When I enter the same command in the postgres command, it takes 4 seconds! Indexes are all there, I checked that.

Question: How can I change the test statement used by the Connection object?

Thanks a lot!

You are probably using some kind of connection pool, directly or indirectly over a datasource or some other framework like hibernate.

Check in the documentation of the connection pool and search for the term 'validation query'. For postgres you should be able to set a query like:

select version();

This should be much faster.

Root Cause has been found: A framework my code was relying on did test whether the table is available or not. This was done via

SELECT 1 from TABLENAME;

This actually returns ALL ROWS from that table and because I have 3 million rows in it, it takes a while.

A much better approach (at least for POSTGRESQL) is this one:

SELECT relname FROM pg_class WHERE relname = 'mytable';

Thanks for all for the already posted comments!

See source of the solution .