Re: [GENERAL] Questions about horizontal partitioning

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 oth

Re: [GENERAL] Questions about horizontal partitioning

Ron Johnson wrote: On 01/08/07 20:39, Tom Lane wrote: John Sales <[EMAIL PROTECTED]> 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 ta

Re: [GENERAL] Questions about horizontal partitioning

Bruno Wolff III <[EMAIL PROTECTED]> writes: > In this case the test would only be applied when no columns were being used > in a table being joined to. Since that is also an unusual case, if that case > could be quickly checked for, then it might conceivably be worth doing the > more expensive test

Re: [GENERAL] Questions about horizontal partitioning

On Tue, Jan 09, 2007 at 10:33:52 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > > No, that's still not right. With a LEFT JOIN you know that each row of > the narrow table will produce at least one row in the join view. What > you don't know is whether the row could produce more than one join ro

Re: [GENERAL] Questions about horizontal partitioning

On Tue, Jan 09, 2007 at 08:28:29 -0500, Chander Ganesan <[EMAIL PROTECTED]> wrote: > 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. Whi

Re: [GENERAL] Questions about horizontal partitioning

Chander Ganesan <[EMAIL PROTECTED]> writes: > 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

Re: [GENERAL] 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 <[EMAIL PROTECTED]> writes: By doing this, I'm hoping that the query optimizer is smart en

Re: [GENERAL] Questions about horizontal partitioning

On 09/01/07, Ron Johnson <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: > John Sales <[EMAIL PROTECTED]> 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

Re: [GENERAL] Questions about horizontal partitioning

-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 <[EMAIL PROTECTED]> writes: >>> By doing this, I'm hoping that the query optimizer is smart enough to see that if

Re: [GENERAL] Questions about horizontal partitioning

Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: John Sales <[EMAIL PROTECTED]> 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 t

Re: [GENERAL] Questions about horizontal partitioning

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: > John Sales <[EMAIL PROTECTED]> 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) t

Re: [GENERAL] Questions about horizontal partitioning

Tom Lane <[EMAIL PROTECTED]> wrote: John Sales 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

Re: [GENERAL] Questions about horizontal partitioning

John Sales <[EMAIL PROTECTED]> 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 thereb

[GENERAL] Questions about horizontal partitioning

Suppose I have a table - lets say it was developed by someone with little or no understanding of database design - and it has 230 columns. Now, it turns out that 99% of the time only about 8 colums are required, but all 230 columns are populated. However, legacy applications (which are run nig