Skip site navigation (1) Skip section navigation (2)

Re: BUG #2236: extremely slow to get unescaped bytea data

From: Kalador Tech Support <support(at)kalador(dot)com>
To: Michael Fuhr <mike(at)fuhr(dot)org>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2236: extremely slow to get unescaped bytea data
Date: 2006-02-08 17:52:33
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
I modified your code slightly to isolate the pg_unescape_bytea() from 
the pg_fetch_result():

 // Connect to the database
 $dbconn = pg_connect('dbname=foo');

 // Get the bytea data
 $res = pg_query("SELECT data FROM bar WHERE name='image'");
 $tstart = microtime(true);
 $raw = pg_fetch_result($res, 'data');
 $fetch  = microtime(true) - $tstart;
 $tfetch = microtime(true);

 // Convert to binary and send to the browser
// header('Content-type: image/gif');
 header('Content-type: text/plain');
 $data = pg_unescape_bytea($raw);
 $dt = microtime(true) - $tfetch;
 printf("fetch    time = %.3fms, %d bytes\n", $fetch*1000.0,strlen($data));
 printf("unescape time = %.3fms, %d bytes\n", $dt*1000.0,strlen($data));

When run from a browser, accessing an Apache webserver, the results 
displayed were:

fetch    time = 3.632ms, 296043 bytes
unescape time = 70625.518ms, 296043 bytes

When running the same code from command line php, the results were:

fetch    time = 3.618ms, 296043 bytes
unescape time = 8.298ms, 296043 bytes

Am running Apache 1.3.33.  I can't figure the difference between 
environments - command line php and apache php are compiled the same 
(except for the --with-apxs=... instruction for apache version).

The code to insert into the database was:

 // Connect to the database
 $dbconn = pg_connect('dbname=foo');

 // Read in a binary file
 $data = file_get_contents('big.gif');

 // Escape the binary data
 $escaped = pg_escape_bytea($data);

 // Insert it into the database
 pg_query("INSERT INTO bar (name, data) VALUES ('image', '{$escaped}')");

As an aside, I'm now using base64 encode/decode to insert data without 
problems.  For GIF images, at least, it is also a much smaller encoding 
than the escape_bytea encoding.

Kai Ronan
Technical Support
Kalador Entertainment Inc.

Michael Fuhr wrote:

>On Sat, Feb 04, 2006 at 04:06:11PM -0800, Kalador Tech Support wrote:
>>I've since isolated the problem to the unescape_bytea function not the 
>>I inserted the same image to a bytea column using base64 encoding, and 
>>extracted it from the table (using base64 decoding) and this worked very 
>>fast (<1 second).  So, it is the unescape_bytea function that is to blame.
>pg_unescape_bytea is fast here; I just unescaped an 850K jpeg image
>in about 0.18 seconds on a slow (500MHz) machine.
>How did you determine that pg_unescape_bytea was the problem?  What
>does something like the following show?
>$tstart = microtime(true);
>$data = pg_unescape_bytea(pg_fetch_result($res, 'data'));
>$dt = microtime(true) - $tstart;
>header("Content-Type: text/plain");
>printf("unescape time = %.3fms, %d bytes\n", $dt * 1000.0, strlen($data));

In response to


pgsql-bugs by date

Next:From: Bruno Wolff IIIDate: 2006-02-08 18:46:04
Subject: Re: BUG #2243: Postgresql fails to finish some queries
Previous:From: Tatiana GribanovskaiaDate: 2006-02-08 16:50:36
Subject: BUG #2247: Old Postgres driver

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group