From: J@Planeti.Biz
Message-ID: <009a01c6229b$d67c6670$0d310d05@fatchubby>
To: <pgsql-performance@postgresql.org>
References: <786c2f6d0601260206k2201a548p48af7c7fc085d3b2@mail.gmail.com>
	<005d01c6228f$3772dca0$0d310d05@fatchubby>
	<43D8F4FD.4040703@modgraph-usa.com>
	<008001c62295$156b8fc0$0d310d05@fatchubby>
	<43D8FD39.8000408@archonet.com>
Subject: Re: Query optimization with X Y JOIN
Date: Thu, 26 Jan 2006 12:13:33 -0500
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

Yes, that helps a great deal. Thank you so much.

----- Original Message ----- 
From: "Richard Huxton" <dev@archonet.com>
To: <J@planeti.biz>
Cc: <pgsql-performance@postgresql.org>
Sent: Thursday, January 26, 2006 11:47 AM
Subject: Re: [PERFORM] Query optimization with X Y JOIN


> J@Planeti.Biz wrote:
>> If I want my database to go faster, due to X then I would think that the 
>> issue is about performance. I wasn't aware of a paticular constraint on 
>> X.
>
> You haven't asked a performance question yet though.
>
>> I have more that a rudementary understanding of what's going on here, I 
>> was just hoping that someone could shed some light on the basic principal 
>> of this JOIN command and its syntax. Most people I ask, don't give me 
>> straight answers and what I have already read on the web is not very 
>> helpful thus far.
>
> OK - firstly it's not a JOIN command. It's a SELECT query that happens to 
> join (in your example) three tables together. The syntax is specified in 
> the SQL reference section of the manuals, and I don't think it's different 
> from the standard SQL spec here.
>
> A query that joins two or more tables (be they real base-tables, views or 
> sub-query result-sets) produces the product of both. Normally you don't 
> want this so you apply constraints to that join (table_a.col1 = 
> table_b.col2).
>
> In some cases you want all the rows from one side of a join, whether or 
> not you get a match on the other side of the join. This is called an outer 
> join and results in NULLs for all the columns on the "outside" of the 
> join. A left-join returns all rows from the table on the left of the join, 
> a right-join from the table on the right of it.
>
> When planning a join, the planner will try to estimate how many matches it 
> will see on each side, taking into account any extra constraints (you 
> might want only some of the rows in table_a anyway). It then decides 
> whether to use any indexes on the relevant column(s).
>
> Now, if you think the planner is making a mistake we'll need to see the 
> output of EXPLAIN ANALYSE for the query and will want to know that you've 
> vacuumed and analysed the tables in question.
>
> Does that help at all?
> -- 
>   Richard Huxton
>   Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>