Re: Questions about horizontal partitioning

Ron Johnson 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?
>
It would. A query that uses an inner join implies that a matching entry
must exist in both tables - so the join must occur, otherwise you could
be returning rows that don't satisfy the join condition.

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com
Expert PostgreSQL Training: http://www.otg-nc.com/training-courses/category.php?cat_id=8

>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFFo5ZfS9HxQb37XmcRAtDRAJ41kKEN1Dv1iKXosTjy6IvMZKGccACfcZc9
> e4pV+u0uLFisHcLu/gyuCvE=
> =q44l
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>