Re: Bad plan on a view

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> PFC <lists(at)peufeu(dot)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