To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PFC <lists@peufeu.com>,  pgsql-performance@postgresql.org
Subject: Re: Bad plan on a view
References: <op.s5qo4bx8cigqcu@apollo13> <10652.1141226168@sss.pgh.pa.us>
In-Reply-To: <10652.1141226168@sss.pgh.pa.us>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 01 Mar 2006 11:04:47 -0500
Message-ID: <87r75mup5s.fsf@stark.xeocode.com>
Lines: 38
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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