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@yahoo.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@postgresql.org so that your      message can get through to the mailing list cleanly