The following bug has been logged online:
Bug reference: 6293
Logged by: Teun Hoogendoorn
Email address: th@atsc.nl
PostgreSQL version: 9.1
Operating system: Linux
Description: JDBC driver performance
Details:
Using the postgresql-9.1-901.jdbc3.jar driver instead of
postgresql-9.0-801.jdbc3.jar drops performance dramatically.
I think it has something to do with using ResultSetMetaData in Java. The
postgres log shows me hundreds of identical query's when retrieving the
ResultSetMetaData for a single query. I'm not using an ORM framework, just
simple JDBC calls.
Example:
LOG: execute <unnamed>: SELECT c.oid, a.attnum, a.attname, c.relname,
n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull),
pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace =
n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN
pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef
d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 53886 AS
oid , 1 AS attnum UNION ALL SELECT 53886, 2 UNION ALL SELECT 53886, 3 UNION
ALL SELECT 53886, 4 UNION ALL SELECT 53886, 5 UNION ALL SELECT 53886, 6
UNION ALL SELECT 53886, 7 UNION ALL SELECT 53886, 8 UNION ALL SELECT 53886,
9 UNION ALL SELECT 53886, 10 UNION ALL SELECT 53886, 11 UNION ALL SELECT
53886, 12 UNION ALL SELECT 53886, 13 UNION ALL SELECT 53886, 14 UNION ALL
SELECT 53886, 15 UNION ALL SELECT 53886, 16 UNION ALL SELECT 53886, 17 UNION
ALL SELECT 53886, 18 UNION ALL SELECT 53886, 19 UNION ALL SELECT 53886, 20
UNION ALL SELECT 53886, 21 UNION ALL SELECT 53886, 22 UNION ALL SELECT
53886, 23 UNION ALL SELECT 53886, 24 UNION ALL SELECT 53886, 25 UNION ALL
SELECT 53886, 26 UNION ALL SELECT 53886, 27 UNION ALL SELECT 53886, 28 UNION
ALL SELECT 53886, 29 UNION ALL SELECT 53886, 30 UNION ALL SELECT 53886, 31
UNION ALL SELECT 53886, 32 UNION ALL SELECT 53886, 33 UNION ALL SELECT
53886, 34 UNION ALL SELECT 53886, 35 UNION ALL SELECT 53886, 36 UNION ALL
SELECT 53886, 37 UNION ALL SELECT 53886, 38 UNION ALL SELECT 53886, 39 UNION
ALL SELECT 53886, 40 UNION ALL SELECT 53886, 41 UNION ALL SELECT 53916, 1
UNION ALL SELECT 53916, 2 UNION ALL SELECT 53916, 3 UNION ALL SELECT 53916,
4 UNION ALL SELECT 53916, 5 UNION ALL SELECT 53916, 6 UNION ALL SELECT
53916, 7 UNION ALL SELECT 53916, 8 UNION ALL SELECT 53916, 9 UNION ALL
SELECT 53916, 10 UNION ALL SELECT 53916, 11 UNION ALL SELECT 53916, 12 UNION
ALL SELECT 53916, 13 UNION ALL SELECT 53916, 14 UNION ALL SELECT 53916, 15
UNION ALL SELECT 53916, 16 UNION ALL SELECT 53916, 17 UNION ALL SELECT
53916, 18 UNION ALL SELECT 53916, 19 UNION ALL SELECT 53916, 20) vals ON
(c.oid = vals.oid AND a.attnum = vals.attnum)