Re: Questions about horizontal partitioning

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.

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.

However,  if the primary key is entirely within those six columns,  there will have to be an index on it in both tables to enforce the primary key constraint.  In that case,  an inner join could be performed with an index lookup or an index scan plus hash join,  for a query that didn’t use any other columns.  Whether that translates into a significant I/O reduction depends on how wide and how frequently non-NULL those other columns are.