From: Greg Stark <gsstark@mit.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad plan on a view
Date: 01 Mar 2006 11:04:47 -0500
Message-ID: <87r75mup5s.fsf@stark.xeocode.com> (raw)
In-Reply-To: <10652.1141226168@sss.pgh.pa.us>
References: <op.s5qo4bx8cigqcu@apollo13>
<10652.1141226168@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, tgl@sss.pgh.pa.us, lists@peufeu.com
Subject: Re: Bad plan on a view
In-Reply-To: <87r75mup5s.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox