Tom Lane <tgl@sss.pgh.pa.us> writes:
> PFC <lists@peufeu.com> writes:
> > So, in order to speed up requests which need a full table scan, I wanted
> > to put the text fields in another table, and use a view to make it look
> > like nothing happened. Also, the small table used for searching is a lot
> > more likely to fit in RAM than the big table with all the text which is
> > only used for display.
>
> Aren't you going to a lot of work to reinvent something that TOAST
> already does for you? (At least, in the cases where the text fields
> are wide enough that it really matters.)
I think this is a fairly common data modelling trick actually. And it's not a
terribly large amount of work either.
While TOAST has a similar goal I don't think it has enough AI to completely
replace this manual process. It suffers in a number of use cases:
1) When you have a large number of moderate sized text fields instead of a
single very large text field. This is probably the case here.
2) When you know exactly which fields you'll be searching on and which you
won't be. Often many speed-sensitive queries don't need to access the
extended information at all.
Instead of making the decision on a per-record basis you can *always* move
the data to the other table saving even more space even in cases where
you're gaining very little per record. In total across the entire scan you
still gain a lot being able to scan just the dense integer fields.
Also, is the optimizer capable of coming up with merge join type plans for
TOAST tables when necessary?
--
greg