The following bug has been logged online:

Bug reference: 2236
Logged by: Kai Ronan
Email address: support (AT) kalador (DOT) 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') );
?>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Kai Ronan wrote:

Do you have an index in the image.name column? What does an

EXPLAIN ANALYZE SELECT data FROM image WHERE name='big.gif'

say?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly