Re: SUBSTRING performance for large BYTEA

On Sat, Aug 18, 2007 at 01:51:18PM -0400, Tom Lane wrote:

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> > Would it be feasible to add an ALTER TABLE mode
> > ... set storage externally-extended cutoff <size> ...
> > where <size> is the user configurable size of the column
> > data at which PostgreSQL switches from extended to external
> > storage strategy ?
>
> Actually, it just occurred to me that this ties into the recent
> discussion of compression parameters
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php
> (which hasn't gone further than discussion yet). Perhaps we need
> an additional parameter which is a maximum input size to attempt
> compression at all. IOW, the current force_input_size is not
> only useless but exactly backwards ...

I can see that a maximum size can be relevant for the
decision as to whether to *attempt* compression since large
things compress slowly and may unduly slow down queries.

As well as a minimum size to use compression on, quite
obviously.

OTOH, I'd like to be able to tell PostgreSQL to be so kind
and refrain from attempting to compress values above a
certain size even if it thought it'd make sense.

> There was some discussion in that thread (or maybe the earlier
> one on -patches) of exposing the lzcompress parameters directly
> to users, perhaps as an extended form of the current SET STORAGE
> command. That won't happen for 8.3 but it might later. In the
Sounds good.

> meantime, if the defaults included not attempting to compress
> multi-megabyte values, I think it'd Just Work for cases like
> yours.
Not as tweakable as I'd eventually want it but, yes, that
would sort of Just Work.

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