7

What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource?

I though that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we should use select version(). This is not obvious to me.

I've tried to compare EXPLAIN ANALYZE SELECT 1 and EXPLAIN ANALYZE SELECT VERSION() and still can't see why second one is (or should be) faster.

10

This is obviously nonsense. SELECT 1 is faster (if only by a tiny bit). version() also returns quite a few bytes of text, in my case:

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

So you get some additional I/O, too.
pgAdmin, for instance, uses SELECT 1 as validation query.

1

JIRA looks at the database version when connecting to figure out things like how it should handle escaping of strings, something that changed in later versions of the database. That's the main reason I know of they use the longer, slower version check instead of a simpler SELECT. There may be other version specific code in there too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.