Re: Questions about horizontal partitioning

Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 01/09/07 07:28, Chander Ganesan wrote:
>
>> Ron Johnson wrote:
>>
>>> 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.
>>
>
> Sure, if you were selecting those 6 columns from the "inner join
> view". <pause> Ah, now that I reread the OP, I see that that's
> what he seems to mean.
>
In theory, if the table with 6 columns was the child of the table with
200+ columns, and a PK-FK relationship existed, then the optimizer
wouldn't need to check for the existence of the rows, since the PK-FK
relationship would indicate that the rows did/do exist. However, I
don't *think* that the optimizer takes that into account (though with
PostgreSQL you never know, it's pretty smart stuff ;-) ) . Of course,
that might make insert operations difficult, but if you are using a view
to perform inserts you could probably handle that fairly easily in the
on insert rule...

--
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)
>
> iD8DBQFFo5tdS9HxQb37XmcRApwEAKDiqD86q3sh5eePFrgH3+o4LbTAYwCg1Oys
> 3/WT7eJvbxfE4RDY3E99NAo=
> =ix6x
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>