Skip site navigation (1) Skip section navigation (2)

Query optimization with X Y JOIN

From: J(at)Planeti(dot)Biz
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query optimization with X Y JOIN
Date: 2006-01-26 15:43:12
Message-ID: 005d01c6228f$3772dca0$0d310d05@fatchubby (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performancepgsql-sql
Hey guys, how u been. This is quite a newbie question, but I need to ask it. I'm trying to wrap my mind around the syntax of join and why and when to use it. I understand the concept of making a query go faster by creating indexes, but it seems that when I want data from multiple tables that link together the query goes slow. The slow is typically due to expensive nested loops. The reason is, all my brain understands is:

select
    tablea.data
    tableb.data
    tablec.data
from
    tablea
    tableb
    tablec
where
    tablea.pri_key = tableb.foreign_key AND
    tableb.pri_key = tablec.foreign_key AND...

From what I read, it seems you can use inner/outer right/left join on (bla) but when I see syntax examples I see that sometimes tables are omitted from the 'from' section of the query and other times, no. Sometimes I see that the join commands are nested and others, no and sometimes I see joins syntax that only applies to one table. From what I understand join can be used to tell the database the fast way to murge table data together to get results by specifiying the table that has the primary keys and the table that has the foreign keys.

I've read all through the postgres docs on this command and I'm still left lost. Can someone please explain to me in simple language how to use these commands or provide me with a link. I need it to live right now. Thanx.

    

In response to

Responses

pgsql-performance by date

Next:From: Jozsef SzalayDate: 2006-01-26 15:50:29
Subject: Incorrect Total runtime Reported by Explain Analyze!?
Previous:From: Jignesh K. ShahDate: 2006-01-26 14:56:14
Subject: Re: PostgreSQL Solaris packages now in beta

pgsql-sql by date

Next:From: Craig A. JamesDate: 2006-01-26 16:12:45
Subject: Re: Query optimization with X Y JOIN
Previous:From: Stephan SzaboDate: 2006-01-26 15:28:56
Subject: Re: Changing the transaction isolation level within the stored

pgsql-general by date

Next:From: Craig A. JamesDate: 2006-01-26 16:12:45
Subject: Re: Query optimization with X Y JOIN
Previous:From: John D. BurgerDate: 2006-01-26 15:13:52
Subject: Re: pgstattuple output?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group