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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026