[JDBC] Performance comparison to psql.

We compared insert & select operations between jdbc (postgres 8.2, postgresql-8.2-506.jdbc3.jar) and psql.

Here are some numbers:

PSQL *COPY* from STDIN
recordsCount = 10 000 000

WITHOUT INDEXES: time = 50544 ms (*0.005 ms* per record) WITH 2 INDEXES: time = 221491 ms (*0.022 ms* per record)

*JDBC (prepared statement without batch)*
recordsCount = 100 000

WITHOUT INDEXES: time = 64874 ms (*0.649 **ms* per record) WITH 2 INDEXES: time = 63057 ms (*0.630 ms* per record)

*JDBC (**prepared statement with batch**)*
recordsCount = 1 000 000

WITHOUT INDEXES: time = 73205 ms (*0.073** ms* per record) WITH 2 INDEXES: time = 100270 ms (*0.100 ms* per record)

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

Comparison table (records inserted per millisecond)

                    COPY    JDBC    JDBC batch
WITHOUT INDEXES:    198     1.5        14
WITH  2 INDEXES:     45     1.5        10

As for select/queries psql was about 3 times faster. Both psql and jdbc operations were done remotely from the same machine.

I understand that JDBC has some overhead (object translation,..) but didn't think the difference would be that big. Do this numbers look correct (any optimization suggestion?)

Any performance improvement in postgresql-8.2-507.jdbc4.jar?

Is the copy operation much more optimized than inserts (and if so when/will the driver support it)?

Thanks,
Arie.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match