JDBC slow performance on resultSet iteration? - Mailing list pgsql-jdbc

Hello everyone,

I'm having slow JDBC performance on iterating a resultSet obtained from a somewhat simple query.

The query, although using PostGIS functions, is pretty straight forward:

SELECT _id, _spatial_type, ST_AsBinary(_geometry), _attribute, _count, _references, countpersons, countfatals,
countdrunks,density 
FROM accidents_5
WHERE (_geometry && ST_MakeEnvelope(-126.60644531250001,26.43122806450644,-63.369140625,52.96187505907603, 4326) )
ORDER BY _pk_id

The results are the following:
- Executing the query directly from pgAdmin: ~2807ms
- Executing from JVM + JDBC: 4184ms

The code i'm executing is pretty much standard:

-------------------- code --------------------

    public static void main(final String[] args) throws Exception {

        String sql = "SELECT _id, _spatial_type, ST_AsBinary(_geometry), _attribute, _count, _references, countpersons,
countfatals,countdrunks, density"; 
        sql += " FROM accidents_5";
        sql += " WHERE (_geometry &&
ST_MakeEnvelope(-126.60644531250001,26.43122806450644,-63.369140625,52.96187505907603,4326) )"; 
        sql += " ORDER BY _pk_id";

        System.out.println(sql);

        Long time = System.currentTimeMillis();
        try {
            Connection connection = DataStoreInfo.getDataStores().get(0);
            connection.setAutoCommit(false);

            System.out.println("[QUERY " + Thread.currentThread().getId()
                    + "] - connection in "
                    + (System.currentTimeMillis() - time) + "ms.");

            Statement st = connection.createStatement(
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY,
                    ResultSet.CLOSE_CURSORS_AT_COMMIT);
            st.setFetchSize(250);

            System.out.println("[QUERY " + Thread.currentThread().getId()
                    + "] - statement in " + (System.currentTimeMillis() - time)
                    + "ms.");

            ResultSet resultSet = st.executeQuery(sql);

            System.out.println("[QUERY " + Thread.currentThread().getId()
                    + "] - done in " + (System.currentTimeMillis() - time)
                    + "ms.");

            GeometryFactory geofact = new GeometryFactory(new PrecisionModel(),4326);
            WKBReader wkbReader = new WKBReader(geofact);

            String id;
            String spatialType;
            Geometry geometry;
            String attribute;
            Long count;
            String reference;
            Map<String, Double> properties;
            String granularSynthString = "GranularSynthesis";

            while (resultSet.next()) {
                id = resultSet.getString(1);
                spatialType = resultSet.getString(2);

                // geometry = wkbReader.read(resultSet.getBytes(3)); // ignored the WKBReader and the results are about
thesame. 
                attribute = resultSet.getString(4);
                count = resultSet.getLong(5);
                reference = resultSet.getString(6);

                properties = new HashMap<String, Double>();
                Double aux = resultSet.getDouble(7);
                properties.put("countpersons", aux);
                aux = resultSet.getDouble(8);
                properties.put("countfatals", aux);
                aux = resultSet.getDouble(9);
                properties.put("countdrunks", aux);
                aux = resultSet.getDouble(10);
                properties.put("density", aux);
            }

            System.out.println("[QUERY " + Thread.currentThread().getId()
                    + "] - done & iterated in "
                    + (System.currentTimeMillis() - time) + "ms.");

            resultSet.close();
            st.close();
            connection.commit();
            connection.close();
        } catch (SQLException exception) {
            exception.printStackTrace();
        // } catch (ParseException exception) {  // ignored from WKBreader
        // exception.printStackTrace();
        }

        System.out.println("[End " + Thread.currentThread().getId()
                + "] - done in " + (System.currentTimeMillis() - time) + "ms.");
    }

-------------------- end code --------------------

Although i'm executing this on slow MacbookPro (2.6 core duo, 2Gbs RAM but SSD) and have a 9.1.2 postgres, i've also
testedthis on a recent retina MBP and the ratio between pgAdmin and JDBC execution is similar. 

Is this expected? Can someone point if i'm doing something terrible wrong?

I'm not concerned about the query performance per-se (i know it CAN be optimized), but the differences just using JDBC
anditerating the resultSet are really annoying. 

Thanks in advance for any help.

Kind regards,

Rui Leal