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

On Wed, Feb 08, 2006 at 09:52:33AM -0800, Kalador Tech Support wrote:
> $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;

The header call should be outside the section of code you're timing.
It probably doesn't matter, but the idea is to time pg_unescape_bytea
so that's all that should be timed (I agree with your decision to
time pg_fetch_result separately).

> 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).

Something must be different, although offhand I can't think of what it
might be. Locale settings, perhaps? I don't know if pg_unescape_bytea's
performance could be affected by that, but I've seen locale settings
cause horrible performance in string-manipulating code before.

> 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.

Too bad PHP's PostgreSQL extension doesn't handle binary results
(or does it?). With a binary cursor or a way to tell pg_query and
friends that you want binary results, you could retrieve the binary
image data without having to mess with encoding and decoding.

Even if you're satisfied with what you're doing, it would be
interesting to find an explanation for pg_unescape_bytea's performance
discrepancy and determine whether it's a problem with PHP or with
the underlying libpq function. Please let us know if you find
anything.

--
Michael Fuhr