Re: Another Idea: Try Including snapshot with TOAS (was: Including Snapshot Info with Indexes)

Hi Simon/Hannu,

a) I accept with storing ctid in place of oid. That would definitely improve
the performance. I guess it would also remove the restriction of the size on
TOASTABLE ATTRIBUTES. I guess different chunks have to be linked together,
without the index.

b) But i don't understand how storing the visibility info in TOAST table
would help you. In that case you may need to update it for every delete/
update happening in the main table. Only thing, it would help is if someone
wants to do a full table scan on TOAST table alone. May be Vacuum of TOAST
tables can be done independently. But do you think it is worth the loss of
performance in Update/Delete?

Thanks,
Gokul.

On 10/8/07, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Mon, 2007-10-08 at 14:58 +0300, Hannu Krosing wrote:
>
> > 1. get rid of indexes for TOAST tables
> >
> > instead of TOAST tuple id store CTID of first TOAST block directly, and
> > use something like skip lists inside the TOAST block headers to access
> > next TOAST tuples.
>
> It should be possible to optimise TOAST for when there is just a single
> chunk that is toasted. Since we often (and by default) compress data
> stored in TOAST tables this would be a frequently used optimisation.
>
> Instead of storing the TOAST OID, which is then looked-up in the index
> to find the TOAST tid, we can just store the tid directly in the toast
> pointer on the main heap. That way we'll get faster read and write
> access for a good proportion of rows by avoiding the toast index and
> going straight to the toast heap.
>
> We'd need a different kind of toast pointer which would be 2 bytes
> longer than the normal pointer. I think we have a spare flag bit to
> indicate this.
>
> That's just a rough sketch, I've not checked the details on this.
>
> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com
>
>