Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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