Re: SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 12:20:42PM -0400, Tom Lane wrote:

> "Vance Maverick" <vmaverick(at)pgp(dot)com> writes:
> > My question is about performance in the postgres server. When I execute
> > "SELECT SUBSTRING (my_bytea FROM ? FOR ?) FROM my_table WHERE id =3D ?",
> > does it fetch the whole BYTEA into memory? Or does it access only the
> > pages that contain the requested substring?
>
> Recent releases will do what you want if the column has been marked
> SET STORAGE EXTERNAL (before storing anything in it...) See the
> ALTER TABLE reference page.
Ah, thanks, good to know !

"Recent releases" seems to mean at least as far back as 8.1
going by the docs.

Now, to convert an existing bytea column I would need to add
a new bytea column with "set storage external", move the
data from the old column to the new column, remove the old
column, and give the new column the original name, correct ?

Or is the an easier way ?

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346