Re: Questions about horizontal partitioning

On 09/01/07, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 01/08/07 20:39, Tom Lane wrote:
> > John Sales <spelunker334(at)yahoo(dot)com> writes:
> >> By doing this, I'm hoping that the query optimizer is smart
> >> enough to see that if a query comes in and requests only the
> >> six columns (that are in the narrower table) that PostgreSQL
> >> won't have to load the wider table into the buffer pool, and
> >> thereby actually have to only access about 10% the amount of
> >> disk that it presently does.
> >
> >> Is this a sound theory?
> >
> > No. It still has to touch the second table to confirm the
> > existence of rows to join to.
>
> But if a query /requests *only* the six columns (that are in the
> narrower table)/, why will the optimizer care about the other 224
> columns?
>

If you are doing an inner join (read normal join) the column has to
exist in both tables to be in the final result. If your doing an outer
join it depends upon its type (left, right or full) and then postgres
may not optimise it out.

Peter Childs