Tom Lane <tgl@xxxxxxxxxxxxx> writes: > PFC <lists@xxxxxxxxxx> 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
