TOAST on indices - Mailing list pgsql-hackers

For discussion:
   First  what the current implementation and the yet to be done   proposals do.
       All varlena data types (text, char, varchar, arrays) will       finally  be  toastable.  Every table that uses
suchtypes       will have a secondary relation to move off attributes.
 
       The toaster allways tries to  keep  a  main  tuple  small       enough  so that at minimum 4 tuples fit into a
block.One       had complained about, and I explain  later  why  I  think       it's a good decision anyway.
 
   This strategy already covers most possible index problems. If   the main tuple fits into 2K after toasting,  any
combination  of  attributes out of it will too. The only thing not covered   are functional indices.
 
   In real world scenarios, indices are usually set up on  small   key  values.   These  are very likely to be kept
plainin the   main tuple by the toaster, becuase it looks  at  the  biggest   values  first.   So  an index (built out
ofthe values in the   main tuple  after  toasting)  will  also  contain  the  plain   values.  Thus,  index scans will
notrequire toast fetches in   turn. Except the indexed attribute had at some point  a  huge   value.
 
   The  current  TOAST implementation hooks into the heap access   methods only.  Automagically covering the index
issuesdue to   the  2K approach. Fact is, that if more toast entries can get   produced during index inserts, we need
totake care for  them   during vacuum (the only place where index items get removed).   Alot of work just to support
hugefunctional indices  -  IMHO   not  worth  the  efford  right  now.  Let's  better  get some   experience with the
entirething before going too far.
 
   Why is it good to keep the main tuple below 2K? First because   of the above side effects for indices. Second,
becausein the   most likely case  of  small  indexed  attributes,  more  main   tuples  (that  must  be  fetched  for
the visibility checks   anyway) will fit into one block. That'll cause more blocks of   the relation to fit into the
givenshared memory buffer cache   and avoids I/O during index scans.
 
   My latest tests load a 1.1M tree full of .html files  into  a   database.   The  result  is  a  140K  heap  plus
300K toast   relation. Without that 2K approach, the result is a 640K heap   plus  90K  toastrel  only.   Since all
compressionis done on   single entries, it scales linear, so that a  1.1G  tree  will   result in a 140M heap plus 300M
toastrelvs. a 640M heap plus   90M toastrel. No need to bechmark it - I know which  strategy   wins.
 


Jan

--

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