Re: TOAST on indices

Philip Warner wrote:
> At 20:42 4/07/00 +0200, Jan Wieck wrote:
> >
> > So an index (built out of the values in the
> > main tuple after toasting) will also contain the plain
> > values. Thus, index scans will not require toast fetches in
> > turn. Except the indexed attribute had at some point a huge
> > value.
>
> So that, for toasted attrs, the indexes will be no use for sorting. I agree
> that in the majority of cases this is not a problem, but if the entire
> tuple gets toasted because it is too large it becomes a problem.

That ain't true, entirely. First of all, only single
attributes get toasted. Never a complete tuple but maybe all
of it's attributes (if it is a table with many, many
attributes or all of them are big).

If so, well, the sort might become damned slow. Assuming all
the rows selected have the attribute to sort on toasted, each
comparision will require two index scans (plus possibly
decompression) during the sort.

But tell me, do you know of real world DB installations where
indices on fields likely to be >1K exist? What is such an
index good for? Fast reverse lookup of 65536-bit RSA keys?

The system won't complain, nor will it bail out in such a
situation. That it won't behave as good as it could is a
con. Maybe we should tell on our web pages that someone who
wants to create indices on multi-K attributes should better
look for another DB, because Postgres is slow in that case?

> I agree that this is only a problem if indexes are used in sorting, and may
> not be a problem if one builds an index on 'substr(toastable-field,1,20)',
> but I think you are suggesting not supporting functional indexes,
> below...but maybe I've missed the point.
>
> > The current TOAST implementation hooks into the heap access
> > methods only. Automagically covering the index issues due to
> > the 2K approach. Fact is, that if more toast entries can get
> > produced during index inserts, we need to take care for them
> > during vacuum (the only place where index items get removed).
> > Alot of work just to support huge functional indices - IMHO
> > not worth the efford right now. Let's better get some
> > experience with the entire thing before going too far.

Yeah - you missed me here.

In the case of a functional index, the function would seldom
return one of the original tuples attribute values. Usually
those functions manipulate one or more attributes to compute
a completely new value (like your substr() example above).
In the TOAST world, any such function returns a plain, fully
expanded, in memory value.

So even if the toaster had worked on the main tuple and
compressed/moved off some attributes, the value that is
computed during index tuple creation is of full size. Having
a char(20000) attribute, the toaster will shrink it down so
the main tuple will fit. But a functional index like
"substr(att, 1, 10000)" must fail, because during index tuple
creation the funtion is evalueated and creates a 10000 byte
value.

In the current implementation, non-functional indices on huge
fields should be supported (there still are bugs because it
doesn't work right now). For functional ones, the old
restriction of "index-tuple must be smaller than supported
tuple size of index method" applies.

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 #