Re: TEXT

"Eric Jain" <jain(at)gmx(dot)net> writes:
>> In what sense did you see performance problems, are those major problems?
>> Are there any benchmarks out there?

> This was a problem I observed with Oracle, where you have to use CLOBs if
> you are not sure if the maximum field size may exceed 32K. Retrieving a few
> thousand rows and reading their (small-sized) CLOB fields easily fills up
> half an hour! Just wanted to be sure PostgreSQL doesn't have the same
> problem...

Not sure what you consider "small-sized", nor what sort of hardware the
half-hour anecdote is for, but here are a couple of numbers that might
help. I made a table:
create table foo (f1 text);
and loaded a single row of 155786 characters into it. I then did
insert into foo select * from foo;
repeatedly to build up more rows. The tenth iteration of this (reading
and re-inserting 512 rows) took 109 seconds wall-clock time.
Subsequently, with 1024 such rows in the table,
select count(*) from foo
is essentially instantaneous (no surprise, since it's not having to read
the toasted values) but
select sum(length(f1)) from foo
takes 16 seconds since it has to read all of 'em.

This is on middle-aged HP hardware (9000/C180 CPU, fairly generic SCSI
disks). On-disk size of the 1024-row table is 8 pages in main table,
12032 pages in TOAST table; a shade under 100MB all told, so TOAST got
about 0.6 compression ratio on the text.

Bottom line: looks like we could stand to do some work on speed of
insertion of toasted values, but retrieval seems fine; at least by
Oracle standards ;-)

regards, tom lane