Re: [HACKERS] LONG

>     Maybe  we  make  this  mechanism  so  general  that   it   is
>     automatically applied to ALL varsize attributes? We'll end up
>     with on big pg_long where 90+% of the databases content  will
>     be stored.

If most joins, comparisons are done on the 10% in the main table, so
much the better.

> 
>     But  as  soon as an attribute stored there is used in a WHERE
>     or is subject to be joined, you'll see why not (as said, this
>     type  will  NOT  be enabled for indexing). The operation will
>     probably fallback to a seq-scan on the main  table  and  then
>     the attribute must be fetched from pg_long with an index scan
>     on every single compare etc. - no, no, no.

Let's fact it.  Most long tuples are store/retrieve, not ordered on or
used in WHERE clauses.  Moving them out of the main table speeds up
things.  It also prevents expansion of rows that never end up in the
result set.

In your system, a sequential scan of the table will pull in all this
stuff because you are going to expand the tuple.  That could be very
costly.  In my system, the expansion only happens on output if they LONG
field does not appear in the WHERE or ORDER BY clauses.

Also, my idea was to auto-enable longs for all varlena types, so short
values stay in the table, while longer chained ones that take up lots of
space and are expensive to expand are retrieved only when needed.

I see this as much better than chained tuples.


> 
>     And it will not be one single pg_long table. Instead it  will
>     be a separate table per table, that contains one or more LONG
>     attributes.  IIRC, the TRUNCATE functionality was implemented
>     exactly  to  QUICKLY  be able to whipe out the data from huge
>     relations AND get the disk space  back.  In  the  case  of  a
>     central  pg_long, TRUNCATE would have to scan pg_long to mark
>     the tuples for deletion and vacuum must be run to really  get
>     back  the  space.  And a vacuum on this central pg_long would
>     probably take longer than the old DELETE, VACUUM of  the  now
>     truncated table itself. Again no, no, no.
> 

I guess a separate pg_long_ per table would be good.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026