HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Fastest validation query in PostgreSQL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlqueryvalidationfastest

Problem

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

I thought 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.

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#13612, answer score: 21

Revisions (0)

No revisions yet.