Johannes wrote: > psql > select lo_get(12345); > +ssl -compression 6.0 sec > -ssl 4.4 sec
psql requests results in text format so that SELECT does not really test the transfer of binary data. With bytea_output to 'hex', contents are inflated by 2x. Can you tell how fast this goes for you, as a comparison point: \lo_export 12345 /dev/null ? Many client interfaces use the text format, but you want to avoid that if possible with large bytea contents. In addition to putting twice the data on the wire, the server has to convert the bytes to hex and the client has to do the reverse operation, a complete waste of CPU time on both ends. At the SQL level, the DECLARE name BINARY CURSOR FOR query can help to force results in binary, but as the doc says: http://www.postgresql.org/docs/current/static/sql-declare.html "Binary cursors should be used carefully. Many applications, including psql, are not prepared to handle binary cursors and expect data to come back in the text format." Personally I don't have experience with JDBC, but looking at the doc: https://jdbc.postgresql.org/documentation/94/binary-data.html I see this: "To use the Large Object functionality you can use either the LargeObject class provided by the PostgreSQL™ JDBC driver, or by using the getBLOB() and setBLOB() methods." If the data lives on the server as large objects, I would think that this LargeObject class has the best potential for retrieving them efficiently, as opposed to "SELECT lo_get(oid)" which looks like it could trigger the undesirable round-trip to the text format. You may want to test that or bring it up as a question to JDBC folks. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
