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

ID: 36278 User updated by: support at kalador dot com Reported By: support at kalador dot com -Status: Feedback +Status: Open Bug Type: Performance problem Operating System: redhat linux PHP Version: 5.1.2 New Comment: libpq is 3.2 Using postgresql 8.0.1 btw Previous Comments: ------------------------------------------------------------------------ [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