Re: SUBSTRING performance for large BYTEA

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Karsten Hilbert wrote:
> 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 ?

Well this is a guess, but:

Set existing column to storage external
update existing column with existing data:

UPDATE foo SET bar = bar;

Now the down side to this is you are going to create a dead row for
every update which means a vacuum (probably full) afterward, but the way
you describe above will do the same thing as well.

Sincerely,

Joshua D. Drake

>
> Karsten

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGxyseATb/zqfZUUQRAn7AAJ9jzhsOb8xoy9QWoI6yfNV4cO9Z3gCeJG6W
n3Z0uaYp5d6QGoFP3O8QJUI=
=fIqx
-----END PGP SIGNATURE-----