Re: SUBSTRING performance for large BYTEA

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: SUBSTRING performance for large BYTEA
Date: 2007-08-18 21:39:56
Message-ID: 20070818213956.GE4545@merkur.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Michelle Konzack 2007-08-18 21:40:19 Re: SELECT question
Previous Message Karsten Hilbert 2007-08-18 21:17:40 Re: SUBSTRING performance for large BYTEA