Thread: Efficiency of inner joins across multiple tables
Hello, I guess this is basic question for DBAs. I have several tables, each numbering in 100,000's of rows. They all have have connectionHash as the primary key and indices on several variables. E.g Table1 has connectionHash, duration, type Table2 has connectionHash, location Table3 has connectionHash, region Now i need to select columns:connectionHash, duration, location, region (the variables can differ) from Tables 1,2 and 3 using an inner join on connectionHash(indexed). Q: How efficient is this? Issues involved: One might suggest that i make a table with all columns from across all tables, however, as my research continues more tables, TableK, with connectionHash, extracolumn will be created. Option: Every time i create a new table should i create a table with all columns and query from that? So I make one new table (made via an inner join, but done only once) and subsequent queries made from this larger table(union of all columns from Tables 1..K) Any suggestions will be very much welcome. Regards Saptarshi
Hey folks, stuck on something here and maybe I went down a dead end road. Is it possible to have a left outer join on 2 tables from the same originating table where T1 left outer joins to T2 and T1 left outer joins to T3? I guess I'm stuck on the FROM clause syntax or trying to do something you just can't do. Maybe it is possible to join T1 and T1again, back to each other but I didn't want to do that. Many Thanks, Bill
On 08/10/2008 01:02, Bill Reynolds wrote:
> Is it possible to have a left outer join on 2 tables from the same
> originating table where T1 left outer joins to T2 and T1 left outer
> joins to T3? I guess I'm stuck on the FROM clause syntax or trying to
> do something you just can't do.
Maybe I'm missing something, but it ought to be just:
...from T1
left join T2 on (T1.a_column = T2.a_column)
left join T3 on (T1.another_column = T3.another_column)
etc.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
Thanks much. Silly Me, I had an extraneous comma
-----Original Message-----
From: Raymond O'Donnell [mailto:rod@iol.ie]
Sent: Tuesday, October 07, 2008 8:12 PM
To: Bill Reynolds
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Left outer join question
On 08/10/2008 01:02, Bill Reynolds wrote:
> Is it possible to have a left outer join on 2 tables from the same
> originating table where T1 left outer joins to T2 and T1 left outer
> joins to T3? I guess I'm stuck on the FROM clause syntax or trying
to
> do something you just can't do.
Maybe I'm missing something, but it ought to be just:
...from T1
left join T2 on (T1.a_column = T2.a_column)
left join T3 on (T1.another_column = T3.another_column)
etc.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
On Tue, Oct 7, 2008 at 7:30 PM, Saptarshi Guha <saptarshi.guha@gmail.com> wrote: > Q: How efficient is this? If connectionHash is unique or near to it, and everything's indexed... it could work for reasonable K values depending on your performance requirements. > Every time i create a new table should i create a table with all columns and > query from that? Is there any reason you can't just add the new column to the existing table? -- - David T. Wilson david.t.wilson@gmail.com
Hello, >> Q: How efficient is this? > > If connectionHash is unique or near to it, and everything's indexed... > it could work for reasonable K values depending on your performance > requirements. > Nice to hear. >> Every time i create a new table should i create a table with all >> columns and >> query from that? > > Is there any reason you can't just add the new column to the > existing table? > Aah, very nice. I wasn't aware I could add a column once it has the table has been created. I see the Alter command will do the trick. Sounds good. Thank you very much Regards Saptarshi