BUG #2236: extremely slow to get unescaped bytea data from db - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2236
Logged by:          Kai Ronan
Email address:      support@kalador.com
PostgreSQL version: 8.0.1
Operating system:   redhat linux
Description:        extremely slow to get unescaped bytea data from db
Details:

Using php 5.1.2, trying to store images in database using bytea column and
pg_unescape_bytea() which is a PHP wrapper for PQunescapeBytea().

It took 77 seconds to extract a 400K gif image from the database.  Using a
text column and base64 escaping, the same image took <1 to extract from the
database.  lo functions also work fast.  Loading images to the db is fast in
all cases.

Code snippet is:

<?php
 // Connect to the database
 $dbconn = pg_connect('dbname=imagedb');

 // Get the bytea data
 $res = pg_query("SELECT data FROM image WHERE name='big.gif'");

 // Convert to binary and send to the browser
 header('Content-type: image/gif');
 echo pg_unescape_bytea( pg_fetch_result($res,'data') );
?>