Re: Applying TOAST to CURRENT

Jan Wieck wrote:
> 
> The Hermit Hacker wrote:
> >
> > have to third this one ... I think it should be totally transparent to the
> > admin/user ... just create it when the table is created, what's the worst
> > case scenario?  it never gets used and you waste 16k of disk space?
> >
> 
>     Not exactly.
> 
>     I've  made  some  good  experiences  with  having the toaster
>     trying to keep the main tuple size below 1/4 of  MaxTupleSize
>     (BLKSIZE  -  block  header). 

Can't _that_ behaviour be made modifyable by some setting ?

>     Remember  that external stored
>     attributes are only fetched from the  secondary  relation  if
>     really  needed  (when the result set is sent to the client or
>     if explicitly used in the query). So in a usual case, where a
>     relatively  small  amount of the entire data is retrieved and
>     key attributes are small, it's a win. With this  config  more
>     main tuples fit into one block, and if the attributes used in
>     the WHERE clause  aren't  stored  external,  the  result  set
>     (including  sort  and  group  actions)  can be collected with
>     fewer block reads. Only those big  values,  that  the  client
>     really wanted, have to be fetched at send time.

What is the priority of checks on indexed fetch?

I mean if we do "SELECT * FROM ttable WHERE toasted LIKE 'ab%' "

DO we first scan by index to 'ab%', then check if tuple is live and 
after that to the LIKE comparison ?

Would it not be faster in toast case to use the already retrieved 
index data and check that first, before going to main table (not to 
mention the TOAST table)

>     If  no  external  table  exists, the toaster will try the <2K
>     thing by compression only. If the resulting tuple  fits  into
>     the  8K  limit,  it's OK. 

Would it not be faster/cleaner to check some configuration variable 
than the existance of toest table ?

>     But if a secondary relation exists,
>     it'll store external to make the tuple <2K.  Thus, a 4K or 6K
>     tuple,  that  actually  fits  and would be stored in the main
>     table, will cause the toaster to jump in if we allways create
>     the secondary table.

Do our current (btree/hash) indexes support toast ?

If not, will they ?

> 
>     Hmmm - thinking about that it doesn't sound bad if we allways
>     create a secondary relation at CREATE TABLE time, but NOT the
>     index  for  it.  And at VACUUM time we create the index if it
>     doesn't exist AND there is external stored data.

Is there a plan to migrate to some combined index/database table for 
at least toast tables later ? 

For at least toast tables it seems feasible to start using the 
originally planned tuple-spanning mechanisms, unless we plan 
migrating LOs to toast table at some point which would make index-less 
tuple chaining a bad idea as it would make seeking on really large 
LOs slow. 

>     The table is prepared for external  storage  allways  and  we
>     avoid  the  risks  from  creating  tables  in  possibly later
>     aborting transactions or due to concurrency  issues.  But  we
>     don't  waste  the  index space for really allways-small-tuple
>     tables.

That could perhaps be done for other tables too, ie CREATE INDEX 
would not actually create index until VACUUM notices that table is 
big enough to make use of that index ?

On second thought that seems not a good idea to me ;(

> 
>     Another benefit would  be,  that  reloads  should  be  faster
>     because  with  this  technique,  the  toaster doesn't need to
>     insert index tuples during the load. The indices are  created
>     later at VACUUM after reload.

AFAIK reloads (from pg_dump at least) create indexes after LOAD'ing data

>     The  toaster  needs  to  use sequential scans on the external
>     table until the next vacuum  run,  but  index  usage  allways
>     depends on vacuum so that's not a real issue from my PoV.
> 
>     At least a transparent compromise - isn't it?

But do we need it ?

I suspect there are other issues that need your attention more than 
complicating table creation to save a few kb ;)

Creating toast tables still wastes only 1MB per 64 tables _that have 
toastable columns_, which seems real cheap considering today's HD
prices.

You would need 6400 toast tables to consume 1% of the smallest currently 
available (10GB) disk.

If that is a concern this can probably be cured by good docs that say 
in detail which datatypes cause toast tables an which don't.

-----------
Hannu