Re: update on TOAST status'

Tom Lane wrote:
> JanWieck(at)t-online(dot)de (Jan Wieck) writes:
> > Tom Lane wrote:
> >> One simple answer that might help for other scenarios too is to keep
> >> a small cache of the last few values that had to be untoasted. Maybe
> >> we only need it for moved-off values --- it could be that decompression
> >> is fast enough that we should just do it over rather than trying to
> >> cache.
>
> > I'm still argueing that indexing huge values is a hint for a
> > misleading schema. If this is true, propagating toasted
> > attributes into indices is a dead end street and I'd have to
> > change the heap-access<->toaster interface so that the
> > modified (stored) main tuple isn't visible to the following
> > code (that does the index inserts).
>
> But you'll notice that is *not* what I suggested. A detoasted-value

Haven't missed it in the first read - of course.

> cache could be useful in more situations than just an index lookup.
> I don't necessarily say we've got to have it in 7.1, but let's keep
> the idea in mind in case we start finding there is a bottleneck here.
>
> > What is the value of supporting index tuples >2K?
>
> If you're toasting the whole main tuple down to <2K, you might find
> yourself toasting individual fields that are a good bit less than
> that. So I don't think indexing a toasted value will be all that
> unusual.

Exactly that's why I'm asking if we wouldn't be better off by
limiting index tuples to (blocksize - overhead) / 4 and
allways store plain, untoasted values in indices.

I've asked now a couple of times "who really has the need for
indexing huge values"? All responses I got so far where of
the kind "would be nice if we support it" or "I don't like
such restrictions". But noone really said "I need it".

> But this is all speculation for now. Let's get it working bulletproof
> for 7.1, and then worry about speedups after we know they are needed.

Let me speculate too a little.

The experience I have up to now is that the saved time from
requiring less blocks in the buffer cache outweights the cost
of decompression. Especially with our algorithm, because it
is byte oriented (instead of huffman coding beeing based on a
bit stream), causing it to be extremely fast on
decompression. And the technique of moving off values from
the main heap causes the main tuples to be much smaller. As
long as the toasted values aren't used in qualification or
joining, only their references move around through the
various executor steps, and only those values that are part
of the final result set need to be fetched when sending them
to the client.

Given a limited amount of total memory available for one
running postmaster, we save alot of disk I/O and hold more
values in their compressed format in the shared buffers. With
the limit on total memory, the size of the buffer cache must
be lowered by the size of the new detoasted cache, and that
only if we make it shared too. Given further an average of
50% compression ratio (what's not unlikely with typical input
like html pages), one cached detoasted value would require
two compressed ones to go away.

Wouldn't really surprise me if we gain speed from it in the
average query. Even if some operations might slow down
(sorting on maybe toasted fields).

We need to see some results and wait for reports for this.
But we know already that it can cause trouble with indexed
fields, because these are likely to be used for comparision
during scans. So do we want to have indices storing plain
values allways and limit them in the index-tuple size or not?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #