#36278 [Opn->Bgs]: extremely slow bytea extraction from pgsql

ID: 36278 Updated by: tony2001@php.net Reported By: support at kalador dot com -Status: Open +Status: Bogus Bug Type: Performance problem Operating System: redhat linux PHP Version: 5.1.2 New Comment: With libpq > 7.3 pg_unescape_bytea() is just a wrapper for PQunescapeBytea(). If this function is slow - sorry, we can do nothing about it. Please report it to PG developers. Previous Comments: ------------------------------------------------------------------------ [2006-02-03 22:38:18] support at kalador dot com libpq is 3.2 Using postgresql 8.0.1 btw ------------------------------------------------------------------------ [2006-02-03 22:17:26] tony2001@php.net What version of libpq are you using? ------------------------------------------------------------------------ [2006-02-03 21:34:53] support at kalador dot com Description: ------------ I tried 3 ways to store big images (500K) in database and display in browser. lo works fast, as does using a "text" column and converting the image to base64 (and back for display). However, using bytea with escaping is very slow. Adding to the database is fine (it is fast), retrieving is the problem. Reproduce code: --------------- This code consumed my CPU and took 77 seconds to run for a 400K image: <?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') ); ?> Expected result: ---------------- This should take less than 1 second. Using a text column and base64 escaping rather than a bytea column and pg_unescape_bytea takes less than 1 second on the same data. Actual result: -------------- Actually result - 77 seconds. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=36278&edit=1